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 ...
Need Help
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