Loop through Worksheets to find row of current date

[Paul]

Hi,

Trying to get the row number of the current date on all worksheets of a workbook

The sheet2.Range("q1").Value is just the current date =Today()

Sub DATEFIND()
Dim lastrow As Integer
Dim ZeroLast As Date
Dim WS As Worksheet

ZeroLast = Sheet2.Range("q1").Value

For Each WS In Sheets

With WS
lastrow = WS.Cells.Find(ZeroLast, searchorder:=xlByRows, searchdirection:=xlPrevious).Row

WS.Range("C1").Value = lastrow
WS.Range("e1").Value = WS.Name

End With
Next WS
End Sub

The error lies in the ws.cells but I don't know how to get past this.

Please help if you can.

Paul

 

[mumps]

Try:

Sub DATEFIND()
    Application.ScreenUpdating = False
    Dim lastrow As Long
    Dim ZeroLast As String
    Dim WS As Worksheet
    ZeroLast = Sheets(2).Range("Q1")
    For Each WS In Sheets
        lastrow = WS.UsedRange.Find(CDate(ZeroLast), LookIn:=xlFormulas, lookat:=xlWhole).Row
        WS.Range("C1").Value = lastrow
        WS.Range("E1").Value = WS.Name
    Next WS
    Application.ScreenUpdating = True
End Sub

 

[Paul]

Thanks for the reply,

I still get an error on this line of code

lastrow = WS.UsedRange.Find(CDate(ZeroLast), LookIn:=xlFormulas, lookat:=xlWhole).Row

Ideas?

I have the code in Module 1. Does that make any difference?

Paul

 

[mumps]

I tried the macro on some dummy data and it worked properly. Are the cells which contain the dates formatted as "Date"? It is always easier to help and test possible solutions if we could work with your actual file. Perhaps you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Include a detailed explanation of what you would like to do referring to specific cells and worksheets. If the workbook contains confidential information, you could replace it with generic data.

 

[Paul]

Ok,
I made a dummy file and uploaded it to:

https://app.box.com/s/4uizlzl7peom1xsyxqad36pnysx9el56

Thank you

 

[mumps]

Try:

Sub DATEFIND()
    Application.ScreenUpdating = False
    Dim lastrow As Long
    Dim ZeroLast As String
    Dim WS As Worksheet
    ZeroLast = Sheets("Sheet2").Range("Q1")
    For Each WS In Sheets
        lastrow = WS.UsedRange.Find(CDate(ZeroLast), LookIn:=xlFormulas, lookat:=xlWhole).Row
        WS.Range("C1").Value = lastrow
        WS.Range("E1").Value = WS.Name
    Next WS
    Application.ScreenUpdating = True
End Sub

 

[Paul]

That worked!! Thank you!

Was the problem in the way ZeroLast was defined?

Paul

 

[mumps]

You are very welcome. The problem was the Sheet name: Sheets("Sheet2")