Summing

[Guest]

I have a database query that retuns data by date range. After the data is
returned
i enter in this formula in Columns G and H down to the last row of Data. Is
there a code to run a macro to do this

Thanks Mike

Column G
=SUM(E6-D6) ' Subtracts the New Price from Old Price
Column H
=SUM(F6*G6) ' New Price * QTY

 

[Gary Keramidas]

here's one way

Sub test()
Dim ws As Worksheet
Dim lastrow As Long
Set ws = Worksheets("sheet1")
lastrow = ws.Cells(Rows.Count, "E").End(xlUp).Row
ws.Range("G6").Formula = "=E6-D6"
ws.Range("H6").Formula = "=F6*G6"
ws.Range("G6:H6").AutoFill ws.Range("G6:H" & lastrow)

End Sub

 

[Gord Dibben]

Mike

Pick a column that has data all the way down to ascetain the last row(Lrow)

I used E

Sub Auto_Fill()
Dim Lrow As Long
With ActiveSheet
Lrow = Range("E" & Rows.Count).End(xlUp).Row
Range("G1:H" & Lrow).FillDown
End With
End Sub


Gord Dibben MS Excel MVP

 

[Gary Keramidas]

and if it's a large query, you can turn off calc and screenupdating while the
code runs.

Sub test()
Dim ws As Worksheet
Dim lastrow As Long
Set ws = Worksheets("sheet1")
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
lastrow = ws.Cells(Rows.Count, "E").End(xlUp).Row

ws.Range("G6").Formula = "=E6-D6"
ws.Range("H6").Formula = "=F6*G6"
ws.Range("G6:H6").AutoFill ws.Range("G6:H" & lastrow)

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

 

[Guest]

TY Gord And TY Gary i wasent able to get Garys to work probably my dumb ...

 

[JMay]

Sub Foo()
lr = Cells(Rows.Count, "E").End(xlUp).Row ' obtain last active row
For i = lr To 2 Step -1
Cells(i, 7).FormulaR1C1 = "=(RC[-2]-RC[-3])" '7 used for Column G
Next i
For i = lr To 2 Step -1
Cells(i, 8).FormulaR1C1 = "=(RC[-2]*RC[-1])" '8 used for Column H
Next i
End Sub

Hope this helps