VLookup between two workbooks combined with ISNA function. Run-time Error '1004': Application defined or object defined error

Question

I am working on ws1 which is a worksheet in "New" workbook. it contains Bill No in column 1 and other relevant information in other columns. I want to Vlookup in another workbook called "PDA" and in that worksheet with sheet name "Sheet1" which has Bill no in Column F and the data that I want to copy in column G. If the Bill No is not present in PDA>Sheet1, then I want to return the value 0, because I have to sum the amount later. I have written this code, but it is giving error. Kindly help me with the issue. I am getting error in this line

.Cells(i, 4) = "=If(ISNA(VLookup(.Cells(i, 1).Value2, y, 2, False)),0,VLookup(.Cells(i, 1).Value2, y, 2, False))"

Dim i As Long
Dim x As Range
Dim b As Range
Dim wb1 As Workbook
Dim ws1 As Worksheet
Set wb1 = Workbooks.Open("C:\Users\mrisingh\Desktop\6-Eccs Billing List.xlsx")
Set x = wb1.Worksheets("Billing Sheet").Range("B2:E100000")
Set ws1 = Workbooks("New").Worksheets("Reconciliation")
With ws1
For i = 2 To .Cells(Rows.Count, 1).End(xlUp).Row
.Cells(i, 3) = Application.VLookup(.Cells(i, 1).Value2, x, 4, False)
Next i
End With
Set b = wb1.Worksheets("Billing Sheet").Range("B2:K100000")
With ws1
For i = 2 To .Cells(Rows.Count, 1).End(xlUp).Row
.Cells(i, 9) = Application.VLookup(.Cells(i, 1).Value2, b, 10, False)
Next i
End With
wb1.Close savechanges:=False

Dim y As Range
Dim wb2 As Workbook
Set wb2 = Workbooks.Open("C:\Users\mrisingh\Desktop\PDA.xlsx")
Set y = wb2.Worksheets("Sheet1").Range("F845:G10000")
With ws1
For i = 2 To .Cells(Rows.Count, 1).End(xlUp).Row
.Cells(i, 4) = "=If(ISNA(VLookup(.Cells(i, 1).Value2, y, 2, False)),0,VLookup(.Cells(i, 1).Value2, y, 2, False))"
Next i
End With
wb2.Close savechanges:=False

Answer 1

You are mixing VBA pieces into an Excel formula - that will not work. Excel does not know what .Cells(i, 1).Value2 or y means, but that is what you write into the cell. VBA, on the other hand, doesn't replace .Cells(i, 1).Value2 or y with the range itself as is it inside a constant string (within quotes), and VBA leaves those strings alone.

So a first attempt would be to write (untested as I don't have your data, but you hopefully get the idea)

dim lookupAdr as string, f as string
lookupAdr = "[" & wb2.name & "]" & "Sheet1!" & y.address
f = "=If(ISNA(VLookup(" & .Cells(i, 1).address & ", " _
    & lookupAdr & ", 2, False)),0,VLookup( " _
    & .Cells(i, 1).address & ", " & lookupAdr & ", 2, False))"
Debug.Print lookupAdr
Debug.Print f
.Cells(i, 4).formula = f

Note that I write the formula and the address of the lookup first into string variables - this helps to find errors. Note also that you should write formulas always to the property .formula of a cell, not to the .value property.

However, there is room for improvement: Unless you are still on Excel 2003, you can use the IFERROR formula so that you don't have to repeat the VLOOKUP-part. This would lead to a much easier formula (again untested):

 f = "=IFERROR(VLookup( " & .Cells(i, 1).address & ", " & lookupAdr & ", 2, False), 0)"

As a general rule when you try to create a formula within VBA, it is often helpfull to go the different way first: Write the final formula that you want to have into one cell, go to the VBA-Editor, open the Immediate Window (press [Ctrl]+[G]) and enter ? activecell.formula. Then, write your code and compare the formula you create within VBA with the one you saw in the immediate window.

And, as advice: Use meaningfull variable names. y is not really a good name for a range variable - use something like lookupRange. Everyone that will read the code (and that includes you!) will be thankfull.