VBA to scan range

[David]

My range, C2:AA2 contains weekday dates for any given month.
I want code that will look through that range and find the last Friday.
eg. lastFri = ActiveSheet.Range("C2:AA2").Find(<the last Friday>)
For my needs, this must be a VBA solution.

Any help?

 

[Bob Phillips]

MsgBox Evaluate("=MAX(IF(WEEKDAY(C2:AA2)=6,C2:AA2))")

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Ardus Petus]

No error checking:

'--------------------------
Sub test()
MsgBox lastFriday(Range("C2:AA2"))
End Sub

Function lastFriday(rng As Range) As Date
Dim i As Long
For i = rng.Count To 1 Step -1
If Weekday(rng(i)) = 6 Then
lastFriday = rng(i)
Exit For
End If
Next i
End Function
'---------------------------------

 

[David]

Bob Phillips wrote

MsgBox Evaluate("=MAX(IF(WEEKDAY(C2:AA2)=6,C2:AA2))")

I was able to succesfully incorporate this into my existing code.
Many thanks.

It took some experimenting, because my code actually loops through several
sheets, performing the same operation on each. So, if I didn't put the
Evaluate line before the loop, sheets other than the first didn't show
desired results. Also had to insure first sheet was selected early.

 

[David]

Ardus Petus wrote

No error checking:

'--------------------------
Sub test()
MsgBox lastFriday(Range("C2:AA2"))
End Sub

Function lastFriday(rng As Range) As Date
Dim i As Long
For i = rng.Count To 1 Step -1
If Weekday(rng(i)) = 6 Then
lastFriday = rng(i)
Exit For
End If
Next i
End Function
'---------------------------------

Long way around, but works. Thanks for your contribution.

 

[Jim May]

Isn't this code just finding the first Right-most Friday
and stopping? What if 2 or 3 columns more
to the left you had another LATER Friday; Wouldn't
this code not consider it?
Sorry, just trying to understand various questions asked
and solutions offered..

 

[David]

Bob Phillips wrote

MsgBox Evaluate("=MAX(IF(WEEKDAY(C2:AA2)=6,C2:AA2))")

I don't know what I was thinking. There's really no need to scan all the
dates. Since V2 (4th Friday) will always hold a date, and AA2 (5th Friday)
may or may not, I only need to look at those 2 cells:

LastFri = Application.Max([C2], [AA2])

 

[David]

David wrote

Bob Phillips wrote

MsgBox Evaluate("=MAX(IF(WEEKDAY(C2:AA2)=6,C2:AA2))")

I don't know what I was thinking. There's really no need to scan all
the dates. Since V2 (4th Friday) will always hold a date, and AA2 (5th
Friday) may or may not, I only need to look at those 2 cells:

LastFri = Application.Max([C2], [AA2])

All brackets not needed:
LastFri = Application.Max([V2, AA2])
or standard convention:
LastFri = Application.Max(Range("V2, AA2"))