Do Until loop nested in For loop, How advance to the "Next x" in the For Loop

[Brian Feth]

Windows 11, Excel 2021: I've been retired for a few years and want to use some of the VBA I seem to no long remember. I am making a work sheet to correlate Venders names to my bank statement's transaction descriptions. For example the bank statement may describe the transaction as "bla bla COSTCO bla bla bla". I have a column of possible Venders e.g. Apple, Amazon, Costco, etc. in a remote location on the spreadsheet in Column "J". The outer FOR loop is intended to proceed through the transaction descriptions. The nested Do Until loop cycles through the list of possible venders searching for a match using the "InStr" function. It matchs the transaction statement to the vender's name and place the Vender "Costco" in column "H" adjacent to "bla bla COSTCO bla bla" statement in column "J". The first discription statement is chosen by the For Loop and the Do Until loop cycles through the possible Venders, locates the proper vender and placing it into the proper column. From there I don't know how to get the routine to go to the next x. (I use long descriptive varrible names to help me keep track of what is what). Any help is greatly appreciated.

For x = 1 To NumDescripRows
ChosenDescript = Range("J" & TopDescripRow).Value 'Do Until TopDescripRow = BottomDescripRow

Do Until FirstVenderRow = LastVenderRow
Vender = Range("I" & FirstVenderRow).Value

If InStr(ChosenDescript, Vender) <> 0 Then 'i.e. There is a match, a positive result, True
Range("H" & TopDescripRow).Value = Vender 'Fills in the vender name in a column afjacent to Check description
FirstVenderRow = Range("I595").Row 'Resets the vender to the first in the Posssible vender list
'TopDescripRow = TopDescripRow + 1 'This is the I wound need to go to "Next x"

ElseIf InStr(ChosenDescript, Vender) = 0 Then 'No match, negitive result, False
FirstVenderRow = FirstVenderRow + 1
End If
Loop

'Application.ScreenUpdating = True
Next x
End Sub

 

[CephasOz]

Just put "Exit Do" where you need to leave the Do Until Loop:

    For x = 1 To NumDescripRows
        ChosenDescript = Range("J" & TopDescripRow).Value 'Do Until TopDescripRow = BottomDescripRow
        
        Do Until FirstVenderRow = LastVenderRow
            Vender = Range("I" & FirstVenderRow).Value
            
            If InStr(ChosenDescript, Vender) <> 0 Then 'i.e. There is a match, a positive result, True
                Range("H" & TopDescripRow).Value = Vender 'Fills in the vender name in a column afjacent to Check description
                FirstVenderRow = Range("I595").Row 'Resets the vender to the first in the Posssible vender list
                'TopDescripRow = TopDescripRow + 1 'This is the I wound need to go to "Next x"
                Exit Do
            
            ElseIf InStr(ChosenDescript, Vender) = 0 Then 'No match, negitive result, False
                FirstVenderRow = FirstVenderRow + 1
            End If
        Loop
        
        'Application.ScreenUpdating = True
    Next x
End Sub

 

[Brian Feth]

Just put "Exit Do" where you need to leave the Do Until Loop:

    For x = 1 To NumDescripRows
        ChosenDescript = Range("J" & TopDescripRow).Value 'Do Until TopDescripRow = BottomDescripRow
       
        Do Until FirstVenderRow = LastVenderRow
            Vender = Range("I" & FirstVenderRow).Value
           
            If InStr(ChosenDescript, Vender) <> 0 Then 'i.e. There is a match, a positive result, True
                Range("H" & TopDescripRow).Value = Vender 'Fills in the vender name in a column afjacent to Check description
                FirstVenderRow = Range("I595").Row 'Resets the vender to the first in the Posssible vender list
                'TopDescripRow = TopDescripRow + 1 'This is the I wound need to go to "Next x"
                Exit Do
           
            ElseIf InStr(ChosenDescript, Vender) = 0 Then 'No match, negitive result, False
                FirstVenderRow = FirstVenderRow + 1
            End If
        Loop
       
        'Application.ScreenUpdating = True
    Next x
End Sub

Just put "Exit Do" where you need to leave the Do Until Loop:

    For x = 1 To NumDescripRows
        ChosenDescript = Range("J" & TopDescripRow).Value 'Do Until TopDescripRow = BottomDescripRow
       
        Do Until FirstVenderRow = LastVenderRow
            Vender = Range("I" & FirstVenderRow).Value
           
            If InStr(ChosenDescript, Vender) <> 0 Then 'i.e. There is a match, a positive result, True
                Range("H" & TopDescripRow).Value = Vender 'Fills in the vender name in a column afjacent to Check description
                FirstVenderRow = Range("I595").Row 'Resets the vender to the first in the Posssible vender list
                'TopDescripRow = TopDescripRow + 1 'This is the I wound need to go to "Next x"
                Exit Do
           
            ElseIf InStr(ChosenDescript, Vender) = 0 Then 'No match, negitive result, False
                FirstVenderRow = FirstVenderRow + 1
            End If
        Loop
       
        'Application.ScreenUpdating = True
    Next x
End Sub

Just put "Exit Do" where you need to leave the Do Until Loop:

    For x = 1 To NumDescripRows
        ChosenDescript = Range("J" & TopDescripRow).Value 'Do Until TopDescripRow = BottomDescripRow
       
        Do Until FirstVenderRow = LastVenderRow
            Vender = Range("I" & FirstVenderRow).Value
           
            If InStr(ChosenDescript, Vender) <> 0 Then 'i.e. There is a match, a positive result, True
                Range("H" & TopDescripRow).Value = Vender 'Fills in the vender name in a column afjacent to Check description
                FirstVenderRow = Range("I595").Row 'Resets the vender to the first in the Posssible vender list
                'TopDescripRow = TopDescripRow + 1 'This is the I wound need to go to "Next x"
                Exit Do
           
            ElseIf InStr(ChosenDescript, Vender) = 0 Then 'No match, negitive result, False
                FirstVenderRow = FirstVenderRow + 1
            End If
        Loop
       
        'Application.ScreenUpdating = True
    Next x
End Sub

 

[Brian Feth]

Just put "Exit Do" where you need to leave the Do Until Loop:

    For x = 1 To NumDescripRows
        ChosenDescript = Range("J" & TopDescripRow).Value 'Do Until TopDescripRow = BottomDescripRow
       
        Do Until FirstVenderRow = LastVenderRow
            Vender = Range("I" & FirstVenderRow).Value
           
            If InStr(ChosenDescript, Vender) <> 0 Then 'i.e. There is a match, a positive result, True
                Range("H" & TopDescripRow).Value = Vender 'Fills in the vender name in a column afjacent to Check description
                FirstVenderRow = Range("I595").Row 'Resets the vender to the first in the Posssible vender list
                'TopDescripRow = TopDescripRow + 1 'This is the I wound need to go to "Next x"
                Exit Do
           
            ElseIf InStr(ChosenDescript, Vender) = 0 Then 'No match, negitive result, False
                FirstVenderRow = FirstVenderRow + 1
            End If
        Loop
       
        'Application.ScreenUpdating = True
    Next x
End Sub

Just put "Exit Do" where you need to leave the Do Until Loop:

    For x = 1 To NumDescripRows
        ChosenDescript = Range("J" & TopDescripRow).Value 'Do Until TopDescripRow = BottomDescripRow
       
        Do Until FirstVenderRow = LastVenderRow
            Vender = Range("I" & FirstVenderRow).Value
           
            If InStr(ChosenDescript, Vender) <> 0 Then 'i.e. There is a match, a positive result, True
                Range("H" & TopDescripRow).Value = Vender 'Fills in the vender name in a column afjacent to Check description
                FirstVenderRow = Range("I595").Row 'Resets the vender to the first in the Posssible vender list
                'TopDescripRow = TopDescripRow + 1 'This is the I wound need to go to "Next x"
                Exit Do
           
            ElseIf InStr(ChosenDescript, Vender) = 0 Then 'No match, negitive result, False
                FirstVenderRow = FirstVenderRow + 1
            End If
        Loop
       
        'Application.ScreenUpdating = True
    Next x
End Sub

Thank you CephasOz, that worked perfectly. Thanks so much.

 

[CephasOz]

Glad to help.