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.

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.

"RogerD" wrote:

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

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


On 14 Okt., 00:40, RogerD <Rog...@discussions.microsoft.com> wrote:
> 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.