VBA: Placing a forumula down a column using a vlookup formula

Question

Below I am attempting to place the formula just to the right of the last column, beginning at row 2. I know the For statement works, as well as the searching for last column/ row as i've used this in a previous macro when placing a formula down a column. The only question I have is how do I make the VLookup formula work properly?

End goal: 1) Forumla on column to the right of last one 2) Vlookup looksup the value in the last column on the given row within the For statement on a tab called "Lookup" 3) On this Lookup tab, column A is where the value will be found, but I need to return the second column value.

Please zero in on the forumula beginning with the "=iferror(...". I currently receive the error, "Application Defined or Object-Defined" error.

EThree = Cells(Rows.Count, 4).End(xlUp).Row
NumThree = Evaluate("=COUNTA(9:9)")

For r = 2 To EThree
    Cells(r, NumThree + 2).Formula = "=IFERROR(((Vlookup(" & Cells(r, 14).Value & ",Lookup!$A:$B,2,0)""))))"

Next

I think your problem is this: Cells(r, 14).Address(False, False) — David Zemens
That didn't resolve the issue. I am still receiving the error "Application-defined or object defined error". I've used this Address function in the same way before, but this is the only time, I've had difficulty. My feeling is the problem resides in how i have the Vlookup forumula typed and my "&"'s potentially. — user3904713
Can you revise your question to show the code as it currently exists (i.e., with that modification I suggested)? Let me see what you're working with and probably can point you in the right direction :) — David Zemens
Crap there was a typo in my comment, it should have been Cells(r, 14).Value, not ...Address.Value. Try that first. — David Zemens

L42 L42 · Accepted Answer · 2014-09-23T23:55:30

You can place your formula in one go; no need to loop.
Try this:

With Sheets("NameOfWorksheet") '~~> change to suit
    '~~> first get the last row and column
    Dim lrow As Long, lcol As Long
    lrow = .Range("D" & .Rows.Count).End(xlUp).Row
    lcol = .Cells(9, .Columns.Count).End(xlToLeft).Column
    Dim rngToFillFormula As Range, mylookup As String
    '~~> get the lookup value address
    mylookup = .Cells(2, lcol).Address(False, False, xlA1)
    '~~> set the range you need to fill your formula
    Set rngToFillFormula = .Range(.Cells(2, lcol), Cells(lrow, lcol)).Offset(0, 1)
    rngToFillFormula.Formula = "=IFERROR(VLOOKUP(" & mylookup & _
        ",Lookup!A:B,2,0),"""")"
End With

What we did is explained in the comments. HTH.

VBA: Placing a forumula down a column using a vlookup formula

1 Answers