CODEWITHSUNDEEP
vbaexcelfunctionexcel-formula
CoderTanner
CoderTanner

Reputation: 17

Excel Vba: Need help to drag formula

I need help placing a formula in row 51 from column A to AE on sheet "COPY". The formula is "Trim(A1)" and needs to be dragged until "Trim(AE1)" while still being in row 51 (A51:AE51)

This is what I have so far, but its pulling up an error on "lascolumn = range..."

Sub INSERT_TRIM_COPY()
Sheets("COPY").Select
Dim Lastcolumn As Long
Lastcolumn = Range("A:AE" & Columns.Count).End(xlToRight).Column
Range("A51:AE51" & Lastcolumn).FORMULA = "=TRIM(A1)"
End Sub

Upvotes: 0

Views: 111

Answers (2)

Xabier
Xabier

Reputation: 7735

I believe the following will do what you expect it to, the code you used to get the Last Column wasn't right:

Sub foo()
    Dim ws As Worksheet: Set ws = Sheets("COPY")
    LastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
    'get the last Column on Row 1 with data
    ws.Range(ws.Cells(51, 1), ws.Cells(51, LastCol)).Formula = "=Trim(A1)"
    'add formula from column A to the last Column
End Sub

Upvotes: 1

Hasib_Ibradzic
Hasib_Ibradzic

Reputation: 666

You need to use: Range(Cells(51,1), Cells(51,Lastcolumn).Formula = "=Trim(A1) Because your lastcolumn is variable is numeric you need to use the cells function in the range. The first number is for row number and the second is for the column.

Upvotes: 1

Related Questions