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!

 

[Michael M]

How about

 = If (isna(vlookup(Cells(i,1).Value, Range(C1:C100),1,0)),""""; vlookup(Cells(i,1).Value, Range(C1:C100),1,0))

 

[Hernan_g_f]

I wrote

For i = 27 To 37

s9.Cells(i, 16).Value = If (isna(vlookup(s9.Cells(i,3).Value, Range(BC16:BC4016),1,0));"""",1))

Next i

And appears a messange: Compile error Expected: expression and VBA mark "If"

How can I resolve it?

 

[Michael M]

That appears to be a different line of code than one originally posted.
It would be easier if you posted ALL of the code, not one line !

 

[Hernan_g_f]

Dim r1, r2 As Range
Set r1 = s9.Range("BC16:BC4016")
Set r2 = s9.Range("C27:C5027")
Set r3 = s9.Range("P27:P5027")

r2.Select
Selection.Style = "Comma"

x1 = WorksheetFunction.CountIf(r1, ">0")
x2 = WorksheetFunction.CountIf(r2, ">0")

For i = 27 To x2 + 27
s9.Cells(i, 16).Value = If (isna(vlookup(Cells(i,3).Value, Range(BC16:BC100),1,0)),""""; vlookup(Cells(i,3).Value, Range(BC16:BC100),1,0))
Next i

r3.Select
Selection.Copy
r3.Select
Selection.PasteSpecial xlValues

 

[Skyybot]

It appears that you are trying to use a Worksheet Function in VBA without using the WorksheetFunction method.

 

[Hernan_g_f]

Ok. Can you give me an example?

Thanks!

 

[Michael M]

Sub MM1()
Dim r1, r2 As Range
Set r1 = s9.Range("BC16:BC4016")
Set r2 = s9.Range("C27:C5027")
Set r3 = s9.Range("P27:P5027")

r2.Select
Selection.Style = "Comma"

x1 = WorksheetFunction.CountIf(r1, ">0")
x2 = WorksheetFunction.CountIf(r2, ">0")

For i = 27 To x2 + 27
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
End Sub

 

[Hernan_g_f]

The trouble persist...
¿Could I send you the sheet with the macro by email? Thanks!

Dim r1, r2 As Range
Set r1 = s9.Range("BC16:BC4016")
Set r2 = s9.Range("C27:C5027")
Set r3 = s9.Range("P27:P5027")

r2.Select
Selection.Style = "Comma"

x1 = WorksheetFunction.CountIf(r1, ">0")
x2 = WorksheetFunction.CountIf(r2, ">0")

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

With r3
.Value = .Value
End With

 

[Michael M]

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