Fill Down to Last Row

[Sphinx404]

I pretty much recorded a macro and have decided that because of my lack of VBA skills, this would be the easiest route. However I have 2 problems with this. My range selection for VLOOKUP is not static and will change, thus I need to come up with a way to select all the same columns, but the selection needs to go to the bottom of the data set. (Also, when I fill the formula down after completing the initial VLOOKUP).

If anyone could simplify this for me, I would be grateful.

    Range("Y2").Select
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC[-4],'DG by Flt Totals'![B][COLOR=#ff0000]R2C13:R6982C27[/COLOR][/B],15,0)"
    Range("Y2").Select
    Selection.AutoFill Destination:=Range("[B][COLOR=#ff0000]Y2:Y1947[/COLOR][/B]")
    Range("Z2").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[-1]=RC[-11],""TRUE"",""FALSE"")"
    Range("Z2").Select
    Selection.AutoFill Destination:=Range("[B][COLOR=#ff0000]Z2:Z1947[/COLOR][/B]")

All of the above in RED essentially needs to become something that goes to the last row in the data set.

If you have the time, I would really like to know how code a VLOOKUP macro without having to use the record button. I'm really not a fan of FormulaR1C1

Thanks guys!

-STEVE

 

[Oaktree]

So, three concepts:

1) To find the last used row... I assume there will be a column you can anchor off of. For example, if column A for a row is blank, that means it's safe to assume the rest of the columns are blank too. If that's true, you can use

cells(rows.count,1).end(xlup).row

to find the last non-blank cell in column A, which we assume to be the last non-blank row. If column B is your anchor point instead, you'd change ,1 to ,2 respectively.

2) It's safer to use sheet names or, better, sheet code names in case you accidentally run your code with a different tab selected. In the VBA project window, you'll see something like Sheet2 (DG by Flt Totals). That tells you the name of the sheet is "DG by Flt Totals", but the code name is "Sheet2". By referencing code names instead of sheet names, your code will still work if/when sheet tabs get renamed. I assume you're entering the formulas on Sheet1 and that "DG by Flt Totals" is Sheet2. If that's not right, update the code names in the code below accordingly.

3) R1C1 is incredibly powerful, so don't throw it out the window just yet! But, you can also use .formula instead of .formulaR1C1 if you want to type A1 formulas.

Combining those 3 concepts, try this:

Sub NameMyMacro()

'Populate column Y
Sheet1.Range("Y2").Formula = _
    "=VLOOKUP(U2,'DG by Flt Totals'!$M$2:$AA$" & Sheet2.Cells(Rows.Count, 1).End(xlUp).Row & ",15,0)"
Sheet1.Range("Y2:Y" & Sheet1.Cells(Rows.Count, 1).End(xlUp).Row).FillDown

'Populate column Z
Sheet1.Range("Z2").Formula = "=IF(Y2=O2,True,False)"
Sheet1.Range("Z2:Z" & Sheet1.Cells(Rows.Count, 1).End(xlUp).Row).FillDown

End Sub