CODEWITHSUNDEEP
vbaexcelautomationvlookup
lookininward
lookininward

Reputation: 671

How to get VLOOKUP to select down to the lowest row in VBA?

Looking to automate the insertion of a VLOOKUP formula in a cell. When recording the macro I instruct it to populate the columns below with the same formula. Works great, however, there is an issue when the table that the VLOOKUP searches through changes (more or less rows).

As it's recorded, the VLOOKUP drops down to the final row in the table (273). However, I want to set it up so that it will go down to the very last row. Meaning that I can run the script on tables of varying numbers of rows.

Selected columns will remain the same. 

Range("AJ2").Select
ActiveCell.FormulaR1C1 = _
    "=VLOOKUP(RC[-20], Previous!R2C2:R273C22,17,FALSE)"

Upvotes: 0

Views: 1050

Answers (2)

user3598756
user3598756

Reputation: 29421

try this:

With Worksheets("Previous")
    Range("AJ2").FormulaR1C1 = _
        "=VLOOKUP(RC[-20], Previous!R2C2:R" & .Cells(.Rows.Count, 2).End(xlUp).Row & "C22,17,FALSE)"
End With

where:

  • Range("AJ2")

    will implicitly reference the ActiveSheet

  • .Cells(.Rows.Count, 2).End(xlUp).Row

    will reference "Previous" worksheet, being inside a With Worksheets("Previous")- End With block

Upvotes: 1

mojo3340
mojo3340

Reputation: 549

@nbayly said it, plenty of posts on this. Infact i have provided an answer to this before here:

How to Replace RC Formula Value with Variable

below is slightly modified for a dynamic range, which is what i believe you are looking for

For j = n To 10 Step -1
    If Cells(j, 1).Value = "" Then
        Cells(j, 1).Formula = "=VLookup(RC20,Previous!R2C2:R273C22,17,FALSE)"
    End If
Next j

remember to define j as long and n=sheets("sheetname)".cells(rows.count,1).end(xlup).row

replace 10 in j = n to 10 with the starting row number

Upvotes: 1

Related Questions