On error goto not working

[pulsecoding]

hi all,

I am using xp office.

below code is showing error " run time error 91: object variable or with block variable not set.

On Error GoTo stop1:

start_to_be_copy = ActiveWorkbook.Sheets("Collated cases").Range("a4:a65000").Find(p).Row
end_of_copy = ActiveWorkbook.Sheets("Collated cases").Range("a4:a65000").Find(p, SearchDirection:=xlPrevious).Row

stop1:

 

[Richard Schollar]

Hi

Don't use On Error in this instance, use object variables to set references to the sought values:

Dim r1 As Range, r2 As Range

Set r1 = ActiveWorkbook.Sheets("Collated cases").Range("a4:a65000").Find(p)

If Not r1 Is Nothing Then
  Set r2 = ActiveWorkbook.Sheets("Collated cases").Range("a4:a65000").Find(p, After:=r1,SearchDirection:=xlPrevious)
  start_to_be_copy = r1.Row
  end_of_copy = r2.Row
  'rest of code

Else
  MsgBox p & " not found in specified range!"
End If

 

[pulsecoding]

Many many many Thanks. It working fine.