CODEWITHSUNDEEP
excelvba
Jorge
Jorge

Reputation: 23

Find a value in a column and return value of same row but different column

I would like to find the value "#N/A" through a column and, if it is found, return the value of same row but different column. This is the code I wrote: 

Sub TestFind()

    Dim r1(), r2()

    r1 = Range("B2:B1000")
    r2 = Range("A2:A1000")

    For i = LBound(r1, 1) To UBound(r1, 1)
        If r1(i, 1) = "#N/A" Then r2(i, 1) 
    Next i

    Range("B3:B1000") = r1

End Sub

What am I doing wrong?

Upvotes: 0

Views: 191

Answers (2)

HTH
HTH

Reputation: 2031

use SpecialCells() method of Range object to loop through cells with error only and use CVERR function to check error type:

Dim cel As Range
For Each cel In Range("B2:B1000").SpecialCells(xlCellTypeFormulas, xlErrors) ' loop through cells with errors
   If cel.Value = CVErr(xlErrNA) Then MsgBox cel.Offset(, -1).Value
Next

Upvotes: 1

Dominique
Dominique

Reputation: 17493

I doubt that you can use ""N/A as a real string, you might use WorksheetFunction.IfError() or WorksheetFunction.IfNa() instead.

Moreover, why don't you use the mentioned functions in a formula (which you put in column C, something like:

=IfNA(A2;B2)

By dragging this formula up till the bottom (row 1000), you might get the same result.

Upvotes: 0

Related Questions