How can i write in vba the If(isna(vlookup(...) function?

[Hernan_g_f]

Dear all,

I have three columns

A B C
1 1
2 4
3 7
4
5
6
7

I need to write in VBA at column B = If (isna(vlookup(Cells(i,"ColumnA").Value, Range(C1:C100),1,0)),""; vlookup(Cells(i,"ColumnA").Value, Range(C1:C100),1,0))

How can I do it? Thanks in advance!

 

[Hernan_g_f]

Dim s9 as Worksheet
Dim sa, s9 As Worksheet
Set s9 = Sheets("9")
Set sa = Sheets("A")

I'm using to develop a methodology to use time series

 

[Hernan_g_f]

what is s9 ??
and why did you not use the macro I provided ??

I'm using it now... back in five minutes. Thanks

 

[Hernan_g_f]

Michael,

When I try to use your macro, a message appears again: "Compile error: Sub or Function not defined" and the program indicates the first "vlookup" at the right of IsNa

For i = 27 To x2 + 27
s9.Select
Cells(i, 16).Value = WorksheetFunction.If(IsNA(VLookup(Cells(i, 3).Value, Range("BC16:BC100"), 1, 0)), """", VLookup(Cells(i, 3).Value, Range("BC16:BC100"), 1, 0))
Next i

With r3
.Value = .Value
End With

 

[Alex Blakenburg]

Try replacing your lookup line with these 3 lines:

    With Application
        Cells(i, 16).Value = .IfError(.VLookup(Cells(i, 3).Value, Range("BC16:BC100"), 1, 0), "")
    End With

 

[Peter_SSs]

Could I send you the sheet with the macro by email?

To answer this question: NO - Please refer to #4 of the Forum Rules

 

[Hernan_g_f]

Try replacing your lookup line with these 3 lines:

    With Application
        Cells(i, 16).Value = .IfError(.VLookup(Cells(i, 3).Value, Range("BC16:BC100"), 1, 0), "")
    End With

Thank you!!!!!! Very much!!!!!!!! Is running !!! Excellent!!! Best! Hernán!!!

 

[Peter_SSs]

Thank you!!!!!! Very much!!!!!!!! Is running !!! Excellent!!! Best! Hernán!!!

The marked solution has been changed accordingly. In your future questions, please mark the post as the solution that actually answered your question, instead of your feedback message as it will help future readers. No further action is required for this thread.

 

[Alex Blakenburg]

Thank you!!!!!! Very much!!!!!!!! Is running !!! Excellent!!! Best! Hernán!!!

You're welcome. Glad we could help.