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:

 ActiveCell.FormulaR1C1 = "=CHOOSE(WEEKDAY(myMonthb & RC[1] & myMonthe), ""Sunday"", ""Monday"", ""Tuesday"", ""Wednesday"", ""Thursday"", ""Friday"", ""Saturday"")"

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

    Dim myMonthb, myMonthe As String
    myMonthb = "5/1"
    myMonthe = "/2008"
    Do
    ActiveCell.FormulaR1C1 = "=CHOOSE(WEEKDAY(""5/"" & RC[1] & ""/2008""), ""Sunday"", ""Monday"", ""Tuesday"", ""Wednesday"", ""Thursday"", ""Friday"", ""Saturday"")"


    ActiveCell.Offset(1, 0).Select

    Loop Until IsEmpty(ActiveCell.Offset(0, 1))

Any assistance would be appreciated. Thanks.

[Paul]

Does this work?

ActiveCell.FormulaR1C1 = "=CHOOSE(WEEKDAY(" & myMonthb & "RC[1]" & myMonthe & "), ""Sunday"", ""Monday"", ""Tuesday"", ""Wednesday"", ""Thursday"", ""Friday"", ""Saturday"")"

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:

 
    Dim myMonthb, myMonthe, myMonthd, myWeekday As String
    myMonthb = "5/"
    myMonthe = "/2008"
    Do
    myMonthd = ActiveCell.Offset(0, 1).Value
    myWeekday = myMonthb & myMonthd & myMonthe
ActiveCell.FormulaR1C1 = "=CHOOSE(WEEKDAY(""" & myWeekday & """), ""Sunday"", ""Monday"", ""Tuesday"", ""Wednesday"", ""Thursday"", ""Friday"", ""Saturday"")"
    ActiveCell.Offset(1, 0).Select

    Loop Until IsEmpty(ActiveCell.Offset(0, 1))

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.

MsgBox Format(Date(), "dddd")

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.