How to check if the cell value contains error as “#N/A” in a Loop

[ThrottleWorks]

Hi,


I am checking below mentioned condition in a Loop.

If Column 1 has valid value and Column 2 has value as “#N/A” then I need to update Column 3.


I am getting error while running below mentioned code.

When the Rng value is “#N/A” macro is showing a bug.


I checked the Rng value in immediate window and it shows as Error 2042.


I can replace “#N/A” manually with N/A and run the edited loop.

However I do not know how to check if the Rng value is Error 2042.


Can anyone please help me how to edit below mentioned code so that it will identify “#N/A” from the Rng value.


For Each Rng In Rng7

If Rng.Value <> "#N/A" And Rng.Offset(0, 1).Value = "#N/A" Then

Rng.Offset(0, 2).Value = "Cleared"

End If

Next

 

[NARAYANK991]

Hi Sachin ,

Read up on VBA.IsError

Narayan

 

[ThrottleWorks]

Hi @NARAYANK991 Sir, sure, I will do it. Thanks a lot for the help.

 

[chirayu]

Am I right in Saying that there are 3 columns?
Column 1 you don't want #N/A
Column 2 should be #N/A
If both conditions are met then column 3 should say Cleared else next rng?

For Each Rng In Rng7
    If Not IsError(Rng) And IsError(Rng.Offset(0, 1)) Then
        Rng.Offset(0, 2) = "Cleared"
    End If
Next

 

[Deepak]

Again why loop.

Range("c1:c5")= "=IF(AND(NOT(ISERROR(A1)),ISERROR(B1)),""Cleared"","""")"

 

[ThrottleWorks]

Hi @ deepak sir thanks a lot for the help.

Have a nice weekend.

 

[ThrottleWorks]

Hi @chirayu , thanks for the help.

Below mentioned code is working. Have a nice day ahead.

For Each Rng In Rng7
If Not IsError(Rng) And IsError(Rng.Offset(0, 1)) Then
Rng.Offset(0, 2) = "Cleared"
End If
Next