Forum Discussion

TheTracker's avatar
TheTracker
Copper Contributor
Dec 28, 2019

Help on a nested Loop

I have the product to order user form so that the information I need is pulled into the list box.  My problem is I would like VBA to look at column P (16) on the "Current" worksheet and if the value in that column is 0 then don't pull that row of information from the spread sheet to the listbox.  I believe I am looking for a nested loop but I keep getting errors with everything I try.  I think I am just overlooking something really simple but being new to Excel, User forms and VBA I have been unable to come up with an answer for this.  Any help would be greatly appreciated.

Need Help

1 Reply

  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor
    Jan 05, 2020

    TheTracker 

    Cleaned up the code for the form a little bit, so that it became easier to read and maintain. Added an IF .... THEN ..... ELSE routine to make sure that your next item is not blank (vbNullString) and that the re-order value <> 0.

    Added counter "j" to keep track of  the number of items that have a re-order value not equal to zero. 

     

    Private Sub CommandButton1_Click()
Dim ws As Worksheet
Dim rng As Range
Dim i, j As Integer
Set ws = Worksheets("Current")
lstProd.Clear
lstProd.ColumnCount = 9
Dim LastRow As Long
LastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    
j = 0
    For i = 1 To LastRow

        If ws.Cells(i, 1).Value <> vbNullString And ws.Cells(i, 16).Value <> 0 Then
        
            lstProd.AddItem ws.Cells(i, 1).Value
            lstProd.List(j, 1) = ws.Cells(i, 2).Value
            lstProd.List(j, 2) = ws.Cells(i, 3).Value
            lstProd.List(j, 3) = "$" & Format(ws.Cells(i, 4).Value, "0.00")
            lstProd.List(j, 4) = ws.Cells(i, 5).Value
            lstProd.List(j, 5) = ws.Cells(i, 6).Value
            lstProd.List(j, 6) = ws.Cells(i, 9).Value
            lstProd.List(j, 7) = ws.Cells(i, 14).Value
            lstProd.List(j, 😎 = ws.Cells(i, 16).Value
            
            j = j + 1
         
            Else
            
        End If
        
Next i
End Sub

     

Resources