Easy IF/Else Problem

The following code cycles cell "r" through several If/Else statements.
The section between the "****" is where I'm having a problem. It works fine as long as r doesn't meet any requirements. It goes to the next like it should. However, when r does qualify one of the statements between the ****'s, it does that action but doesn't go to next. It starts over and resets r. Therefore, the code gets stuck in a loop. Interestingly though, it eventually gets out successfully after about a 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 to 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 ask for help. TIA
Paul


Dim lastrow As Long, r As Long

lastrow = Workbooks("TAStrategyUS.xls").Sheets("Open 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 And 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 And Workbooks("xxx").Sheets("xxx").Cells(r, 19) = "Yes" Then
Workbooks("xxx").Sheets("xxx").Cells(r, 18) = "Sell " & 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 Sub

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" <Paul987.26m8ao_1145625007.1232@excelforum-nospam.com> wrote in
message news:Paul987.26m8ao_1145625007.1232@excelforum-nospam.com...
>
> The following code cycles cell "r" through several If/Else statements.
>
> The section between the "****" is where I'm having a problem. It works
> fine as long as r doesn't meet any requirements. It goes to the next
> like it should. However, when r does qualify one of the statements
> between the ****'s, it does that action but doesn't go to next. It
> starts over and resets r. Therefore, the code gets stuck in a loop.
> Interestingly though, it eventually gets out successfully after about a
> 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 to
> 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 ask
> for help. TIA
> Paul
>
>
> Dim lastrow As Long, r As Long
>
> lastrow = Workbooks("TAStrategyUS.xls").Sheets("Open
> 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 And
> 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 And
> Workbooks("xxx").Sheets("xxx").Cells(r, 19) = "Yes" Then
> Workbooks("xxx").Sheets("xxx").Cells(r, 18) = "Sell
> " & 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 Sub
>
>
> --
> Paul987
> ------------------------------------------------------------------------
> Paul987's Profile:
> http://www.excelforum.com/member.php...o&userid=24850
> View this thread: http://www.excelforum.com/showthread...hreadid=534939
>

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

Paul,

Something like:

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

hth,

Doug


"Paul987" <Paul987.26mc8c_1145630112.1545@excelforum-nospam.com> wrote in
message news:Paul987.26mc8c_1145630112.1545@excelforum-nospam.com...
>
> 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
>
>
> --
> Paul987
> ------------------------------------------------------------------------
> Paul987's Profile:
> http://www.excelforum.com/member.php...o&userid=24850
> View this thread: http://www.excelforum.com/showthread...hreadid=534939
>