Autofill Formula to last Row in a Column

[SAMCRO2014]

I have been trying to get my macro to auto fill a vlookup formula from cells E2 to the last row based on how many rows data is the spreadsheet "Data". This number will constantly change.

Here is my coding:

Dim Data As Worksheet
Set Data = ThisWorkbook.Sheets("Data")

Dim LastRowData As Long
LastRowData = Data.Cells(Rows.Count, 1).End(xlUp).Row

' Open Salary GL Workbook to see if the GL numbers are categorized as regular, overtime, premiums, other.

  
    Range("E2").Select
    Workbooks.Open Filename:= _
        "J:\FinanceAdmin\Finance\Resource Management\Regional\2018-19\Salary Reconciliation\Planned vs Actuals\Look up Tables\Salary GL.xlsx"
    Windows("2019.P#.Planned vs Actuals - 1237.v1.RXM.xlsm").Activate
    Range("E2").Select
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC[-2],'[Salary GL.xlsx]Page1_1'!R2C1:R49C3,3,0)"
    Range("E2").Select
    Selection.AutoFill Destination:=Range("E2:E" & LastRowData)

This is the result I get.

-- corrupted image removed --

What I am doing wrong?

 

[Joe4]

When using the R1C1 relative range formula referencing, you can set the formula for the whole range at once without using loops, i.e.

Dim Data As Worksheet
Set Data = ThisWorkbook.Sheets("Data")

Dim LastRowData As Long
LastRowData = Data.Cells(Rows.Count, 1).End(xlUp).Row

' Open Salary GL Workbook to see if the GL numbers are categorized as regular, overtime, premiums, other.

Workbooks.Open Filename:= _
"J:\FinanceAdmin\Finance\Resource Management\Regional\2018-19\Salary Reconciliation\Planned vs Actuals\Look up Tables\Salary GL.xlsx"
Windows("2019.P#.Planned vs Actuals - 1237.v1.RXM.xlsm").Activate
Range("E2:E" & LastRowData).FormulaR1C1 = _
    "=VLOOKUP(RC[-2],'[Salary GL.xlsx]Page1_1'!R2C1:R49C3,3,0)"

 

[SAMCRO2014]

It didn't work. This is what a get for a result:

Cell E1 #N/A
Cell E2 Regular (This is correct)
Cell E3 to Last row in Column is blank

It seems like the autofill is going upwards instead of downward.

 

[Joe4]

It didn't work.

It sounds like it is working, but just not giving the results you expect because their is either a logic or formula issue. If it is going “upwards”, that means that your LastRow calculation is probably returning “1”, which means you probably did not set it up correctly.

Is there any data in column A on your “Data” sheet? If so, exactly where is the last row of data in this column?

 

[SAMCRO2014]

There is data in column A. There are 4913 lines of data.

This is how I set it up:
Dim Data As Worksheet
Set Data = ThisWorkbook.Sheets("Data")

Dim LastRowData As Long
LastRowData = Data.Cells(Rows.Count, "1").End(xlUp).Row

 

[Joe4]

Do not place the 1 in quotes:

Data.Cells(Rows.Count, "1")

It should be a numeric reference like 1, or a letter, like "A"

Add a Message Box to see what you calculation is actually returning, i.e.

Dim Data As Worksheet
Set Data = ThisWorkbook.Sheets("Data")

Dim LastRowData As Long
LastRowData = Data.Cells(Rows.Count, 1).End(xlUp).Row
MsgBox "Last row is: " & LastRowData

' Open Salary GL Workbook to see if the GL numbers are categorized as regular, overtime, premiums, other.

Workbooks.Open Filename:= _
"J:\FinanceAdmin\Finance\Resource Management\Regional\2018-19\Salary Reconciliation\Planned vs Actuals\Look up Tables\Salary GL.xlsx"
Windows("2019.P#.Planned vs Actuals - 1237.v1.RXM.xlsm").Activate
Range("E2:E" & LastRowData).FormulaR1C1 = _
    "=VLOOKUP(RC[-2],'[Salary GL.xlsx]Page1_1'!R2C1:R49C3,3,0)"

Is that Message Box returning 1 or 4913?

 

[SAMCRO2014]

It is bringing back the value of 1

 

[Joe4]

So there is a problem with your LastRowData calculation.

Which file is this "Data" sheet located in?
Is there any data in column A on that "Data" sheet?

 

[SAMCRO2014]

I figured it out. Strangely, I defined all my variables at the beginning of the macro including LastRowData. I moved the two lines of variables for LastRowData to just before the coding and it worked.

Thanks for help!

 

[Joe4]

Can you post your amended code?
I am interested to see what you did, and may be able to explain why it works differently (if I can see exactly what you did).