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).
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