Autofill Not Autofilling

[jski21]

Good day all,

Trying to run a macro from a command button on one tab (Tab 1) to transform data on another tab (Tab 3). Conducting a VLOOKUP from Tab 3 into Tab 1 and then autofilling the data down with this code:

Sheets("Tab 3").Range("D2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP([@User],Tab 1!C[4]:C[6],3,FALSE)"
Range("D2").AutoFill Range("D2:D" & Range("C" & Rows.Count).End(xlUp).Row)

Seems right but after executing the 3rd line to autofill the VLOOKUP, nothing happens. Column C is completely full. What gives?


Thanks!

jski

 

[jski21]

Yes, but using Tab 3 as generic illustration. Should have changed that before I sent image.

 

[MARK858]

Should have changed that before I sent image

No you shouldn't.

What happens with the below

    With Sheets("Clockify")
        .Range("D2:D" & .Range("C" & Rows.Count).End(xlUp).Row).FormulaR1C1 = "=VLOOKUP([@User],'Tab 1'!C[4]:C[6],3,FALSE)"
    End With

Assuming that Tab 1 is actually called Tab 1 although I doubt it

 

[jski21]

Actually, it is. Thanks Mark. That did it. With Sheets / End With?

What are the major differences one should keep in mind in running code from your PERSONAL file as opposed to actually containing it in within the workbook the macro will be used?

 

[MARK858]

With Sheets / End With?

Just saves writing something multiple times (see the below as to what it looks like without it), it also means Excel only evaluates it once (only matters when using a lot of references to whatever you have on the With line).

Sheets("Clockify").Range("D2:D" & Sheets("Clockify").Range("C" & Rows.Count).End(xlUp).Row).FormulaR1C1 = "=VLOOKUP([@User],'Tab 1'!C[4]:C[6],3,FALSE)"

What are the major differences one should keep in mind in running code from your PERSONAL file as opposed to actually containing it in within the workbook the macro will be used?

Biggest one is remembering the workbook the code is working on is the Active workbook and not ThisWorkbook

 

[jski21]

Instructive and very helpful. Thanks Mark and to all who contributed to this thread.

 

[MARK858]

You're welcome, happy we could help