VBA to find #N/A in column

[aaleem]

Hi,

im trying to amend an existing code to find the text "#N/A" in column L then go for the Vlookup.

while doing so im getting the Method 'Range' of object_Global failed error.

please see below the code

With ws1
        lLastRow = .Cells(.Rows.Count, 2).End(xlUp).Row
        arr = .Range("A2:M" & lLastRow)
        For i = LBound(arr) To UBound(arr)
            
            If Range(arr(i, 12)).Text = "#N/A" Then
            If Len(arr(i, 1)) > 0 Then

                arr(i, 13) = Application.VLookup(arr(i, 1), ws3.Range("A:C"), 3, False)
            End If
        End If
    Next
    .Range("M1:M" & lLastRow) = Application.Index(arr, 0, 13)
    End With

any suggestion will be highly appreciated.
thanks,
Aleem

 

[Fluff]

Try

If arr(i, 12) = "#N/A" Then

 

[aaleem]

Try

If arr(i, 12) = "#N/A" Then

Thank you so much Fluff for your time, i tried the above line im getting the Type mismatch error on the rows where #N/A values are there in column L.

 

[Fluff]

Will you have any error values in col L other than #N/A & if so do you want to still do the vlookup?

 

[aaleem]

Will you have any error values in col L other than #N/A & if so do you want to still do the vlookup?

HI Fluff, i have text values including #N/A and blank rows, no other errors on column L.

 

[Fluff]

In that case try

If IsError(arr(i, 12)) Then

 

[aaleem]

In that case try

If IsError(arr(i, 12)) Then

Thank you so much Fluff, it is working perfectly.

 

[Fluff]

You're welcome & thanks for the feedback.