Find in VBA

[kentg]

I recorded a macro which worked the first time but now it get an error - error 91 - 'object variable or with block variable not set'

Cells.Find(What:="Sheet No :", After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

I just can't figure out what is causing the error. I recorded a new macro which again runs the first time and then gets the error.

[sdckapr]

You get that error if the search does not find anything since it can not activate nothing

You could try something like:

Dim rFind As Range
Set rFind = Cells.Find(What:="Sheet No :", _
  After:=ActiveCell, LookIn:=xlValues, _
  LookAt:=xlPart, SearchOrder:=xlByRows, _
  SearchDirection:=xlNext, MatchCase:=False, _
  SearchFormat:=False)

If rFind Is Nothing Then
  MsgBox "not found"
Else
  rFind.Activate
End If

Steve

[kentg]

Brilliant, thankyou very much.