|  | VBA; Escaping a 'For Next' loop question | | |
| | | ChipButtyMan |  |
|  Posted: Fri Aug 29, 2008 6:01 pm Post subject: VBA; Escaping a 'For Next' loop question |  |
| |  | |
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 | |
| | Posted: Fri Aug 29, 2008 6:01 pm Post subject: Re: VBA; Escaping a 'For Next' loop question | |
| | | |
Just before your "End If" for 'If valid=True', I think you could just add
"Next i"
Does that help?
Paul
--
"ChipButtyMan" <colingray45@btinternet.com> wrote in message
news:a8b2ad00-52a8-47bb-b3ef-3f8b0a463f2c@d1g2000hsg.googlegroups.com...
| Quote: | 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. |
|
| |
| | | Dave Peterson | |
| | Posted: Fri Aug 29, 2008 10:08 pm Post subject: Re: VBA; Escaping a 'For Next' loop question | |
| | | |
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
ChipButtyMan wrote:
| Quote: |
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.
|
--
Dave Peterson |
| |
| | | Rick Rothstein | |
| | Posted: Fri Aug 29, 2008 10:12 pm Post subject: Re: VBA; Escaping a 'For Next' loop question | |
| | | |
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)
"ChipButtyMan" <colingray45@btinternet.com> wrote in message
news:a8b2ad00-52a8-47bb-b3ef-3f8b0a463f2c@d1g2000hsg.googlegroups.com...
| Quote: | 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. |
|
| |
| | | ChipButtyMan | |
| | Posted: Fri Aug 29, 2008 11:13 pm Post subject: Re: VBA; Escaping a 'For Next' loop question | |
| | | |
On Aug 29, 7:35 pm, "PCLIVE" <pclive(RemoveThis)@cox.net> wrote:
| Quote: | Just before your "End If" for 'If valid=True', I think you could just add
"Next i"
Does that help?
Paul
--
"ChipButtyMan" <colingra...@btinternet.com> wrote in message
news:a8b2ad00-52a8-47bb-b3ef-3f8b0a463f2c@d1g2000hsg.googlegroups.com...
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.- Hide quoted text -
- Show quoted text -
|
Thanks Paul but it doesn't work.
For without Next error. |
| |
| | | Rick Rothstein | |
| | Posted: Sat Aug 30, 2008 4:15 pm Post subject: Re: VBA; Escaping a 'For Next' loop question | |
| | | |
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)
"ChipButtyMan" <colingray45@btinternet.com> wrote in message
news:67e27b26-12c2-44b5-9f60-c92e7460c61d@w7g2000hsa.googlegroups.com...
On Aug 30, 1:12 am, "Rick Rothstein"
<rick.newsNO.S...@NO.SPAMverizon.net> wrote:
| Quote: | 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)
"ChipButtyMan" <colingra...@btinternet.com> wrote in message
news:a8b2ad00-52a8-47bb-b3ef-3f8b0a463f2c@d1g2000hsg.googlegroups.com...
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.- Hide quoted text -
- 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 | |
| | Posted: Sat Aug 30, 2008 4:24 pm Post subject: Re: VBA; Escaping a 'For Next' loop question | |
| | | |
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)
"ChipButtyMan" <colingray45@btinternet.com> wrote in message
news:8d3da405-9317-42f5-90da-eb03a748af25@s50g2000hsb.googlegroups.com...
On Aug 30, 6:56 pm, ChipButtyMan <colingra...@btinternet.com> wrote:
| Quote: | On Aug 30, 1:12 am, "Rick Rothstein"
rick.newsNO.S...@NO.SPAMverizon.net> wrote:
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)
"ChipButtyMan" <colingra...@btinternet.com> wrote in message
news:a8b2ad00-52a8-47bb-b3ef-3f8b0a463f2c@d1g2000hsg.googlegroups.com...
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.- Hide quoted text -
- 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.- 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. |
| |
| | | ChipButtyMan | |
| | Posted: Sat Aug 30, 2008 5:56 pm Post subject: Re: VBA; Escaping a 'For Next' loop question | |
| | | |
On Aug 30, 1:12 am, "Rick Rothstein"
<rick.newsNO.S...@NO.SPAMverizon.net> wrote:
| Quote: | 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)
"ChipButtyMan" <colingra...@btinternet.com> wrote in message
news:a8b2ad00-52a8-47bb-b3ef-3f8b0a463f2c@d1g2000hsg.googlegroups.com...
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.- Hide quoted text -
- 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 | |
| | Posted: Sat Aug 30, 2008 6:12 pm Post subject: Re: VBA; Escaping a 'For Next' loop question | |
| | | |
On Aug 30, 6:56 pm, ChipButtyMan <colingra...@btinternet.com> wrote:
| Quote: | On Aug 30, 1:12 am, "Rick Rothstein"
rick.newsNO.S...@NO.SPAMverizon.net> wrote:
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)
"ChipButtyMan" <colingra...@btinternet.com> wrote in message
news:a8b2ad00-52a8-47bb-b3ef-3f8b0a463f2c@d1g2000hsg.googlegroups.com...
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.- Hide quoted text -
- 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.- 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. |
| |
|
|
