CODEWITHSUNDEEP
vbavlookup
user3904713
user3904713

Reputation: 1

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

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

Upvotes: 0

Views: 998

Answers (1)

L42
L42

Reputation: 19737

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.

Upvotes: 1

Related Questions