Thread: Break & Continue in VB (Excel)

I have a process that I need to run in an Excel spreadsheet using a macro. I have a loop that iterates through the records and evaluates them; then performs an action based on that. Sometimes there are conditions where I need to break out of the loop, but I want to continue if the condition that caused a break warrants it.

Unfortunately, I see that there is no Break... Continue available (as it is in C#). Any ideas on how to circumvent that?

Thanks!

P.S. I can post code if needed

Posting one's code is always helpful...

Anyway...depending on the loop, there are ways to get out of it. For example, in a For...Next loop use: Exit For

Exiting is not a problem. I use Exit Do in this case. I thought about it further, and I am thinking that I may need a function call in the loop to make that decision as to whether to exit the loop. When I get that worked out I will post back, but in the meantime, I guess I am without a continue.... probably a good thing anyway.

As for posting code, the only reason I did not do so, is that the code makes no sense unless you know the problem I am trying to solve... that explanation is not short

I'm not sure I totally understand...but maybe look into GoTo

I thought about GoTo, but I try not to use it if possible (as it makes the code less readable). I will create a function I think... then call that function in the loop. The purpose of the function would be to make the decision as to whether to continue the process or not based on the data in the current record (if that makes any sense). It is probably better to do it that way anyhow, but when I get it done, I will post here

Update... I think I got this in a workable state finally. This process basically takes records in a label format (inserted into column A) i.e.:

Name
Address
City, State zip

and inserts a new line after each. A subsequent process will split the data into fields on the new line. There is probably an easier way to do this, of that I am certain

Code:

Sub linereplace()

Dim recno As Integer
recno = 1

Dim checktext As String
Dim EndofLine As String
Dim BoxCheck As String
Dim IsEndOfLine As Boolean
IsEndOfLine = False 'Initialize variable to False... we'll change it when needed.

Range("A65536").Select 'This is the presumed end of the worksheet
Selection.End(xlUp).Select

Dim LastRow As Integer
LastRow = ActiveCell.Row

Dim MaxRecord As Integer
MaxRecord = LastRow * 3

'First delete any data in columns other than A
Columns("B:IV").Select
Selection.Delete

'Remove blank lines sub:
RemoveBlankLines

Range("A1").Select 'Ensures that A1 is the start point

Do Until recno = MaxRecord
 
checktext = Range("A" & recno)
checktext = Strings.Trim(checktext)
checktext = Strings.Replace(checktext, "-", "")
 
Dim EvalArr As Variant
EvalArr = Split(checktext, " ", , vbTextCompare)

On Error Resume Next

Dim MaxArr As Variant
MaxArr = UBound(EvalArr)

'the last item in the line must be numeric
If IsNumeric(EvalArr(MaxArr)) Then
    IsEndOfLine = True
End If

If Len(EvalArr(MaxArr)) > 9 Or Len(EvalArr(MaxArr)) < 5 Then
    IsEndOfLine = False
End If

If IsEndOfLine = True Then
    Dim iCount As Integer
    iCount = 0

    For iCount = 0 To MaxArr
    Dim s As String
    s = EvalArr(iCount)

    If Strings.LCase(s) = "box" Or Strings.LCase(s) = "pmb" Then
        IsEndOfLine = False
        Exit For
    End If
    Next
End If

If IsEndOfLine = True Then
    Dim NewCell As String
    NewCell = Range("A" & (recno + 1))
    If Not NewCell = "" Then
        Rows(recno + 1 & ":" & recno + 1).Select
        Selection.Insert shift:=xlDown
    End If 'Evaluate New Cell Contents
End If 'Is this the end of the line?
recno = recno + 1
Loop
 
End Sub

Private Sub RemoveBlankLines()
'Select the last row then move up to the last row where data is found.
Range("A65536").Select
Selection.End(xlUp).Select

Do Until ActiveCell.Row = 1
Dim i As Integer

Selection.End(xlUp).Select
i = ActiveCell.Row

Dim RemoveRow As Integer
RemoveRow = i - 1
If RemoveRow < 1 Then Exit Do

Rows(RemoveRow & ":" & RemoveRow).Select
Selection.Delete shift:=xlUp

Loop 'End remove extra spaces loop

End Sub

It always surprises me how complex the simplest task can be! The only way to know that the end of the record was reached is when the last item on a line is a number AND it is not a PO Box or PMB. However, I can see there being other exclusions as well....