Easy IF/Else Problem

[Paul987]

The following code cycles cell "r" through several If/Else statements.

The section between the "****" is where I'm having a problem. It work
fine as long as r doesn't meet any requirements. It goes to the nex
like it should. However, when r does qualify one of the statement
between the ****'s, it does that action but doesn't go to next. I
starts over and resets r. Therefore, the code gets stuck in a loop
Interestingly though, it eventually gets out successfully after about
minute, but I'm not sure how.

Any ideas?. I'm sure Im missing something simple.
This runs constantly in the background on a large sheet, and I'm new t
VBA, so If you know of any ways to make this run faster, I'm all ears.
I've spent one day trying to figure this out, figures it was time to as
for help. TIA
Paul


Dim lastrow As Long, r As Long

lastrow = Workbooks("TAStrategyUS.xls").Sheets("Ope
Positions").Cells(Rows.Count, "A").End(xlUp).Row

If Workbooks("TAStrategyUS.xls").Sheets("Open Positions").Cells(1, 17
= "ON" Then

For r = 5 To lastrow

If Workbooks("xxx").Sheets("xxx").Cells(r, 9) < -0.07 An
Workbooks("xxx").Sheets("xxx").Cells(r, 5) = "" Then
On Error Resume Next
MsgBox "xxx" & Workbooks("xxx").Sheets("xxx").Cells(r, 3)
" " & Workbooks("xxx").Sheets("xxx").Cells(r, 1) & " at "
Workbooks("xxx").Sheets("xxx").Cells(r, 6) & "xxx"
Workbooks("xxx").Sheets("xxx").Cells(r, 5) = "xxx"
End If

If Workbooks("xxx").Sheets("xxx").Cells(r, 16)
Workbooks("xxx").Sheets("xxx").Cells(r, 9) Then
Workbooks("xxx").Sheets("xxx").Cells(r, 16)
Workbooks("xxx").Sheets("xxx").Cells(r, 9)
End If
*******************************************
If Workbooks("xxx").Sheets("xxx").Cells(r, 9) >= 0.2 Then
If Workbooks("xxx").Sheets("xxx").Cells(r, 17) = "" Then
Workbooks("xxx").Sheets("xxx").Cells(r, 17)
Workbooks("xxx").Sheets("xxx").Cells(1, 14)
Else
If Workbooks("xxx").Sheets("xxx").Cells(r, 17) <> "
And Workbooks("xxx").Sheets("xxx").Cells(r, 17)
Workbooks("xxx").Sheets("xxx").Cells(r, 7) > 21 An
Workbooks("xxx").Sheets("xxx").Cells(r, 19) = "Yes" Then
Workbooks("xxx").Sheets("xxx").Cells(r, 18) = "Sel
" & Workbooks("xxx").Sheets("xxx").Cells(r, 3) & " @ "
Workbooks("xxx").Sheets("xxx").Cells(r, 20) + 0.01
Else
If Workbooks("xxx").Sheets("xxx").Cells(r, 17) <
"" And Workbooks("xxx").Sheets("xxx").Cells(r, 17)
Workbooks("xxx").Sheets("xxx").Cells(r, 7) < 22 Then
Workbooks("xxx").Sheets("xxx").Cells(r, 18)
"xxx" & Workbooks("xxx").Sheets("xxx").Cells(r, 7) + 56
End If
End If
End If
End If

Next r
*****************************************************
End If
End Su

 

[Doug Glancy]

Paul,

In an If - Then structured as yours, if A is true the If - Then is exited
and B is never tested for.

If A then
'do A stuff
ElseIf B then
'do B stuff
EndIf

In order to test for both A and B you need separate If-Thens for each, as
below:

If A then
'do A
EndIf

If B then
'do B
EndIf

To make it run faster you could turn off ScreenUpdating and Calculation at
the beginning of the procedure and turn it back on at the end:

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
'your code
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

You can also use With statements:

With Workbooks("xxx").Sheets("xxx")
.Range("A1") = "Test"
.Range("B1") = "Something Else"
End With

Last, you've got an "On Error Resume Next" that runs through the whole
procedure. You should probably delete that unless you know exactly what
errors you expect and why it's okay to skip them.

hth,

Doug

 

[Paul987]

Thanks for the help.
I understand how the If/Thens are working, when I said it doesn't go to
next,
I meant next r not the next If/then statement in the section where they
are nested.
I'm having a problem with a cell that quailifies in the nested section,
where it does the "then" part of the statement, but afterwards, it
doesn't move to the next r. It resets and begins again.

I have the "On Error Resume Next" line, because some of the cells may
have an "#N/A" value for a bit. They are real-time data feeds, and may
not always have a value. If I don't include this line, the macro
assumes anything is greater than #N/A. Do you know of a better way to
overlook #n/A values?
Thanks. Paul

 

[Doug Glancy]

Paul,

Something like:

If Range("A1").Text = "#N/A" Then

hth,

Doug