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

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
--


Gary


"Mike" wrote in message
...
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

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

On Sun, 18 Feb 2007 09:55:13 -0800, Mike wrote:

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

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

--


Gary


"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...

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
--


Gary


"Mike" wrote in message
...
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

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

"Gord Dibben" wrote:

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

On Sun, 18 Feb 2007 09:55:13 -0800, Mike wrote:

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

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


"Gary Keramidas" <GKeramidasATmsn.com wrote in message
:

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

--


Gary


"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...

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
--


Gary


"Mike" wrote in message
...
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