Converting For Each range to Lbound and Ubound

[dbobo88]

Hello, I have the below code (OptimumSpeed) which works well but is a bit slow when applied to 8760 rows. I was trying to change it using Lbound to Ubound but can't seem to make it work (see second code Optimum) when trying to associate it with a Do until....Loop. Appreciate if anybody could help me solve this.
Thanks in advance

Sub OptimumSpeed()

Application.ScreenUpdating = False

Dim i As Long

Dim rng, rng1, rng2, rng3 As Range

Set rng = Range("A1:A8760")

Set rng1 = Range("B1") '' this cell has a formula linked to C1 value

Set rng2 = Range("C1")  '' this cell has a formula linked to D1 value

Set rng3 = Range("D1") 

i = 0

For Each cell In rng

Do Until rng1.Offset(i, 0) > rng2.Offset(i, 0)

rng3.Offset(i, 0) = rng3.Offset(i, 0) - 1

Loop

i = i + 1

Next cell

Application.ScreenUpdating = True

End Sub

Sub optimum()

Dim arr(),

Dim j As Long



arr = Range("A1:A8760")



For i = LBound(arr, 1) To UBound(arr, 1)

Do Until Range("B1").Offset(j , 0) > Range("C1").Offset(j , 0)

arr(i, 1) = Range("Temp_CtSPeed1").Offset(j , 0) - 1

Loop

j = j + 1

Next i



Range("A1:A8760")= arr



End Sub

 

[CSmith]

Try using row value and do the 3 cells in succession on the same row loop.

 

[kevin9999]

Without seeing your formulas in cells B1 & C1 it's pretty difficult to offer suggestions. A sample of your sheet using the XL2BB - Excel Range to BBCode would also greatly assist (alternatively you can share your file via Dropbox, Google Drive or similar method).