last row auto sum ?

[Joshua88]

hello every one, I have a line of code that I'm working on, it auto sums the data from column "A" and puts the sum, one cell below the last, what I'm trying to do is have to sum appear in "W1" instead of the cell below the last, any thought?

[CODEWith Sheets("DataDump8")
Dim Lr As Long
Lr = Cells(Rows.Count, "A").End(xlUp).Row + 1
Cells(Lr, "A").Formula = "=SUM(A1:A" & Lr - 1 & ")*4"
End With

][/CODE]

 

[Fluff]

Try

Range("W1").Formula =

 

[Joshua88]

it worked Thanks again

 

[Fluff]

You're welcome

 

[Joshua88]

ok so how about this I ran into an issue with the code running on a home sheet and not on my data dump sheet, I made a change and it runs on the data sheet but instead of getting a sum I get #NAME ?
any idea ?

With Sheets("DataDump8")
'Dim Lr As Long
Lr = Cells(Rows.Count, "A").End(xlUp).Row + 1
Sheets("DataDump8").Range("W1").Formula = "=SUM(A:A" & Lr - 1 & ")*4"
End With

 

[Fluff]

You're missing the row reference from the 1st A

With Sheets("DataDump8")
   Dim Lr As Long
   Lr = Cells(Rows.Count, "A").End(xlUp).Row 
   .Range("W1").Formula = "=SUM(A[COLOR=#ff0000]1[/COLOR]:A" & Lr  & ")*4"
End With

 

[Joshua88]

ok I added the 1 it works on the active data dump8 sheet I get a sum of 100 as I should, but when I select my home sheet and make that active, and run the code I get a sum of 4 ? dosent seem to be totaling column A on datadump sheet?

 

[Fluff]

You'll need to put the sheet name in front of the range

 

[Joshua88]

thanks for the reply
I still get the same thing a sum of 4 when home is active and get a sum 100 when data dump is active, 100 is correct just not when home is active, is there a better way of achieving the same results?

With Sheets("DataDump8")
   Dim Lr As Long
   Lr = Cells(Rows.Count, "A").End(xlUp).Row
   Sheets("DataDump8").Range("W1").Formula = "=SUM(A1:A" & Lr & ")*4"

 

[Joshua88]

I found that the code below works

[CODESheets("DataDump8").Range("W1").FormulaR1C1 = "=SUMIF(C[-21],C[-21],C[-22])*4"][/CODE]

there's more then one way to skin a cat