Find in VBA

Topic

New Reply

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.

Reply | Quote

Replies

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

You could try something like:

Code:

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

Reply | Quote

Brilliant, thankyou very much.

Reply | Quote