CODEWITHSUNDEEP
excelvbacopycriteria
Emma F
Emma F

Reputation: 11

VBA to create several lists based on multiple columns and criteria

I have two sheets, one input sheet with a large data set (Input SAP) and one sheet where I would like to list instances based on different criteria, but also in different categories (Output). I would want to push a button in the "Output" and get the different lists. In the below code, there are two lists, but there will be more.

The output sheet would be something like this: Column E would list all SAP numbers where column H in the input sheet is blank. Column G would list all SAP numbers where column O is equal to zero.

Column E gets populated correctly with the below code, but nothing happens to column G.I do not know how to fill several columns within the same sub.. I would also prefer not to make seperate row definitions since there will be approx. 10 different lists in the end.

Any help is greatly appreciated!

   Private Sub CommandButton1_Click()
     ReadIncomplete
   End Sub


   Private Sub ReadIncomplete()
   Dim wb As Workbook: Set wb = ThisWorkbook
   Dim ws As Worksheet: Set ws = wb.Worksheets("Output")
   Dim iws As Worksheet: Set iws = wb.Worksheets("Input SAP")

   irow = ws.Range("E7").Row
   RowNo = iws.Range("A2").Row


  Do Until iws.Cells(RowNo, 2) = ""
     If iws.Cells(RowNo, "H") = "" Then
     ws.Cells(irow, "E") = iws.Cells(RowNo, 2)
     irow = irow + 1
  End If
  RowNo = RowNo + 1
  Loop

  Do Until iws.Cells(RowNo, 2) = ""
     If iws.Cells(RowNo, "O") = 0 Then
     ws.Cells(irow, "G") = iws.Cells(RowNo, 2)
     irow = irow + 1
  End If
  RowNo = RowNo + 1
  Loop

  End Sub

Upvotes: 1

Views: 671

Answers (1)

cybernetic.nomad
cybernetic.nomad

Reputation: 6408

A more efficient way to loop would be:

irowE = 7
irowG = 7
RowNo = 2

Do Until iws.Cells(RowNo, 2) = ""
    If iws.Cells(RowNo, "H") = "" Then
        ws.Cells(irowE, "E") = iws.Cells(RowNo, 2)
        irowE = irowE + 1
    End if
    If iws.Cells(RowNo, "O") = 0 Then
        ws.Cells(irowG, "G") = iws.Cells(RowNo, 2)
        irowG = irowG + 1
    End If
    RowNo = RowNo + 1
Loop

Having 10 different variables for 10 lists may seem like a pain, but it will greatly speed things up since you will only loop through your input sheet once.

Upvotes: 1

Related Questions