VBA: Autofill to last row not working

ObiWanBaloney

New Member
Joined
May 10, 2012
Messages
23
Code:
Sub ApplyVlookupFormulatoColumn()
'
' ApplyVlookupFormulatoColumn Macro
'


    ActiveCell = _
        "=IFERROR(VLOOKUP(E2,'ORDER TEMPLATE'!A:B,2,FALSE),0)"
    
    Dim lastrow As Long
     
    lastrow = Worksheets("ALL ITEMS").Range("A2").End(xlDown).Row
    With Worksheets("ALL ITEMS").Range(ActiveCell.Address)
[B]        .AutoFill Destination:=Range(ActiveCell.Address & lastrow&)[/B]
    End With
  
End Sub



Bolded part is not working. This is intended to work off whatever cell is selected, hence the activecell.address wherever it appears. Anyone know what to fix in this situation?
 

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Try

Code:
.AutoFill Destination:=.Range(.Cells(ActiveCell.Row, ActiveCell.Column), .Cells(lastrow, ActiveCell.Column))
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,334
Office Version
  1. 2010
Platform
  1. Windows
Try

Rich (BB code):
.AutoFill Destination:=.Range(.Cells(ActiveCell.Row, ActiveCell.Column), .Cells(lastrow, ActiveCell.Column))
(Untested) If I am not mistaken, I think the red highlighted text could be replaced with just this...

ActiveCell.Address
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
You know that I like the verbose solutions Rick :)
 

ObiWanBaloney

New Member
Joined
May 10, 2012
Messages
23

ADVERTISEMENT

Thanks for the response guys! I'll definitely give it a try when I'm back to work =)
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Try

Code:
Sub ApplyVlookupFormulatoColumn()
'
' ApplyVlookupFormulatoColumn Macro
'


    ActiveCell.Formula = _
        "=IFERROR(VLOOKUP(E2,'ORDER TEMPLATE'!A:B,2,FALSE),0)"
    
    Dim lastrow As Long
     
    lastrow = Worksheets("ALL ITEMS").Range("A2").End(xlDown).Row
    With Worksheets("ALL ITEMS")
        ActiveCell.AutoFill Destination:=.Range(.Cells(ActiveCell.Row, ActiveCell.Column), .Cells(lastrow, ActiveCell.Column))
    End With
  
End Sub
 

ObiWanBaloney

New Member
Joined
May 10, 2012
Messages
23
Try

Code:
Sub ApplyVlookupFormulatoColumn()
'
' ApplyVlookupFormulatoColumn Macro
'


    ActiveCell.Formula = _
        "=IFERROR(VLOOKUP(E2,'ORDER TEMPLATE'!A:B,2,FALSE),0)"
    
    Dim lastrow As Long
     
    lastrow = Worksheets("ALL ITEMS").Range("A2").End(xlDown).Row
    With Worksheets("ALL ITEMS")
        ActiveCell.AutoFill Destination:=.Range(.Cells(ActiveCell.Row, ActiveCell.Column), .Cells(lastrow, ActiveCell.Column))
    End With
  
End Sub

Works! Thanks a bunch =D
 

Watch MrExcel Video

Forum statistics

Threads
1,109,466
Messages
5,528,972
Members
409,848
Latest member
Blomsten
Top