Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Do Loop or If Then
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Do Loop or If Then
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Do Loop or If Then
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 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
returning back to loop check condition without completing the loop | Excel Programming | |||
Loop to Filter, Name Sheets. If Blank, Exit Loop | Excel Programming | |||
Naming Worksheets - Loop within a loop issue | Excel Programming | |||
Advancing outer Loop Based on criteria of inner loop | Excel Programming | |||
Problem adding charts using Do-Loop Until loop | Excel Programming |