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

 

[Jeffrey Mahoney]

Please try this. When as sheet has a space in the name, apostrophes need to encompass the sheet name. I don't know about using autofill instead of copy, but this should work

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

 

[Joe4]

This can all be simplified to this:

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

 

[jski21]

Thanks for your time gentlemen...

So for Jeff's solution, I receive a run time 1004 error; "Select method of Range class failed" on line 2 and a 1004; "Method 'Range' of object'_Worksheet failed" on line 3.

With Joe4s', I receive a 1004 error; "Application-defined or object-defined on line 2.

 

[Jeffrey Mahoney]

In all my code selecting a cell like Range("D2").Select has never failed for me. As for the 3rd line, I changed it so that it would work from me.

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

 

[Joe4]

In all my code selecting a cell like Range("D2").Select has never failed for me. As for the 3rd line, I changed it so that it would work from me.

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

It isn't a "problem", it is just totally unnecessary, and besides making the code longer, every unnecessary select actually slows the code down.
It may not be noticeable if just a single select, but if this were inside a loop, you could be doing hundreds, thousands, etc unnecessary select, and that will start to be noticeable.
Most of the time in VBA, it is not necessary to select ranges to work on them. They are usually the result of using the Macro Recorder to record code, which is very literal and records everything, every cell you select, every scroll you make. And those type of things are not necessary to make your code work.

So for Jeff's solution, I receive a run time 1004 error; "Select method of Range class failed" on line 2 and a 1004; "Method 'Range' of object'_Worksheet failed" on line 3.

With Joe4s', I receive a 1004 error; "Application-defined or object-defined on line 2.

That would seem to suggest that there is a problem with the VLOOKUP formula.
I noticed your original did NOT have single-quotes around the Sheet name of 'Tab 1', and Jeff's did. I worked off of his copy.
Did the original formula your code placed in cell D2 work for you? Did it do what it was supposed to, at least in that cell?

 

[jski21]

Thanks everyone. Joe4--Yes, the VLOOKUP places the correct result in Cell D2. It just doesn't autofill the formula down. Here's the code that seems to work using Jeff's solution for Line 4:

'Insert VLOOKUP formula in Hourly column
Sheets("Tab 3").Activate
Sheets("Tab 3").Range("D2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP([@User],'Tab 1'!C[4]:C[6],3,FALSE)"
Range("D2").Copy Range("D2:D" & Range("C" & Rows.Count).End(xlUp).Row)

The only thing with Line 4 is it just glosses over the code without doing anything. I also used this in Line 4 with the same result:

Range("D2").AutoFill Range("D2:D" & Range("C" & Rows.Count).End(xlUp).Row)
Selection.AutoFill Destination:=Range("Tab 3[Hourly]")



It gets a bit weirder...I have another column that I use this code for that fulfills the VLOOKUP and autofills down with no issue:

'Insert VLOOKUP formula in PayNo column
Sheets("Tab 3").Select
Sheets("Tab 3").Range("H2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP([@[Start Date]],'Tab 1'!C[-7]:C[-6],2,FALSE)"


BTW, my data is in a table so shouldn't it automatically fill down if your referencing direct cells in a table?

 

[Joe4]

You are trying to look at column C on your "Tab 3" sheet to find how many rows to copy it down.
Is that correct?
Can you show us an image of "Tab 3"?

 

[jski21]

Yes, using Column C was the intention. Here's an image:

Lead Chargeback 2024 Pay01-Pay12.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Project	Grant	User	Hourly	Group	Billable	Start Date	PayNo	Start Time	End Date	End Time	Duration (h)	Duration (decimal)
2	Larry	ABC	User 1	28.15	Group 1	Yes	05/24/2024	Pay12	11:31 PM	05/25/2024	7:31 AM	8:00	8.00
3	Curly	DEF	User 2		Group 2	Yes	05/24/2024	Pay12	4:00 PM	05/24/2024	4:30 PM	0:30	0.50
4	Moe	GHI	User 3		Group 3	Yes	05/24/2024	Pay12	3:00 PM	05/24/2024	4:00 PM	1:00	1.00
5	Shemp	JKL	User 4		Group 4	Yes	05/24/2024	Pay12	2:00 PM	05/24/2024	4:00 PM	2:00	2.00
6	Joe	MNO	User 5		Group 5	Yes	05/24/2024	Pay12	12:00 PM	05/24/2024	4:00 PM	4:00	4.00
7	Larry	PQR	User 6		Group 6	Yes	05/24/2024	Pay12	12:00 PM	05/24/2024	4:00 PM	4:00	4.00
Clockify

 

[MARK858]

So the sheet name for column C where the last row is defined and the formula is being placed is actually Clockify?