I have been running this macro for the last 4 weeks with success until the
date changed to 01-Apr-08 when it started giving the error message below I
have checked the format of the date cells and they are ok. Any ideas why it
is not working

Macro is
Sub GOTOTOALL()
Range("D17:HO17").Find(Format(Now(), "dd-mmm-yy"), , xlValues).Select
End Sub

Error
Run Time error: "91"
Object variable or With block variable not set


Best regards

Stuart

Untested:

Option Explicit
Sub GOTOTOALL()
Dim FoundCell As Range
With ActiveSheet.Range("D17:HO17")
Set FoundCell = .Cells.Find(what:=CLng(Date), _
after:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
lookat:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
End With

If FoundCell Is Nothing Then
Beep 'not found
Else
FoundCell.Select
End If
End Sub

But dates in VBA can be painful to work with (as you've seen!).

Since you're only looking in a single row, you may want to try this if the first
suggestion doesn't work:

Option Explicit
Sub GOTOTOALL2()
Dim res As Variant
Dim myRng As Range

Set myRng = ActiveSheet.Range("D17:HO17")

res = Application.Match(CLng(Date), myRng, 0)

If IsError(res) Then
Beep
Else
myRng(res).Select
End If
End Sub

Stuart WJG wrote:

I have been running this macro for the last 4 weeks with success until the
date changed to 01-Apr-08 when it started giving the error message below I
have checked the format of the date cells and they are ok. Any ideas why it
is not working

Macro is
Sub GOTOTOALL()
Range("D17:HO17").Find(Format(Now(), "dd-mmm-yy"), , xlValues).Select
End Sub

Error
Run Time error: "91"
Object variable or With block variable not set

Best regards

Stuart

--

Dave Peterson

Dave
Used 2nd option. Fantastic done the job
agreat help

Thanks

"Dave Peterson" wrote:

Untested:

Option Explicit
Sub GOTOTOALL()
Dim FoundCell As Range
With ActiveSheet.Range("D17:HO17")
Set FoundCell = .Cells.Find(what:=CLng(Date), _
after:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
lookat:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
End With

If FoundCell Is Nothing Then
Beep 'not found
Else
FoundCell.Select
End If
End Sub

But dates in VBA can be painful to work with (as you've seen!).

Since you're only looking in a single row, you may want to try this if the first
suggestion doesn't work:

Option Explicit
Sub GOTOTOALL2()
Dim res As Variant
Dim myRng As Range

Set myRng = ActiveSheet.Range("D17:HO17")

res = Application.Match(CLng(Date), myRng, 0)

If IsError(res) Then
Beep
Else
myRng(res).Select
End If
End Sub

Stuart WJG wrote:

I have been running this macro for the last 4 weeks with success until the
date changed to 01-Apr-08 when it started giving the error message below I
have checked the format of the date cells and they are ok. Any ideas why it
is not working

Macro is
Sub GOTOTOALL()
Range("D17:HO17").Find(Format(Now(), "dd-mmm-yy"), , xlValues).Select
End Sub

Error
Run Time error: "91"
Object variable or With block variable not set

Best regards

Stuart

--

Dave Peterson