Finding Last Saturday in Range

[tjlaser shepard]

I am writing a macro to try and sort some payroll information. On the worksheet, column "I" contains the "payroll end dates" for accounting records in the spreadsheet. I have used the following section of code to find the max date column "I" and assign it to a variable:

Dim lastEndDate As Long

With ThisWorkbook.Sheets("100")

lastEndDate = WorksheetFunction.Max(Range("I:I"))

End With

This works fine for finding the latest date, but I need a way to make it more specifically the latest Saturday. Is there someway to incorporate the WEEKDAY function or something else to specify that the date stored in lastEndDate must be a Saturday? Thanks

 

[steve the fish]

See if this works:

Set sh = Sheets("100")
lr = sh.Range("I" & Rows.Count).End(xlUp).Row

lastEndDate = Evaluate("=MAX(IF(ISNUMBER('" & sh.Name & "'!I1:I" & lr & "),IF(WEEKDAY('" & sh.Name & "'!I1:I" & lr & ")=7,'" & sh.Name & "'!I1:I" & lr & "),0))")

 

[steve the fish]

Can you do anything like that with the sheet name too? I put the isnumber in to prevent errors with text.

 

[tjlaser shepard]

It works! Thanks for the help, Steve.

 

[Jonmo1]

Can you do anything like that with the sheet name too? I put the isnumber in to prevent errors with text.

You would also need the isnumber if you were to switch to MIN, blanks would be counted as saturday as well)

 

[Rick Rothstein]

See if this works:

Set sh = Sheets("100")
lr = sh.Range("I" & Rows.Count).End(xlUp).Row

lastEndDate = Evaluate("=MAX(IF(ISNUMBER('" & sh.Name & "'!I1:I" & lr & "),IF(WEEKDAY('" & sh.Name & "'!I1:I" & lr & ")=7,'" & sh.Name & "'!I1:I" & lr & "),0))")

You can write your Evaluate code line a little more compactly like this...

lastEndDate = Evaluate(Replace(Replace("MAX(IF(WEEKDAY('@'!I1:I#)=7,'@'!I1:I#))", "#", lr), "@", sh.Name))

 

[MARK858]

Seeing as you are only looking for the one value (for a change) I don't see any reason why you can't use an 'orrible loop.

 Sub GetSat()
    Dim lastEndDate As Date, lstRw As Long, xLng As Long
    With ThisWorkbook.Sheets("100")
        lstRw = .Range("I" & Rows.Count).End(xlUp).Row
        For xLng = lstRw To 2 Step -1
            If Weekday(.Cells(xLng, "i").Value, vbSaturday) = 1 Then
                lastEndDate = .Cells(xLng, "i").Value
                Debug.Print lastEndDate
                Exit Sub
            End If
        Next
    End With
End Sub

 

[Jonmo1]

@Mark

I had thought of that as well, however that doesn't take into account the 'Max' date If Saturday.
It would work if the dates are sorted in Ascending order, but we're not sure if they are..

 

[MARK858]

@Mark

I had thought of that as well, however that doesn't take into account the 'Max' date If Saturday.
It would work if the dates are sorted in Ascending order, but we're not sure if they are..

Good point, me being dumb. paying too much attention to the thread title and not the question.