(Solved) – Successive rows are being skipped in the Do….Loop

  • by
(solved)-–-successive-rows-are-being-skipped-in-the-do….loop

I am searching through an excel worksheet to find occurrences. I am passing in the worksheet and the range. The loop is missing successive cells containing those values “LED”, “MV” and “HPS”. I am making a mistake somewhere in my loop which I cannot figure out. I would appreciate your help on this.

Dim RngIL As Range
Set RngIL = Range("I6:I" & lastRow)
Set sh = ActiveWorkbook.Worksheets("Detailed Selection")
lastRow = Cells(Rows.Count, "M").End(xlUp).Row

Call MultipleSearch(RngIL, sh)
    Sub MultipleSearch(searchRng As Range, sh As Worksheet)

        ' Get name to search
    '    Dim name As String: name = "Elli"

        ' Get search range
        Dim rgSearch As Range
        Set rgSearch = searchRng
        Dim RngQ As Range
        Dim RngR As Range

        Dim cell As Range
        Dim element As Variant
        Dim findItems(3) As String
        findItems(0) = "LED"
        findItems(1) = "HPS"
        findItems(2) = "MV"

        sh.Unprotect dsPassword
        For Each element In findItems

        Set cell = rgSearch.Find(CStr(element))

        ' If not found then exit
        If cell Is Nothing Then
            Exit Sub
        End If

        ' Store first cell address
        Dim firstCellAddress As String
        firstCellAddress = cell.Address

        ' Find all cells containing Elli
        Do
            'Debug.Print "Found: " & cell.Address
            Set cell = rgSearch.FindNext(cell)
        If cell.Value = "LED" Then

    '        If searchMode = "DisableLEDBulb" Then

    '           sh.Unprotect dsPassword
               With sh.Range(cell.Offset(0, -8).Address & ":" & cell.Offset(0, 2).Address)
                       .Interior.Color = RGB(255, 255, 204)
               End With
               With sh.Range(cell.Offset(0, 3).Address & ":" & cell.Offset(0, 7).Address)
                   .Interior.Color = RGB(217, 217, 217)
                   .Validation.Delete
               End With
               With sh.Range(cell.Offset(0, 8).Address & ":" & cell.Offset(0, 9).Address)
                   .Interior.Color = RGB(221, 217, 196)
                   .Validation.Delete
               End With
               sh.Range(cell.Offset(0, -8).Address & ":" & cell.Offset(0, 9).Address).Locked = True
    '           sh.Protect dsPassword
    '        End If
         ElseIf cell.Value = "High Press Sodium" Then

    '         If searchMode = "HPSColorWattage" Then
    '           sh.Unprotect dsPassword
                If sh.Range(cell.Offset(0, 1).Address).Value <> "100" And sh.Range(cell.Offset(0, 1).Address).Value <> "150" And sh.Range(cell.Offset(0, 1).Address).Value <> "200" Then

                   sh.Range(cell.Offset(0, 1).Address).Locked = False
                   sh.Range(cell.Offset(0, 1).Address).Interior.Color = RGB(252, 155, 48)
                   sh.Range(cell.Offset(0, 1).Address).Locked = True

                End If

                sh.Range(cell.Offset(0, 7).Address).Locked = False
            '    Sh.Range(Target.Offset(0, 7).Address).Value = "No"
    '            sh.Protect dsPassword

    '        End If
          ElseIf cell.Value = "Mercury Vapour" Then

    '         If searchMode = "MVColorWattage" Then
    '         sh.Unprotect dsPassword
               If sh.Range(cell.Offset(0, 1).Address).Value <> "175" And sh.Range(cell.Offset(0, 1).Address).Value <> "250" And sh.Range(cell.Offset(0, 1).Address).Value <> "400" Then

                  sh.Range(cell.Offset(0, 1).Address).Locked = False
                  sh.Range(cell.Offset(0, 1).Address).Interior.Color = RGB(252, 155, 48)
                  sh.Range(cell.Offset(0, 1).Address).Locked = True

               End If

                sh.Range(cell.Offset(0, 7).Address).Locked = True
                sh.Range(cell.Offset(0, 7).Address).Value = "Yes"

                Set RngQ = sh.Range(cell.Offset(0, 8).Address): RngQ.Locked = False
                 ChangeValidation RngQ, "=listone!F$2:F$4"

                 Set RngR = sh.Range(cell.Offset(0, 9).Address): RngR.Locked = False
                 ChangeValidation RngR, "=listone!G$2:G$9"
    '            sh.Protect dsPassword
    '        End If
          End If

        Loop While firstCellAddress <> cell.Address

        Next element
        sh.Protect dsPassword

    End Sub

Leave a Reply

Your email address will not be published. Required fields are marked *