Still trying to find the date

[bpotter]

I think I have the code to loop through worksheets to find the date on
the correct sheet. Now I am getting an error called type mismatch.

here is my code for some reason it isn't pulling the data from the cell
into lookup

Private Sub workbook_open()
Dim ws As Worksheet
Dim today As Long
Dim lookup As Long
today = Date
For Each ws In ActiveWorkbook.Worksheets
lookup = ws.Cells("b3:b3").Value
If lookup = today Then
ws.Activate

End If
Next


End Sub

 

[Dave Peterson]

How about:

Option Explicit
Private Sub workbook_open()
Dim ws As Worksheet
Dim today As Long
Dim lookup As Long
Dim FoundIt As Boolean
FoundIt = False
today = Date
For Each ws In ActiveWorkbook.Worksheets
'notice the .value2 here
If IsNumeric(ws.Range("B3").Value2) Then
lookup = CLng(ws.Range("b3").Value2)
If lookup = today Then
ws.Activate
FoundIt = True
Exit For
End If
End If
Next ws

If FoundIt = False Then
MsgBox "It wasn't found!"
End If
End Sub

And .cells("B3:B3") would have to be .cells(3,"B") or .cells(3,3). But
..Range("B3") is prettier in this case.

 

[bpotter]

Thanks That helped alot!!!