Help with macro to assign day of week

[DavidCampbell]

I have a single table Excel 2003 sheet with 10 columns and a variable number of rows. I would like to find all lines in the used sheet area with an asterisk in column g. For each row found I want to assign two words to column h based on the contents of columns c and d.

Column c (DOW) contains a number from 1 to 5, representing Mon, Tue, Wed, Thur and Fri. Col d (WOM) contains a number from 1 to 4 representing the first, second, third or fourth week of the month.

On a given line, based on cols c & d, I want to assign the words, First Mondays or Third Wednesdays, etc. to col h. If the selected line (with an asterisk in col g) doesn't have numbers in cols c & d that are within range, I want to assign a blank (not null) to col h.

I'd like to use an array in the macro because I think that's the most efficient, but I'm at a loss as to how to attack it.

Any help with coding or ideas would be appreciated.

Thanks.

 

[Bong]

This might get you started:

Sub test()
    ord = Array("first", "second", "third", "fourth")
    Set r2 = Sheet1.Columns("G")
    Set rtn = r2.Find("*")
    firstAddress = rtn.Address
    Do
        Set rtn = r2.FindNext(rtn)
        newtext = WeekdayName(Cells(rtn.Row, 3), False, vbMonday)
        newtext = ord(Cells(rtn.Row, 4)) & " " & newtext & "s"
        Cells(rtn.Row, 8) = newtext
    Loop While rtn.Address <> firstAddress
End Sub

_________________
Bob Rashkin

 

[AnotherHiggins]

If you have any control over the source data that is being brought into this excel table, I'd strongly suggest that you bring in an actual date. But I understand we can't always get what we want in these things.

What about the fifth week of the month? Such as Oct 29th this month - that's the fifth Monday.

You can do all of this with worksheet formulas:

Instead of "First, Second", would "1st, 2nd" work for you? If so, this will work for you:
[COLOR=blue white]=D2 & CHOOSE(D2, "st", "nd", "rd", "th", "th"))[/color]
If you really want to see it spelled out, then change that to:
[COLOR=blue white]=CHOOSE(D2, "First", "Second", "Third", "Fourth", "Fifth")[/color]

As for days of the week, once again you can just use a worksheet formula:
[COLOR=blue white]=TEXT(DAY(C2) + 1, "DDDD")[/color]

Putting those together into a single formula, you'd have something like:
[COLOR=blue white]=D2 & CHOOSE(D2, "st", "nd", "rd", "th", "th") & " " & TEXT(DAY(C2)+1,"DDDD")[/color]

Wrap the whole thing in an IF to see if an asterisk is in column G. That gives you:
[COLOR=blue white]=IF(G2="*",D2 & CHOOSE(D2, "st", "nd", "rd", "th", "th") & " " & TEXT(DAY(C2)+1,"DDDD"), "")[/color]



[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[DavidCampbell]

I will try both of these solutions.

I was looking for a vba routine to get my feet wet with a live application and look forward to trying the code. I don't have any formula dates other that a footer and the 5th week fortunately doesn't come into play.

I'm a formula kind of guy and am impressed with the second approach which I think is going to solve the immediate problem.

Thanks for both ideas!