Excel Autofill Formula Down to Adjacent Column

MtyAVATAR

New Member
Joined
Jul 16, 2015
Messages
7
To start off, I have found this answer on several different forums, all the answers are the same, but I cannot get it to work.

I have a set of data from column A through column T with the number of rows varying based on report ran. I need to paste a formula in U2 and have it paste all the way down to the last row of data in column T. Column T never has blanks in it so the formula should work. What am I doing wrong?

Sheets("Data Dump").Select
Range("U2").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(VLOOKUP(RC[-18],'2016 Claimed Cost Centers'!C[-20],1,FALSE)),""N"",""Y"")"
Range("U2").Select
Selection.AutoFill Destination:=Range("U2:U" & Cells(Rows.Count, "T").End(x1Up).Row) '''Here is where it errors out
Columns("U:U").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,364
Office Version
365
Platform
Windows
It should be xlUp not x1Up.
Code:
With Sheets("Data Dump")
    .Range("U2").FormulaR1C1 = _
"=IF(ISNA(VLOOKUP(RC[-18],'2016 Claimed Cost Centers'!C[-20],1,FALSE)),""N"",""Y"")"
    .Range("U2").AutoFill Destination:=Range("U2:U" & .Cells(Rows.Count, "T").End(xlUp).Row) 
    .Columns("U:U").Copy
    .Columns("U:U").PasteSpecial Paste:=xlPasteValues
End With
 

MtyAVATAR

New Member
Joined
Jul 16, 2015
Messages
7
Okay so I changed the "1" to a "l" and reran the macro and it inputs the formula at U1 but nowhere else.

User Name D/CN
ZZ90PHC
TZKT02C
U1106267D
ZZ90PHC
TZKT02C
TZKT02C
U1106523C
U1106523C
U1106523D
U1106523C

<tbody>
</tbody><colgroup><col><col><col></colgroup>

here is my full code:

Sub FAR_31_2016()

Sheets("Data Dump").Select

ActiveCell.FormulaR1C1 = _
"=IF(ISNA(VLOOKUP(RC[-18],'2016 Claimed Cost Centers'!C[-20],1,FALSE)),""N"",""Y"")"

Range("U2").Select

Selection.AutoFill Destination:=Range("U2:U" & Cells(Rows.Count, "T").End(xlUp).Row)

Columns("U:U").Select

Selection.Copy

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

End Sub
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,364
Office Version
365
Platform
Windows
Did you try the code I posted?
 

MtyAVATAR

New Member
Joined
Jul 16, 2015
Messages
7
My apologies. I pasted the code and it worked perfectly and I see what I did wrong. Thank you. Much appreciated!
 

Forum statistics

Threads
1,082,557
Messages
5,366,304
Members
400,882
Latest member
zaldy_a

Some videos you may like

This Week's Hot Topics

Top