IfNa+Vlookup lead to an error 1004

[Saab95]

Hi there,

I tried to do a VBA that insert a formula into a specific cell.

Formula is:
Range("Y2").Formula = "=IfNa(VLOOKUP(X2,ECB!$A$2:$AA$10000,25,FALSE),"")"
Range("Y2").AutoFill Range("Y2:Y20000")

But I get an run time error 1004 Application defined or object defined error.

I have also tried with :
Sub Test()
With Worksheets("Master Sheet").Range("V2").Formula = "=IFNA(VLOOKUP(U2,'Collateral UCCS'!A1:R20000,18,FALSE,"")"
End With
End Sub

This time no error but nothing is inserted in the cell.

Been thru many threads but can't get a solution.

Strangely I had no issue with a simple VLOOKUP with no IfNa
Range("R2").Formula = "=VLOOKUP(A2,'PALA QLR'!$A$2:$F$4998,2,0)"
Range("R2").AutoFill Range("R2:R20000")

If you can help.

 

[RoryA]

You need to double up any quotes within the quoted formula string:

Range("Y2").Formula = "=IfNa(VLOOKUP(X2,ECB!$A$2:$AA$10000,25,FALSE),"""")"

 

[Fluff]

Hi & welcome to MrExcel.
You need to double-up on the quotes inside the formula like

Range("Y2").Formula = "=IfNa(VLOOKUP(X2,ECB!$A$2:$AA$10000,25,FALSE),"""")"

 

[Saab95]

Awesome, thank you all !!

 

[Fluff]

Glad we could help & thanks for the feedback.