jeudi 4 avril 2019

Set up a variable with multiple IF statements linked to a FOR loop with a counter

I have 3 sheets. In the sheet "Manager", there are 7 dropdown lists for criteria: H5, H7, H9, H11, H13, H15, H17. Once the criteria are selected and the user clicks on the button "COPY", the macro searches in the sheet "Data" columns A:G the rows matching the selected criteria. Then it copies the range P:W for the matching rows and pastes it in sheet "Quote" starting from row 11. It is important to note when the users does not select a criterion for any of the dropdown list, then that criterion is just ignored (cf. VbNullString in the code)

By now, the macro runs fine with multiple criteria selection for the Company dropdown list (H5) and single criterion selection for the others (H7, H9, H11, H13, H15, H17).

enter image description here

Sub Quote()

Dim Source As Worksheet
Dim Target As Worksheet
Dim Manager As Worksheet
Dim Multiple () As String 'Here
Dim InfoA As String
Dim InfoB As String
Dim InfoC As String
Dim ProductType As String
Dim SalesStatus As String
Dim finalrow As Integer
Dim counter As Integer
Dim I As Integer

Set Source = Worksheets("Data")
Set Target = Worksheets("Quote")
Set Manager = Worksheets("Manager")
If Worksheets("Manager").Range("H5").Value <> vbNullString Then 'Here
Multiple = Split(Worksheets("Manager").Range("H5").Value, ",") 'Here
   If Worksheets("Manager").Range("H13").Value <> vbNullString Then 'Modified
   Multiple = Split(Worksheets("Manager").Range("H13").Value, ",") 'Here

      Else 'Here
      Multiple = Split("", "") 'Here
   End If 'Here
End If 'Here
InfoA = Worksheets("Manager").Range("H7").Value
InfoB = Worksheets("Manager").Range("H9").Value
InfoC = Worksheets("Manager").Range("H11").Value
ProductType = Worksheets("Manager").Range("H15").Value
SalesStatus = Worksheets("Manager").Range("H17").Value
finalrow = Source.Cells(Rows.Count, 1).End(xlUp).Row


For counter = 0 To UBound(Multiple) 'Here
lookupMult = Trim(Multiple(counter)) 'Here

For I = 2 To finalrow

    thisComp = Source.Cells(I, 1)
    thisInfA = Source.Cells(I, 2)
    thisInfB = Source.Cells(I, 3)
    thisInfC = Source.Cells(I, 4)
    thisProd = Source.Cells(I, 5)
    thisType = Source.Cells(I, 6)
    thisSale = Source.Cells(I, 7)

    If (thisComp = lookupMult Or lookupMult = vbNullString) Then 'Here
        If (thisInfA = InfoA Or InfoA = vbNullString) Then
            If (thisInfB = InfoB Or InfoB = vbNullString) Then
                If (thisInfC = InfoC Or InfoC = vbNullString) Then
                    If (thisProd = lookupMult Or lookupMult = vbNullString) Then 'Here
                        If (thisType = ProductType Or ProductType = vbNullString) Then
                            If (thisSale = SalesStatus Or SalesStatus = vbNullString) Then
                    Source.Range(Source.Cells(I, 16), Source.Cells(I, 23)).Copy Target.Range("A200").End(xlUp).Offset(1, 0).Resize(1, 8)
                            End If
                        End If
                    End If
                End If
            End If
        End If
    End If
Next I
Next counter

End Sub

In addition to the multiple criteria selection for H5, I need also to enable it for the Product (H13). To do so, I tried to modify the variable Company using a more elaborated IF statement. But nothing is copy-pasted and I cannot figure out what I'm doing wrong. I added some comments 'Here to show what part of the code I modified.

Aucun commentaire:

Enregistrer un commentaire