PDA

View Full Version : Stop FIND() errors - related to my late post



maxhayden
09-02-2009, 08:52 AM
Hi guys!

I've used some code that you guys have helped me on earlier.


y = UserForm1.TextBox1
If Cells(i, 1) = "" Then
Worksheets("instructionsreceived").Columns(1).Find(y, lookat:=xlPart).EntireRow.Range("A1,C1,F1,G1,J1,K1").Copy

Worksheets("Sheet1").Cells(i, 1).Select
Worksheets("Sheet1").Paste


This is great, BUT if there is a number that is not in "instructionsreceived".Columns(1), an error message comes up and it wants debugging on that line of code.

Is there anyway I can put an if it can't find then Warning.Show

Rather than having the VBA/Excel error message come up??

(Warning being my own userform that informs the user that the number they're trying to find does not exist in the database.

mdmackillop
09-02-2009, 11:19 AM
Set a variable to the found range (if any) and test for Nothing

Dim c As Range, y As String
y = "Test"
Set c = Worksheets("Sheet1").Columns(1).Find(y, lookat:=xlPart)
If Not c Is Nothing Then c.Range("A1,C1,F1,G1,J1,K1").Copy Worksheets("Sheet2").Cells(1, 1)

rbrhodes
09-02-2009, 06:42 PM
Will show the userform:



'Allow not found error
On Error Resume Next

If Cells(i, 1) = "" Then
'Atempt to find
Worksheets("instructionsreceived").Columns(1).Find(y, lookat:=xlPart).EntireRow.Range("A1,C1,F1,G1,J1,K1").Copy

If Err = 0 Then
'Error = 0 = found
Worksheets("Sheet1").Cells(i, 1).Paste
Else
'Not found. Reset for next
Err.Clear
'Inform user
'warning.Show
'Reset Errorhandler to 0 or whatever...
On Error GoTo 0
End If
End If