VBA; Escaping a 'For Next' loop question

[ChipButtyMan]

Hi,
I have some code which uses a For Next loop which works very well
with a very large set of 'Regular Expression' statements.
I'm sure when the code is run, it would be a bit quicker if it
could go back to 'For' when the value of 'valid' is True rather than
continue to the end of the statements to find 'Next'

Here is a tiny section of the code;

Sub RationaliseOrders()
Dim RegEx As Object
Dim strTest As String
Dim valid As Boolean
Dim Matches As Object
Dim i As Integer

For i = 1 To 2000

Set RegEx = CreateObject("VBScript.RegExp")
RegEx.Pattern = "(SOME PATTERN 1)"

Range("B" & i).Activate
strTest = ActiveCell.Text
valid = RegEx.test(strTest)
If valid = True Then
Set Matches = RegEx.Execute(strTest)
Range("C" & i).Value = StrConv(CStr(Matches(0)),
vbProperCase)
Range("C" & i).Value = RegEx.Replace(CStr(Matches(0)),
"SOME STRING 1")
End If


Set RegEx = CreateObject("VBScript.RegExp")
RegEx.Pattern = "(SOME PATTERN 2)"

Range("B" & i).Activate
strTest = ActiveCell.Text
valid = RegEx.test(strTest)
If valid = True Then
Set Matches = RegEx.Execute(strTest)
Range("C" & i).Value = StrConv(CStr(Matches(0)),
vbProperCase)
Range("C" & i).Value = RegEx.Replace(CStr(Matches(0)),
"SOME STRING 2")
End If


Set RegEx = CreateObject("VBScript.RegExp")
RegEx.Pattern = "(SOME PATTERN 3)"

Range("B" & i).Activate
strTest = ActiveCell.Text
valid = RegEx.test(strTest)
If valid = True Then
Set Matches = RegEx.Execute(strTest)
Range("C" & i).Value = StrConv(CStr(Matches(0)),
vbProperCase)
Range("C" & i).Value = RegEx.Replace(CStr(Matches(0)),
"SOME STRING 3")
End If
Next
End Sub

Thank you for any help here.

 

[PCLIVE]

Just before your "End If" for 'If valid=True', I think you could just add
"Next i"

Does that help?
Paul

 

[ChipButtyMan]

Just before your "End If" for 'If valid=True', I think you could just add
"Next i"

Does that help?
Paul

--
















- Show quoted text -

Thanks Paul but it doesn't work.
For without Next error.

 

[Dave Peterson]

But you still want to do those extra couple of statements after the check the
status of valid???

If that's true, then maybe you could just use a series of if/then/else's:

Option Explicit
Sub RationaliseOrders()
Dim RegEx As Object
Dim strTest As String
Dim valid As Boolean
Dim Matches As Object
Dim i As Integer

For i = 1 To 2000
Set RegEx = CreateObject("VBScript.RegExp")
RegEx.Pattern = "(SOME PATTERN 1)"
Range("B" & i).Activate
strTest = ActiveCell.Text
valid = RegEx.test(strTest)
If valid = True Then
Set Matches = RegEx.Execute(strTest)
Range("C" & i).Value = StrConv(CStr(Matches(0)), vbProperCase)
Range("C" & i).Value _
= RegEx.Replace(CStr(Matches(0)), "SOME STRING 1")
Else
Set RegEx = CreateObject("VBScript.RegExp")
RegEx.Pattern = "(SOME PATTERN 2)"
Range("B" & i).Activate
strTest = ActiveCell.Text
valid = RegEx.test(strTest)
If valid = True Then
Set Matches = RegEx.Execute(strTest)
Range("C" & i).Value = StrConv(CStr(Matches(0)), vbProperCase)
Range("C" & i).Value _
= RegEx.Replace(CStr(Matches(0)), "SOME STRING 2")
Else
Set RegEx = CreateObject("VBScript.RegExp")
RegEx.Pattern = "(SOME PATTERN 3)"

Range("B" & i).Activate
strTest = ActiveCell.Text
valid = RegEx.test(strTest)
If valid = True Then
Set Matches = RegEx.Execute(strTest)
Range("C" & i).Value _
= StrConv(CStr(Matches(0)), vbProperCase)
Range("C" & i).Value _
= RegEx.Replace(CStr(Matches(0)), "SOME STRING 3")
End If
End If
End If
Next i
End Sub

 

[Rick Rothstein]

Add a label just before your Next statement

....
....
End If
Continue:
Next

I am using Continue for the Label's name in this example. Now, do your test
and use GoTo to skip to the end of the loop...

If <<YourLogicalTestHere>> Then GoTo Continue

 

[ChipButtyMan]

Add a label just before your Next statement

            ....
            ....
        End If
Continue:
Next

I am using Continue for the Label's name in this example. Now, do your test
and use GoTo to skip to the end of the loop...

If <<YourLogicalTestHere>> Then GoTo Continue

--
Rick (MVP - Excel)
















- Show quoted text -

Hi Rick,
I'm not familiar with labels. I did this;

Dim Continue As Label
and entered the code as per your advice
but I get the error 'Label not defined'

Any advice is most appreciated Thanks for your help everyone.

 

[ChipButtyMan]

Hi Rick,
            I'm not familiar with labels. I did this;

Dim Continue As Label
and entered the code as per your advice
but I get the error 'Label not defined'

Any advice is most appreciated Thanks for your help everyone.- Hide quoted text -

- Show quoted text -

Hello Dave,
I just tried your solution & it worked just fine.
Thanks for your help.
I'm still interested to learn about Ricks label solution.
The label idea is something I would like to learn.

 

[Rick Rothstein]

Remove the "Dim As Label" that you added (I didn't say to do that) and just
use the code I posted as I instructed. The Label you Dim'med was a control;
the label (I think the help files refer to it as a "line label") I was
referring to is a statement in your code (the Continue with a colon after
it... the colon is what makes it a label and the GoTo statement can be used
to go to a statement label).

--
Rick (MVP - Excel)

Add a label just before your Next statement

....
....
End If
Continue:
Next

I am using Continue for the Label's name in this example. Now, do your
test
and use GoTo to skip to the end of the loop...

If <<YourLogicalTestHere>> Then GoTo Continue

--
Rick (MVP - Excel)
















- Show quoted text -

Hi Rick,
I'm not familiar with labels. I did this;

Dim Continue As Label
and entered the code as per your advice
but I get the error 'Label not defined'

Any advice is most appreciated Thanks for your help everyone.

 

[Rick Rothstein]

Here is a simple example to show you how the label idea works. Go to code
window and copy/paste this subroutine in it...

Sub Test()
Dim X As Long
For X = 1 To 100
If X > 10 And X < 91 Then GoTo Continue
Debug.Print X
Continue:
Next
End Sub

Now run it. The loop will execute 100 times, but only 20 numbers (1 thru 10
and 91 thru 100) will be printed out to the Immediate window. In the
If..Then statement, whenever the loop counter X is greater than 10 and less
than 91, the Go To Continue statement will be executed. What this does is
skip over any remaining code and immediately go to the line labeled
Continue: (the colon is what makes the word Continue a label).

--
Rick (MVP - Excel)

Hi Rick,
I'm not familiar with labels. I did this;

Dim Continue As Label
and entered the code as per your advice
but I get the error 'Label not defined'

Any advice is most appreciated Thanks for your help everyone.- Hide quoted
text -

- Show quoted text -

Hello Dave,
I just tried your solution & it worked just fine.
Thanks for your help.
I'm still interested to learn about Ricks label solution.
The label idea is something I would like to learn.