Argthtjtr

I'm trying to copy a range that meets two criteria from two combo boxes on a user form.

ComboBox1 contains criteria 1, the Branch.

ComboBox2 contains criteria 2, the Quarter.

Column A needs to match the Branch criteria and Row 1 needs to match the Quarter criteria.

I cannot get my code to work properly. It only copies data from column 2 and is not checking the entire row for the Quarters criteria.

For example, if I select the Pearl branch and quarter Q1 the code should copy "apple" and "8".

Here is the code:

Private Sub CommandButton1_Click()



Dim LastRow As Long, i As Long, ws2 As Worksheet



With Worksheets("Sheet1")

    LastRow = .Range("A" & .Rows.Count).End(xlUp).Row



    For i = 2 To LastRow



        If .Cells(i, 1) = ComboBox1 And .Cells(1, 2) = ComboBox2 Then

            With Worksheets("Sheet4")

                .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0) = _

                     Worksheets("Sheet1").Cells(i, 2).Value

            End With

        End If



    Next i

End With

Unload Me



End Sub

The main problem with your code is that though you are correctly looping through the rows, you aren't looping through the columns.

Adding an inner loop would solve this. However, a better solution is to use the worksheet function MATCH() to find the matching row, and loop through the columns instead:

Private Sub CommandButton1_Click()



Dim LastColumn As Long

Dim i As Long



With Worksheets("Sheet1")

    LastColumn = .Cells(1, Columns.Count).End(xlToLeft).Column

    Dim lngMatchingRow As Long

    lngMatchingRow = Excel.WorksheetFunction.Match(ComboBox1.Value, .Range("A:A"), 0)



    For i = 2 To LastColumn



        If .Cells(1, i).Value2 = ComboBox2.Value Then

            With Worksheets("Sheet4")

                .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0) = _

                     Worksheets("Sheet1").Cells(lngMatchingRow, i).Value2

            End With

        End If



    Next i

End With

Unload Me



End Sub

Note that I've taken the liberty of modifying some other parts of the code to follow best practices:

• Variables should be declared one per line


• Variables should be declared as close to first usage as possible


• 
  .Value2 show always be used in preference to .Value when pulling data from a spreadsheet


• Instead of relying on default properties, they should be explicitly specified, e.g. ComboBox1.Value instead of ComboBox1
  

Now, if I were writing the code from scratch and if the Quarters were guaranteed to be grouped together, I would dispense with the column loop as well.

Instead, I would use MATCH() and COUNTIF() to find the column limits and copy the data all at once:

Private Sub CommandButton1_Click()

        Dim ƒ As Excel.WorksheetFunction: Set ƒ = Excel.WorksheetFunction



  With Worksheets("Sheet1")

    Dim lngMatchingRow As Long

    lngMatchingRow = ƒ.Match(ComboBox1.Value, .Range("A:A"), 0)

    Dim lngStartCol As Long

    lngStartCol = ƒ.Match(ComboBox2.Value, .Range("1:1"), 0)

    Dim lngColCount As Long

    lngColCount = ƒ.CountIf(.Range("1:1"), "Q1")



    Worksheets("Sheet4").Cells(Rows.Count, "A").End(xlUp).Offset(1).Resize(lngColCount) _

    = ƒ.Transpose(.Cells(lngMatchingRow, lngStartCol).Resize(1, lngColCount).Value2)



  End With

  Unload Me



End Sub