Do Loop or If Then

[RogerD]

Hi,
I've got the following code working in a longer code sequence but it is slow
and I was wondering whether 'If Then' statements would be better/faster.
The code is looking down a column of data and using the row number where a
condition is True to carry out further calculations.

' calculate time to 1bar pressure
Range("J3").Activate
Do
ActiveCell.Offset(rowOffset:=1).Activate
If ActiveCell.Row > 6002 Then Exit Do
Loop Until ActiveCell.Value >= 1
e = ActiveCell.Row
' Calculate time between engine movement and 1 bar
f = (e - s) * 0.005
If ActiveCell.Row > 6002 Then f = ""

' calculate time to 2bar pressure
Do
ActiveCell.Offset(rowOffset:=1).Activate
If ActiveCell.Row > 6002 Then Exit Do
Loop Until ActiveCell.Value >= 2
e = ActiveCell.Row
' Calculate time between engine movement and 2 bar
g = (e - s) * 0.005
If ActiveCell.Row > 6002 Then g = ""

This is repeated 10 times in total.

 

[JLGWhiz]

There probably is a more efficient way to write the procedure, but you would
have to give a little more explanation of what you are doing for anyone to
give meaningful assistance. For instance I noticed that the f changes to g
in the second procedure, which would indicate that the next iteration might
change to h, etc. I would assume that these variables would then be used
elsewhere in the code, so without understanding what the entire procedure is
attempting to accomplish, any input would be guesswork.

 

[Per Jessen]

Hi

There's no need to activate cells, just work with references. It's
much faster.

Look at this:

Dim TargetRange As Range
' calculate time to 1bar pressure
Set TargetRange = Range("J3")
Do
Set TargetRange = TargetRange.Offset(1, 0)
If TargetRange.Row > 6002 Then Exit Do
Loop Until TargetRange.Value >= 1
e = TargetRange.Row
' Calculate time between engine movement and 1 bar
f = (e - s) * 0.005
If TargetRange.Row > 6002 Then f = ""


' calculate time to 2bar pressure
Do
Set TargetRange = TargetRange.Offset(1, 0)
If TargetRange.Row > 6002 Then Exit Do
Loop Until TargetRange.Value >= 2
e = TargetRange.Row
' Calculate time between engine movement and 2 bar
g = (e - s) * 0.005
If TargetRange.Row > 6002 Then g = ""

Regards,
Per