CODEWITHSUNDEEP
excelvba
Leean
Leean

Reputation: 31

Translate a worksheet formula in VBA

Can somebody help me to write this formula in excel VBA?

=IF(ISERROR(VLOOKUP(A3,Temp!$A$3:$A$595,1,FALSE)),A3,"0")

My code is getting stuck with :"syntax error"

Sub checkDuplitems()

    Application.ScreenUpdating = False
    Const top As Integer = 3
    Dim bottom As Long
    bottom = Sheets("Temp").Cells(Rows.Count, top).End(xlUp).row
       With ThisWorkbook.Sheets("trash").Range("A" & top & ":A" & bottom)
        .Formula = "=IF(ISERROR(VLOOKUP(A" & top & ",Temp!$B$" & top & ":$B$" & bottom & _
                   ",1,FALSE)),A" & top & ", & '" 0" & ," '")"
        .Value = .Value
        .SortSpecial
    End With
   'Call something...
End Sub

Upvotes: 1

Views: 214

Answers (2)

user4039065
user4039065

Reputation:

Are you sure you want to use top as both the starting row in column A and the column to get the bottom row from the Temp worksheet? The important column on the Temp worksheet is column B (i.e. 2) not C (i.e. 3).

If you are putting formula(s) into Trash!A3:A595 that reference Trash!A3:A595 then these are circular references and cannot be resolved under normal conditions. I'll put the formulas into column Z.

If you are operating with Excel 2007 or newer then I would humbly propose this alternate that uses the worksheet's IFERROR function and does not attempt to make text out of the 0 returned value.

Const top As Integer = 3
Dim bottom As Long
bottom = Sheets("Temp").Cells(Rows.Count, "B").End(xlUp).Row  '<~~change here
With ThisWorkbook.Sheets("trash")
    With .Range("Z" & top, .Cells(Rows.Count, "A").End(xlUp).Offset(0, 25))
        .Formula = "=IFERROR(VLOOKUP(A" & top & ", Temp!$B$" & top & ":$B$" & bottom & _
               ", 1, FALSE), 0)"  '<~~ big change here
        .Value = .Value
   End With
End With

It is also curious as to why the number of rows of formulas in the Trash worksheet must be governed by the number of rows of data in the Temp worksheet. I would have thought that the number of values in column A of the Trash sheet should govern how many formulas go into the Trash worksheet.

Upvotes: 1

joehanna
joehanna

Reputation: 1489

You have a concatenation problem in the second line of the .Formula line.

To emulate the formula you have at the top of your question (which is wrong incidentally because you should be pointing to $B$3:$B$595 or something like that because your look up cell A3 should not be inside the VLOOKUP range).

Try this new .Formula line:-

    .Formula = "=IF(ISERROR(VLOOKUP(A" & top & ",Temp!$B$" & top & ":$B$" & bottom & _
               ",1,FALSE)),A" & top & ", " & "0)"

Upvotes: 1

Related Questions