CODEWITHSUNDEEP
vbaexcelvlookup
Stu
Stu

Reputation: 47

Need help to input If and Vlookup formula into cell using VBA

I created a button which adds a new job entry into my job database. Based on the value in Cells (emptyRow,40), Vlookup with search "Job Category" Worksheet and output the results in Cells (emptyRow,43). Hence, each time I am adding a new job entry, I would like Cells(emptyRow,43) to input the formula and tell me what job this falls under, as shown below 

Dim emptyRow As Long
emptyRow = Range("Z100000").End(xlUp).Offset(1, 0).Row
Cells(emptyRow, 43).Formula = "=IF(Cells(emptyRow,40)=0, "", VLOOKUP(Cells(emptyRow,40),'Job Category'!$G$10:$I$12, 3, TRUE))"

However, I get this message: "Run-time error '1004' : Application-defined or object-defined error."

Anyone knows why?

Upvotes: 2

Views: 208

Answers (3)

Davesexcel
Davesexcel

Reputation: 6984

You can use iferror as well, 

Sub Button1_Click()
    Dim emptyRow As Long

    emptyRow = Cells(Rows.Count, "Z").End(xlUp).Row + 1
    Cells(emptyRow, 43).Formula = "=IFERROR(VLOOKUP(AN" & emptyRow & ",'Job Category'!$G$10:$I$12,3,1),"""")"

End Sub

Upvotes: 0

Tim Williams
Tim Williams

Reputation: 166540

Dim emptyRow As Long, addr as string
emptyRow = Range("Z100000").End(xlUp).Offset(1, 0).Row
addr = Cells(emptyRow,40).Address()

Cells(emptyRow, 43).Formula = "=IF(" & addr & "=0, """", VLOOKUP(" & _
                              addr & ",'Job Category'!$G$10:$I$12, 3, TRUE))"

Upvotes: 1

user4039065
user4039065

Reputation:

You are mashing together worksheet function syntax and VBA syntax. While you can use a WorksheetFunction object to return the result from a VLOOKUP function, it seems that you want to write the formula into the cell. This may be better as an xlR1C1 style formula due to the emptyRow variable.

'as an xlA1 style
 Cells(emptyRow, 43).Formula = _
    "=IF(AN" & emptyRow & "=0, """", VLOOKUP(AN" & emptyRow & ",'Job Category'!$G$10:$I$12, 3, TRUE))"
'as an xlR1C1 style
 Cells(emptyRow, 43).FormulaR1C1 = _
    "=IF(RC40=0, """", VLOOKUP(RC40, 'Job Category'!R10C7:R12C9, 3, TRUE))"

Remember that when including quotes within a quoted string, you need to double up the quotes.

Upvotes: 2

Related Questions