Search code examples
excelvbadrop-down-menucopy-paste

Paste rows if criteria from dropdown lists match the data in the table: skip part of the code if a cell containing a dropdown list is left blank

On the worksheet “Manager”, the user selects in dropdown lists the criteria that will be used to create a quotation. Once all the criteria are selected, he runs the below macro. The macro searches in the columns “Company”, “Info A”, and “Info B” of the worksheet “Data” the matching criteria. Every time it finds one row where the 3 criteria match, it copies the range P:W from the matching row and paste it to the worksheet “Quotation ENG” in A:H.

As long as the 3 criteria in “Manager” are filled, the corresponding ranges will be pasted in the “Quotation ENG”. But if one or two of the criteria are left blank in the “Manager”, then nothing is pasted in the “Quotation ENG”. It is normal because there are no blank cells in the “Data” table and the AND statement in the code links all criteria together.

It is not the behavior I want. I need to skip the search-copy-paste of any criterion which is left blank in “Manager”. For example in the below picture, the criterion from the cell E9, “Info B”, is left blank, and so it should only search the results for the criteria “Company/Brand” and “Info A” in the “Data” sheet.

Sample view of the 3 worksheets

Sub Quote()

    Dim Source As Worksheet
    Dim Target As Worksheet
    Dim Manager As Worksheet
    Dim Company() As String
    Dim InfoA As String
    Dim InfoB As String
    Dim Finalrow As Integer
    Dim counter As Integer
    Dim I As Integer

    Set Source = Worksheets("Data")
    Set Target = Worksheets("Quotation ENG")
    Set Manager = Worksheets("Manager")
    Company = Split(Worksheets("Manager").Range("E5").Value, ",")
    InfoA = Worksheets("Manager").Range("E7").Value
    InfoB = Worksheets("Manager").Range("E9").Value

    Finalrow = Source.Cells(Rows.Count, 1).End(xlUp).Row

    For counter = 0 To UBound(Company)

        For I = 2 To Finalrow
            If Source.Cells(I, 1) = Trim(Company(counter)) And Source.Cells(I, 2) = InfoA And Source.Cells(I, 3) = InfoB 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
        Next I
    Next counter

    Target.Activate
    Target.Range("A1").Select

End Sub

== EDIT ==

Here is the working after the modifications from elektrykalAJ.

Sub Quote()

Dim Source As Worksheet
Dim Target As Worksheet
Dim Manager As Worksheet
Dim Company() As String
Dim InfoA As String
Dim InfoB As String
Dim finalrow As Integer
Dim counter As Integer
Dim I As Integer

Set Source = Worksheets("Data")
Set Target = Worksheets("Quotation ENG")
Set Manager = Worksheets("Manager")
InfoA = Worksheets("Manager").Range("E7").Value
InfoB = Worksheets("Manager").Range("E9").Value
finalrow = Source.Cells(Rows.Count, 1).End(xlUp).Row

If Worksheets("Manager").Range("E5").Value <> vbNullString Then
Company = Split(Worksheets("Manager").Range("E5").Value, ",")
Else
Company = Split("", "")
End If

For counter = 0 To UBound(Company)
lookupComp = Trim(Company(counter))

For I = 2 To finalrow

    thisComp = Source.Cells(I, 1)
    thisInfA = Source.Cells(I, 2)
    thisInfB = Source.Cells(I, 3)

    If (thisComp = lookupComp Or lookupComp = vbNullString) Then
        If (thisInfA = InfoA Or InfoA = vbNullString) Then
            If (thisInfB = InfoB Or InfoB = 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
Next I
Next counter

Target.Activate
Target.Range("A1").Select

End Sub
Solution

  • Two things:

    1. If you don't want the code to "stop" when any of the criteria is blank, then you'll need to add some Or statements to your logic:

      If the "lookup value" is blank OR the "current value" equals the "lookup value" then...

    2. The efficient way to check for an empty string in VBA is to compare with vbNullString:

      If value = vbNullString Then ...

    Here is your modified code:

    I extracted some variables and replaced the And statements with If statements just to make it easier to read. I also made the variables lowercase:

    For counter = 0 To UBound(company)
    lookupComp = Trim(company(counter))

    For I = 2 To finalrow
        
        thisComp = source.Cells(I, 1)
        thisInfA = source.Cells(I, 2)
        thisInfB = source.Cells(I, 3)
        
        If (thisComp = lookupComp Or lookupComp = vbNullString) Then
            If (thisInfA = infoA Or infoA = vbNullString) Then
                If (thisInfB = infoB Or infoB = 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
    Next I
Next counter

    Bonus:

    If "Company" could be blank, then add this check when you define the company variable:

    
If Worksheets("Manager").Range("E5").Value <> vbNullString Then
    company = Split(Worksheets("Manager").Range("E5").Value, ",")
Else
    company = Split("", "")
End If

    Here is a clearer picture of what the OP is trying to do:

    Screenshot of tabs