VBA; Escaping a 'For Next' loop question

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

ChipButtyMan - 29 Aug 2008 19:01 GMT

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.

Reply to this Message

PCLIVE - 29 Aug 2008 19:35 GMT

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

Does that help?
Paul

> Hi,
> I have some code which uses a For Next loop which works very well
[quoted text clipped - 59 lines]
>
> Thank you for any help here.

Reply to this Message

ChipButtyMan - 30 Aug 2008 00:13 GMT

> Just before your "End If" for 'If valid=True', I think you could just add
> "Next i"
[quoted text clipped - 69 lines]
>
> - Show quoted text -

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

Reply to this Message

Dave Peterson - 30 Aug 2008 01:08 GMT

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

> Hi,
> I have some code which uses a For Next loop which works very well
[quoted text clipped - 59 lines]
>
> Thank you for any help here.

Signature

Dave Peterson

Reply to this Message

Rick Rothstein - 30 Aug 2008 01:12 GMT

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

Signature

Rick (MVP - Excel)

> Hi,
> I have some code which uses a For Next loop which works very well
[quoted text clipped - 59 lines]
>
> Thank you for any help here.

Reply to this Message

ChipButtyMan - 30 Aug 2008 18:56 GMT

On Aug 30, 1:12 am, "Rick Rothstein"
<rick.newsNO.S...@NO.SPAMverizon.net> wrote:

> Add a label just before your Next statement
>
[quoted text clipped - 77 lines]
>
> - 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.

Reply to this Message

ChipButtyMan - 30 Aug 2008 19:12 GMT

> On Aug 30, 1:12 am, "Rick Rothstein"
>
[quoted text clipped - 95 lines]
>
> - 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.

Reply to this Message

Rick Rothstein - 30 Aug 2008 19:24 GMT

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).

Signature

Rick (MVP - Excel)

> On Aug 30, 1:12 am, "Rick Rothstein"
>
[quoted text clipped - 97 lines]
>
> - 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.

Reply to this Message

Rick Rothstein - 30 Aug 2008 19:15 GMT

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).

Signature

Rick (MVP - Excel)

On Aug 30, 1:12 am, "Rick Rothstein"
<rick.newsNO.S...@NO.SPAMverizon.net> wrote:

> Add a label just before your Next statement
>
[quoted text clipped - 82 lines]
>
> - Show quoted text -

Reply to this Message