Do while loop

[njones3]

I am hoping someone can help, I am trying to add a 'do while' type loop into some existing code. The current code makes manual changes to my data dump but it can only do one row at a time. Sometimes there are a large amount of rows which all need the same adjustment made to them so i need the loop to repeat the process until it has modified every relevant line of my data.

The section of existing code is below:

    If AdjustmentsArray(i, 1) = "Renewals (ESS Status)" Then
                    CheckColumn1 = Cells.Find(What:="MPAN", after:=Cells(1, 1), lookat:=xlWhole, searchorder:=xlByRows, MatchCase:=True).Column
                    CheckColumn2 = Cells.Find(What:="Contract ID", after:=Cells(1, 1), lookat:=xlWhole, searchorder:=xlByRows, MatchCase:=True).Column
                    CheckColumn3 = Cells.Find(What:="MPID", after:=Cells(1, 1), lookat:=xlWhole, searchorder:=xlByRows, MatchCase:=True).Column
                    CheckIterations = Application.CountIf(Columns(CheckColumn1), AdjustmentsArray(i, 2))
                    j = 1
                    CheckRow = 1
                    Set RangeFind = Cells.Find(What:=AdjustmentsArray(i, 2), after:=Cells(CheckRow, CheckColumn1), lookat:=xlWhole, searchorder:=xlByColumns)
                    If Not RangeFind Is Nothing Then
                        For j = 1 To CheckIterations
                            CheckRow = Cells.Find(What:=AdjustmentsArray(i, 2), after:=Cells(CheckRow, CheckColumn1), lookat:=xlWhole, searchorder:=xlByColumns).Row
                            If Cells(CheckRow, CheckColumn2) = AdjustmentsArray(i, 3) And Cells(CheckRow, CheckColumn3) = AdjustmentsArray(i, 5) Then ChooseRow = CheckRow
                        Next j
                        If ChooseRow = 0 Then Sheets("Manual Adjustments").Cells(i + FindRow(19), FindColumn(19)) = "Record not found"
                    Else
                        Sheets("Manual Adjustments").Cells(i + FindRow(19), FindColumn(19)) = "Record not found"
                    End If
                Else

Ideally it would look at column B and if there is a 'yes' in the box it would repeat the loop until all lines have been updated.

Thanks in advance,

Nathan

 

[bobsan42]

something like this:

Dim rng As Range, wsh As Worksheet, cc As Range
Set wsh = ThisWorkbook.Worksheets("[COLOR=#ff0000]sheet1[/COLOR]")
Set rng = Intersect(wsh.UsedRange, wsh.Range("B:B"))
For Each cc In rng
    If cc.Value = "yes" Then
        '.................. here you do what you need to do .......................
    End If
Next cc
Set rng = Nothing
Set cc = Nothing
Set wsh = Nothing

cc will be each cell in column B with yes in it when your code runs - so you can refer to it as range.
set the name in red to your sheet name. You will also probably have to make some adjustments to your code.