Error handling - find data not found

[BatFace]

And another problem....

Excel 2000
I am using the below code to find and mark data over 2 pages - the 2nd page ("Sheet2&quot won't necessarily have data on it though...so I am getting an error message because the data can't be found on sheet2...the On Error Goto Nextstep works fine to skip this in the first half of code, but I cannot get any kind of error handler to work within the loop (Error stops at the c.Offset(0, 12).Select, with "select method of range class failed&quot...

I might be going about this in completely the wrong way - so any suggestions on tidying this up will be most welcome - the best way for me to learn!

Thanks in advance for your help

sub marksale ()

Dim strFindText As String
Sheets("Sheet1&quot.Select
strFindText = InputBox("Please enter number of sale item", "Enter Number&quot

If strFindText = "" Then Exit Sub
On Error GoTo wrongrego

Sheets("Sheet1&quot.Select
With Sheets("Sheet1&quot.Range("d:d&quot
Set c = .FIND(strFindText, LookIn:=xlValues)
c.Offset(0, 13).Select
ActiveCell.FormulaR1C1 = "Sale"
Range(Selection, Cells(ActiveCell.Row, 1)).Select
Selection.Font.ColorIndex = 5

On Error GoTo nextstep
With Sheets("Sheet2&quot.Range("a:a&quot
Set c = .FIND(strFindText, LookIn:=xlValues)
c.Offset(0, 12).Select
ActiveCell.FormulaR1C1 = "Sale"
Range(Selection, Cells(ActiveCell.Row, 1)).Select
Selection.Font.ColorIndex = 5
Sheets("Sheet1&quot.Select

nextstep:
msg = "Would you like to mark another entry?"
ans = MsgBox(msg, vbYesNo, "Mark Other Entry&quot

Do Until ans = vbNo

Sheets("Sheet1&quot.Select
strFindText = InputBox("Please enter number of sale item", "Enter Number&quot

If strFindText = "" Then Exit Sub

Sheets("Sheet1&quot.Select
With Sheets("Sheet1&quot.Range("d:d&quot
Set c = .FIND(strFindText, LookIn:=xlValues)
c.Offset(0, 13).Select
ActiveCell.FormulaR1C1 = "Sale"
Range(Selection, Cells(ActiveCell.Row, 1)).Select
Selection.Font.ColorIndex = 5

With Sheets("Sheet2&quot.Range("a:a&quot
Set c = .FIND(strFindText, LookIn:=xlValues)
c.Offset(0, 12).Select 'error stops here
ActiveCell.FormulaR1C1 = "Sale"
Range(Selection, Cells(ActiveCell.Row, 1)).Select
Selection.Font.ColorIndex = 5
Sheets("Sheet1&quot.Select
Application.ScreenUpdating = True

msg = "Would you like to mark another entry?"
ans = MsgBox(msg, vbYesNo, "Mark Other Entry&quot

End With
End With

Loop

Exit Sub
wrongrego:
MsgBox "You have entered invalid data, please try again", , "Invalid Data Entry"


I really appreciate your help - have been trying to figure this out for hours!

 

[Loomah]

BatFace
I'd just started a long drawn ot reason that was completely wrong! Anyway, people kept interupting me with work!

You appear to have forgotten to select "sheet2"

Try that and see if you have the same problem. Before you do that, remove you error handling and your code may well crash at the first stage.

As a point of interest, your code could probably be shortened by just using the search within your Do..Loop, and speeded up by not selecting each cell and just referencing them. If you need more guidance, let me know.

;-) If a man says something and there are no women there to hear him, is he still wrong?
"...Three Lions On A Shirt..."

 

[BatFace]

Thanks Loomah,

I changed it to the below code which is working (so far)...and changed my loop around - much better...

Sheets("Sheet2&quot.Select
With Sheets("Sheet2&quot.Range("a:a&quot
Set c = .FIND(strFindText, LookIn:=xlValues)
If Not c Is Nothing Then
c.Offset(0, 13).Select
ActiveCell.FormulaR1C1 = "Sale"
Range(Selection, Cells(ActiveCell.Row, 1)).Select
Selection.Font.ColorIndex = 5
If C Is Nothing Then
Resume Next
End If
End If
End With

Thanks for your help!