dimanche 29 décembre 2019

Excel VBA code to run a loop only if the value in column 16 of the active sheet is > 0

This is my first experience writing any kind of code. I have been building a tracking system for my work in Excel. I have everything that I want currently working except I have a user form that when you click the command button it will look at my current inventory table (the table has a column (16 or P) that list how many cases of a product we should order to get us to our target stock quantity) and return a list of products and what we need to order. I have the form so it works, It populates a list box on the form with all the info that I want, but I would like it to exclude any rows that the table says we don't need to order. Here is my current code.

Private Sub CommandButton1_Click()
    Dim ws As Worksheet
    Dim i 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

    For i = 1 To LastRow
        lstProd.AddItem ws.Cells(i, 1).Value
        lstProd.List(i - 1, 1) = ws.Cells(i, 2).Value
        lstProd.List(i - 1, 2) = ws.Cells(i, 3).Value
        lstProd.List(i - 1, 3) = "$" & Format(ws.Cells(i, 4).Value, "0.00")
        lstProd.List(i - 1, 4) = ws.Cells(i, 5).Value
        lstProd.List(i - 1, 5) = ws.Cells(i, 6).Value
        lstProd.List(i - 1, 6) = ws.Cells(i, 9).Value
        lstProd.List(i - 1, 7) = ws.Cells(i, 14).Value
        lstProd.List(i - 1, 8) = ws.Cells(i, 16).Value
    Next i
End Sub

I have tried a lot of if ws.cells(i, 16) = "0" and For ws.cells..... but always end up with different errors. I know it is something simple that I am missing but it has just eluded me so I thought I would break down and ask for help.

Aucun commentaire:

Enregistrer un commentaire