CODEWITHSUNDEEP
vbaexcel
Josephine Bautista
Josephine Bautista

Reputation: 605

VBA drag formula from left to right

I am trying to drag a formula from a cell to another cell to the right (i.e. cellA1 [formula: A2 + A3] -> cellB1 [formula: B2+B3]). Here is what I have:

Dim lastColumn As Long
lastColumn = Workbook2.Worksheets(7).Cells(1, Columns.Count).End(xlToLeft).Column

cellSource = Workbook2.Worksheets(7).Cells(3, lastColumn)
cellTarget = Workbook2.Worksheets(7).Cells(3, lastColumn + 1)

cellSource.AutoFill Destination:=cellTarget, Type:=xlFillDefault

Error is "Object Required"

Upvotes: 2

Views: 12072

Answers (2)

Santosh
Santosh

Reputation: 12353

Try below code :

Dim lastColumn As Long, cellSource As Range, cellTarget As Range
lastColumn = Workbook2.Worksheets(7).Cells(1, Columns.Count).End(xlToLeft).Column

Set cellSource = Workbook2.Worksheets(7).Range(Cells(3, lastColumn).Address)
Set cellTarget = Workbook2.Worksheets(7).Range(Cells(3, lastColumn), Cells(3, lastColumn + 1))

cellSource.AutoFill Destination:=cellTarget, Type:=xlFillDefault

Upvotes: 4

Our Man in Bananas
Our Man in Bananas

Reputation: 5981

ranges are objects, so you should use the set keyword when assigning values to those variables:

try this:

Dim lastColumn As Long
lastColumn = Workbook2.Worksheets(7).Cells(1, Columns.Count).End(xlToLeft).Column

set cellSource = Workbook2.Worksheets(7).Cells(3, lastColumn)
set cellTarget = Workbook2.Worksheets(7).Cells(3, lastColumn + 1)

cellSource.AutoFill Destination:=cellTarget, Type:=xlFillDefault

Upvotes: 1

Related Questions