Is there a faster way to loop thru cells

[Mike]

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

 

[Gary Keramidas]

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

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

 

[Mike]

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]

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

 

[Mike]

Thanks Gary

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

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

 

[dougaj4]

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.