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