Error in finding value on column

[gio123bg]

Hi All,
I'm trying to find the row number where a value (contract_number) is set in a specific column.

More exactly the VBA code is the following

contract_number = Sheet3.Cells(countR1, 15).Value
row_contract_number = Sheet12.Cells.Find(What:=contract_number, After:=Sheet12.Range("A1"), LookIn:=xlValues, LookAt:= xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

If the value is present, this VBA code returns the number of the row but the value is not present (variable empty) the error message is the following

Run-time error '91':

Object variable or With block variable not set

I wont to test this variable, for example

IF row_contract_number > 0 THEN ... This check works if the data is found in the column.


Any suggestion will be well appreciate.

Thanks in advance for your kind support.

Regards,

Giovanni

 

[VoG]

Try like this

Dim Found As Range
contract_number = Sheet3.Cells(countR1, 15).Value
Set Found = Sheet12.Cells.Find(What:=contract_number, After:=Sheet12.Range("A1"), LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
If Found Is Nothing Then
    MsgBox "Not found"
    Exit Sub
Else
    row_contract_number = Found.Row
End If

 

[Saagar]

You will need to use "error handling routines" to deal with this.

Add this line:
on error goto line1

before
row_contract_number = Sheet12.Cells.Find(What:=contract_number, After:=Sheet12.Range("A1"), LookIn:=xlValues, LookAt:= xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

insert this statement after your if loop.
line1:

If the find statement errors when the contract number is not found, the code jumps to line1 after the if statement. You can make the code jump to any line you want.

For more information refer to error handling in excel help.

 

[gio123bg]

Hi All,
Thanks a lot for your kind support and suggestions. Now the macro work correctly applying your suggestion.

Thanks!

Regards,

Giovanni