Where lastRow maybe +/- 25000
For i = 5 To lastRow
xlApp.Cells(i, "G").value = _
(xlApp.Cells(i, "E").value - xlApp.Cells(i, "D").value) _
* xlApp.Cells(i, "F").value
Next

how about a formula?

With ws
.Range("G5:G" & lastrow).Formula = "=(" & .Range("E5").Address(0, 0) & _
"-" & .Range("D5").Address(0, 0) & ")*" & .Range("F5").Address(0, 0)
End With
End Sub

--


Gary


"Mike" wrote in message
...
Where lastRow maybe +/- 25000
For i = 5 To lastRow
xlApp.Cells(i, "G").value = _
(xlApp.Cells(i, "E").value - xlApp.Cells(i, "D").value) _
* xlApp.Cells(i, "F").value
Next

here's the code i forgot to post

Sub test()
Dim lastrow As Long
Dim ws As Worksheet
Set ws = Worksheets("Sheet1")
lastrow = ws.Cells(Rows.Count, "D").End(xlUp).Row
With ws
.Range("G5:G" & lastrow).Formula = "=(" & .Range("E5").Address(0, 0) & _
"-" & .Range("D5").Address(0, 0) & ")*" & .Range("F5").Address(0, 0)
End With
End Sub


--


Gary


"Mike" wrote in message
...
Where lastRow maybe +/- 25000
For i = 5 To lastRow
xlApp.Cells(i, "G").value = _
(xlApp.Cells(i, "E").value - xlApp.Cells(i, "D").value) _
* xlApp.Cells(i, "F").value
Next

Thanks Gary
While I was waiting i was trying this which was faster then the loop i posted
But for speed how does this compare to what you suggest
Dim rng As Excel.Range = xlApp.Range("G5")
xlApp.Range("G5").Formula = "=(E5-D5)*F5"
rng.AutoFill(xlApp.Range("G5:G" & lastRow),
Excel.XlAutoFillType.xlFillCopy)

"Gary Keramidas" wrote:

here's the code i forgot to post

Sub test()
Dim lastrow As Long
Dim ws As Worksheet
Set ws = Worksheets("Sheet1")
lastrow = ws.Cells(Rows.Count, "D").End(xlUp).Row
With ws
.Range("G5:G" & lastrow).Formula = "=(" & .Range("E5").Address(0, 0) & _
"-" & .Range("D5").Address(0, 0) & ")*" & .Range("F5").Address(0, 0)
End With
End Sub


--


Gary


"Mike" wrote in message
...
Where lastRow maybe +/- 25000
For i = 5 To lastRow
xlApp.Cells(i, "G").value = _
(xlApp.Cells(i, "E").value - xlApp.Cells(i, "D").value) _
* xlApp.Cells(i, "F").value
Next

if i had to guess, i'd say mine was faster. fraction of a second? but i'm not
sure. i've used both.

--


Gary


"Mike" wrote in message
...
Thanks Gary
While I was waiting i was trying this which was faster then the loop i posted
But for speed how does this compare to what you suggest
Dim rng As Excel.Range = xlApp.Range("G5")
xlApp.Range("G5").Formula = "=(E5-D5)*F5"
rng.AutoFill(xlApp.Range("G5:G" & lastRow),
Excel.XlAutoFillType.xlFillCopy)

"Gary Keramidas" wrote:

here's the code i forgot to post

Sub test()
Dim lastrow As Long
Dim ws As Worksheet
Set ws = Worksheets("Sheet1")
lastrow = ws.Cells(Rows.Count, "D").End(xlUp).Row
With ws
.Range("G5:G" & lastrow).Formula = "=(" & .Range("E5").Address(0, 0)
& _
"-" & .Range("D5").Address(0, 0) & ")*" & .Range("F5").Address(0,
0)
End With
End Sub


--


Gary


"Mike" wrote in message
...
Where lastRow maybe +/- 25000
For i = 5 To lastRow
xlApp.Cells(i, "G").value = _
(xlApp.Cells(i, "E").value - xlApp.Cells(i, "D").value) _
* xlApp.Cells(i, "F").value
Next

Thanks Gary

"Gary Keramidas" wrote:

if i had to guess, i'd say mine was faster. fraction of a second? but i'm not
sure. i've used both.

--


Gary


"Mike" wrote in message
...
Thanks Gary
While I was waiting i was trying this which was faster then the loop i posted
But for speed how does this compare to what you suggest
Dim rng As Excel.Range = xlApp.Range("G5")
xlApp.Range("G5").Formula = "=(E5-D5)*F5"
rng.AutoFill(xlApp.Range("G5:G" & lastRow),
Excel.XlAutoFillType.xlFillCopy)

"Gary Keramidas" wrote:

here's the code i forgot to post

Sub test()
Dim lastrow As Long
Dim ws As Worksheet
Set ws = Worksheets("Sheet1")
lastrow = ws.Cells(Rows.Count, "D").End(xlUp).Row
With ws
.Range("G5:G" & lastrow).Formula = "=(" & .Range("E5").Address(0, 0)
& _
"-" & .Range("D5").Address(0, 0) & ")*" & .Range("F5").Address(0,
0)
End With
End Sub


--


Gary


"Mike" wrote in message
...
Where lastRow maybe +/- 25000
For i = 5 To lastRow
xlApp.Cells(i, "G").value = _
(xlApp.Cells(i, "E").value - xlApp.Cells(i, "D").value) _
* xlApp.Cells(i, "F").value
Next

some mvp may be able to give you a better answer.

--


Gary


"Mike" wrote in message
...
Thanks Gary

"Gary Keramidas" wrote:

if i had to guess, i'd say mine was faster. fraction of a second? but i'm not
sure. i've used both.

--


Gary


"Mike" wrote in message
...
Thanks Gary
While I was waiting i was trying this which was faster then the loop i
posted
But for speed how does this compare to what you suggest
Dim rng As Excel.Range = xlApp.Range("G5")
xlApp.Range("G5").Formula = "=(E5-D5)*F5"
rng.AutoFill(xlApp.Range("G5:G" & lastRow),
Excel.XlAutoFillType.xlFillCopy)

"Gary Keramidas" wrote:

here's the code i forgot to post

Sub test()
Dim lastrow As Long
Dim ws As Worksheet
Set ws = Worksheets("Sheet1")
lastrow = ws.Cells(Rows.Count, "D").End(xlUp).Row
With ws
.Range("G5:G" & lastrow).Formula = "=(" & .Range("E5").Address(0,
0)
& _
"-" & .Range("D5").Address(0, 0) & ")*" &
.Range("F5").Address(0,
0)
End With
End Sub


--


Gary


"Mike" wrote in message
...
Where lastRow maybe +/- 25000
For i = 5 To lastRow
xlApp.Cells(i, "G").value = _
(xlApp.Cells(i, "E").value - xlApp.Cells(i, "D").value) _
* xlApp.Cells(i, "F").value
Next

On Apr 10, 10:29*am, Mike wrote:
Where lastRow maybe +/- 25000
* * * * For i = 5 To lastRow
* * * * * * xlApp.Cells(i, "G").value = _
* * * * * * (xlApp.Cells(i, "E").value - xlApp.Cells(i, "D").value) _
* * * * * * * xlApp.Cells(i, "F").value
* * * * Next


It's quicker to read the data into an array, do all the maths in VBA,
and write it all back in one operation.

For instance

Sub writeloop2()
Dim lastrow As Long, i As Long, xlApp As Variant

xlApp = Range("d1:g25000").Value

lastrow = UBound(xlApp) - LBound(xlApp) + 1
For i = 5 To lastrow
xlApp(i, 4) = _
(xlApp(i, 2) - xlApp(i, 1)) _
* xlApp(i, 3)
Next i
Range("d1:g25000").Value = xlApp
End Sub


In XL2007 this is virtually instantaneous for 25000 lines, compared
with about 15 seconds seconds reading and writing line by line.