"Find" and #N/A - error when not found

[DonAndress]

Hello.

I am trying to check if in column there is a formula returning error #N/A.
I use the below line of code.
If #N/A is found, it works perfectly.
But it it's not found (there is no such error in column) I get 'Object variable or With block variable not set' and macro breaks. I don't have any 'With' block in the code.

searchNA = destWkb.Sheets("temp").Range("H:H").Find("#N/A", LookIn:=xlValues, searchdirection:=xlPrevious, searchorder:=xlByRows).row

where destWkb = ThisWorkbook

May I ask for guidance how to fix it?

 

[Herakles]

Hello.

I am trying to check if in column there is a formula returning error #N/A.
I use the below line of code.
If #N/A is found, it works perfectly.
But it it's not found (there is no such error in column) I get 'Object variable or With block variable not set' and macro breaks. I don't have any 'With' block in the code.

searchNA = destWkb.Sheets("temp").Range("H:H").Find("#N/A", LookIn:=xlValues, searchdirection:=xlPrevious, searchorder:=xlByRows).row

where destWkb = ThisWorkbook

May I ask for guidance how to fix it?

You really need to correct the formulas that produce the errors.

Can you post the formulas and a sample of your data using XL2BB.

Can you update your profile to let people know what version you are on.

 

[Peter_SSs]

May I ask for guidance how to fix it?

What do you want to happen if "#N/A" is not found?

BTW I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Special-K99]

Am no expert at VBA but this seems to work

Sub k1()
Set destWkb = Application.ThisWorkbook
Set searchNA = destWkb.Sheets("Sheet1").Range("A:A").Find("#N/A", LookIn:=xlValues, searchdirection:=xlPrevious, searchorder:=xlByRows)
If Not searchNA Is Nothing Then
    MsgBox searchNA.Row
End If
End Sub

Using .row at the end of the search statement caused the error so I placed it on the MsgBox statement

 

[DonAndress]

You really need to correct the formulas that produce the errors.

Can you post the formulas and a sample of your data using XL2BB.

Can you update your profile to let people know what version you are on.

Well yes, I need to correct my formulas.
But there's nothing really to post.
I have a basic column with vlookups, some of them return #N/A

What do you want to happen if "#N/A" is not found?

BTW I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Well originally this piece of code served me to find and store row of first not empty cell. I wanted to use it to find #N/A and then have a simple IF statement.
BTW, account details updated.

Am no expert at VBA but this seems to work

Sub k1()
Set destWkb = Application.ThisWorkbook
Set searchNA = destWkb.Sheets("Sheet1").Range("A:A").Find("#N/A", LookIn:=xlValues, searchdirection:=xlPrevious, searchorder:=xlByRows)
If Not searchNA Is Nothing Then
    MsgBox searchNA.Row
End If
End Sub

Using .row at the end of the search statement caused the error so I placed it on the MsgBox statement

So, this works, thank you.

Although I don't understand the mechanism.
Why this gives me row number stored in a variable

searchNA = destWkb.Sheets("Sheet1").Range("A:A").Find("*", LookIn:=xlValues, searchdirection:=xlPrevious, searchorder:=xlByRows).Row

And why this does not store Empty/Null when there is no #N/A?

searchNA = destWkb.Sheets("Sheet1").Range("A:A").Find("#N/A", LookIn:=xlValues, searchdirection:=xlPrevious, searchorder:=xlByRows).Row

 

[Alex Blakenburg]

From Microsoft help: Range.Find method (Excel)
• Range.Find returns "A Range object that represents the first cell where that information is found."
• "This method returns Nothing if no match is found"

".Row" is a property of the Range object, so when it doesn't find anything and the Range Object is nothing trying to get the ".Row" property errors out.
(Object Variable is not set)

 

[Peter_SSs]

But there's nothing really to post.

Your existing worksheet formula, which sometimes returns a #N/A error.

I wanted to use it to find #N/A and then have a simple IF statement.

What is the simple IF statement?

BTW, account details updated.

Thanks for doing that.

 

[DonAndress]

Your existing worksheet formula, which sometimes returns a #N/A error.

=VLOOKUP(D1,G1:K100,2,0)

What is the simple IF statement?

if searchNA is nothing then
[some code here]
end if

but this was not working when I still had .Row at the end:
searchNA = destWkb.Sheets("Sheet1").Range("A:A").Find("#N/A", LookIn:=xlValues, searchdirection:=xlPrevious, searchorder:=xlByRows).Row