Optimize VBA

[Numus]

Hi all...new to VBA and forum.
I am working on a spreadsheet with a few VBA to help with calculations that cell formulas cannot complete.
Below is a portion of the VBA I have written (that is working) but as you can see it is calling specific cells and runs a loop which from what I have read is a poor way to code. Unfortunately, in this code, "X" is x = x +1 which is taking foooorrreeevvveeerrr to complete because it can actually end up in the 100,000.00 + range when complete.



Sub step5()
Dim x As Long
x = CLng(1)
If ActiveWorkbook.Worksheets("Pre Trial").Range("G22").Value <= 0.5 Then
Exit Sub
Else
Do Until ActiveWorkbook.Worksheets("Pre Trial").Range("R38").Value <= Range("R37").Value _
Or Range("R38").Value = 0.5 Or Range("G32").Value = Range("R32").Value
Range("G32").Value = x
x = x + 1
ActiveWorkbook.Worksheets("Pre Trial").Range("G35").Value <= Range("R34").Value
Loop
End If
ActiveWorkbook.Worksheets("Pre Trial").Range("G35").Value <= Range("R34").Value
End Sub


Any ideas or help would be greatly appreciated.

Thanks all!!

[Paul_Hossler]

Just guessing since there's nothing to test with

I 'think' there's some syntax errors, and some non-specific references, i.e. just Range("R37") without something that identifes the worksheet, defaults to the Activesheet and it might not be the one you think

My biggest gues is that sometimes you check all Million+ rows, i.e. just keep on going with no escape condidtion

Some ideas to consider in the Alt_Step5 version

If there's a lot of other activity such as fomula calculations, you might need a DoEvents inside the loop



If i Mod 100 = 0 then DoEvents




Option Explicit

Sub step5()
Dim x As Long
With ActiveWorkbook.Worksheets("Pre Trial")
If .Range("G22").Value <= 0.5 Then Exit Sub
'need dot on the Range R37 etc otherwise it uses what ever the Active sheet is
Do Until .Range("R38").Value <= .Range("R37").Value Or .Range("R38").Value = 0.5 Or .Range("G32").Value = .Range("R32").Value
.Range("G32").Value = x
x = x + 1
'syntax error on this. Did you mean Equals??
' .Range("G35").Value <= Range("R34").Value
.Range("G35").Value = Range("R34").Value
Loop
'syntax error on this. Did you mean Equals??
' .Range("G35").Value <= Range("R34").Value
.Range("G35").Value = Range("R34").Value
End With
End Sub


Sub Alt_step5()
Dim i As Long, iMax As Long
With ActiveWorkbook.Worksheets("Pre Trial")
If .Range("G22").Value <= 0.5 Then Exit Sub
' there can be M+ rows, so need to stop
' this is the last non-blank row in col G
iMax = .Cells(.Rows.Count, 7).End(xlUp).Row
For i = 38 To iMax
' .the cells are floating point and might not be exactly 0.5, i.e. 0.5000000000001 which are not equal tp 0.5000000000000
If .Range("R38").Value <= .Range("R37").Value Then Exit For
If .Range("R38").Value = 0.5 Then Exit For
If .Range("G32").Value = .Range("R32").Value Then Exit For
.Range("G32").Value = i
Exit For
Next i
.Range("G35").Value = Range("R34").Value
End With
End Sub