Error in the VBA code

Question

The aim of my code is to print available if there is any data betweeen L1 to W1000 of length 7.

Although it finds the value of length 7, my code doesn't obey exit for.

what is the reason?

Private Sub CommandButton1_Click()

Dim Prod As Variant
Dim Dev As Variant
Dim counter As Integer
Dim j As Variant

Prod = Array("PBA_100", "PCA_500", "PRD_500", "PGA_500", "PVD_500")

For j = LBound(Prod) To UBound(Prod)
     MsgBox Prod(j)

 With ThisWorkbook.Sheets(Prod(j))
 LastRow = ThisWorkbook.Sheets(Prod(j)).Columns("A").Cells.Find("*", 
 SearchOrder:=xlByRows, LookIn:=xlValues, SearchDirection:=xlPrevious).Row
    For Each cell In .Range("N2:N" & LastRow)
        arr = Split(Replace(cell.Value, "  ", " "), " ")
        For Each arrElem In arr
            If Len(arrElem) = 7 Then
            MsgBox arrElem
        Exit For
      Else
      MsgBox arrElem
            End If
        Next arrElem
    Next cell
 End With

Next j

End Sub

k is defined at the top i have only included the part where i have the issue. — Anirudh Chauhan
– Anirudh Chauhan, Commented Sep 6, 2017 at 10:29

Vityata · Accepted Answer · 2017-09-06 10:36:51Z

1

You need to exit the nested for loop, if you find available value. Like this:

With ThisWorkbook.Sheets(Prod(j))
LastRow = ThisWorkbook.Sheets(Prod(j)).Columns("A").Cells.Find("*", 
SearchOrder:=xlByRows, LookIn:=xlValues, SearchDirection:=xlPrevious).Row
    For Each cell In .Range("L1:W1000", .Cells(.Rows.Count, "A").End(xlUp))
        arr = Split(Replace(cell.Value, "  ", " "), " ")
        For Each arrElem In arr
            If Len(arrElem) = 7 Then
            ThisWorkbook.Sheets("Sheet1").Range("D" & k).Value = "available"
            Exit For 'You exit only the nested loop here!
            Else
            ThisWorkbook.Sheets("Sheet1").Range("D" & k).Value = "NOT available"
            End If
        Next arrElem
    Next cell
End With

edited Sep 6, 2017 at 10:36

answered Sep 6, 2017 at 10:30

Vityata

43.8k10 gold badges64 silver badges110 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Vityata Over a year ago

@AnirudhChauhan - start debugging step by step with F8 and try paying attention what arrElem is and its value. For example add debug.print arrElem before the If Len(arrElem)=7. Hopefully, after about 30 minutes of pressing F8 you would see the solution.

Collectives™ on Stack Overflow

Error in the VBA code

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related