VBA to scan range
 

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?

--
David

VBA to scan range
 

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

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"David" wrote in message
...
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?

--
David

VBA to scan range
 

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" a écrit dans le message de
...
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?

--
David

VBA to scan range
 

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

VBA to scan range
 

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
'---------------------------------

"David" a écrit dans le message de
...
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?

--
David



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

--
David

VBA to scan range
 

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" wrote in message
...
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
'---------------------------------

"David" a écrit dans le message de
...
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?

--
David



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

--
David

VBA to scan range
 

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

VBA to scan range
 

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"))

--
David