Retrieve Weekday from String

[rasak]

Hey everyone,

First time poster here, hope I'm doing the right thing.

I'm attempting to do the following:

Create two variables myMonthb and myMonthe, which will be used as =Weekday(myMonthb + day + myMonthe). The macro will get the day number from the column to the right of the active cell, and insert it as =Weekday(myMonthb/day/MyMonthe) - the problem I am having is that when I attempt to reference the variables myMonthb and myMonthe in the statement, I get a debug error. I am referencing them in the following way:

Please Login or Register  to view this content.

If I simply add the month and date in (as in the code below), the macro works fine.

Please Login or Register  to view this content.

Any assistance would be appreciated. Thanks.

[Paul]

Does this work?

Please Login or Register  to view this content.

The variables must be outside the quotation marks that define the formula, otherwise they're seen as text only.

[rasak]

Thanks. That seems to work, but the output into the Formula bar of each cell is below:

=CHOOSE(WEEKDAY(5/E9/2008), "Sunday", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday")

Where it should be:

Choose(Weekday("5/" & E9 & "/2008")...

As the middle value is pulled from cell to the right, and the Weekday function doesn't work if used as ("5/E9/2008") or (5/E9/2008).

Is there a way to simply get the value of E9 in the macro, and then insert it into the formula as the value, rather than a reference to the right cell?

[rasak]

Working now with the following code:

Please Login or Register  to view this content.

Thanks for your help!

[Norie]

Why are you using this method?

There are functions in VBA that could return the text for the weekday.

For example.

Please Login or Register  to view this content.

By the way I can't work with your attachment since it's Excel 2007.

[rasak]

Thanks - I'll take a look at that, and in the future will post in 2003 format. As for why, I don't really know, just came to it by searching over the help files. Looks a bit backwards, I know.