Using 'find' in VBA code

[JimM]

Guys

I'm trying to write some code that will search a sheet for variable1 and then assign the row number that the result is in to variable2

Got as far as

variable2 = Sheets("Sheet1").Range("J:J").Find(What:=variable1, LookAt:=xlPart).Row


However if variable1 isn't found it bombs out. How would I go about setting variable2 to 1000 when this happens

Thanks

Jim

 

[VoG]

Try like this

Dim Found As Range
Set Found = Sheets("Sheet1").Range("J:J").Find(What:=variable1, LookAt:=xlPart)
If Found Is Nothing Then
    variable2 = 1000
Else
    variable2 = Found.Row
End If

 

[prajul89]

set variable2 = Sheets("Sheet1").Range("J:J").Find(What:=variable1, LookAt:=xlPart)

If variable2 Is Nothing Then ' whatever you want to do

hope this helpssssssssss

 

[jim may]

Try:

Sub Foo()
variable1 = "Test"  << Change to your variable1
On Error GoTo errhandler
Variable2 = Sheets("Sheet1").Range("J:J").Find(What:=variable1, LookAt:=xlPart).Row
MsgBox "Found in Row " & Variable2
Exit Sub
errhandler:
Variable2 = 1000
MsgBox "Didn't Find variable1, so assigned " & Variable2
On Error GoTo 0
End Sub

 

[prajul89]

Try:

Sub Foo()
variable1 = "Test"  << Change to your variable1
On Error GoTo errhandler
Variable2 = Sheets("Sheet1").Range("J:J").Find(What:=variable1, LookAt:=xlPart).Row
MsgBox "Found in Row " & Variable2
Exit Sub
errhandler:
Variable2 = 1000
MsgBox "Didn't Find variable1, so assigned " & Variable2
On Error GoTo 0
End Sub

why use error handler when you can set the variable?
I think error handler aren't that safe.

 

[jim may]

Different strokes, for different folks;
error handling such as this is used extensively, according to what I've seen.

 

[prajul89]

Different strokes, for different folks;
error handling such as this is used extensively, according to what I've seen.

whatever u say. Thank for improving my knowledge.