samedi 31 août 2019

VBA: Sort unique values under conditions (Working between different sheets)

I have a workbook with the following two Sheets. In Sheet1 there is the data.

| Order |     Date | Status | Country |
|-------|---------:|-------:|---------|
| 78    | 19-04-19 | OK     | FR      |
| 33    | 19-04-19 | OK     | GB      |
| 198   | 19-04-19 | CL     | GB      |
| 43    | 19-04-19 | CL     | CH      |
| 29    | 19-04-19 | CL     | CN      |
| 12    | 20-04-19 | CL     | GB      |
| 169   | 20-04-19 | OK     | GB      |
| 95    | 20-04-19 | OK     | GB      |
| 54    | 20-04-19 | OK     | FR      |
| 31    | 20-04-19 | OK     | GB      |

What I am trying to achieve is to sort 'Country' unique values under the condition that Status=OK. So far, with a bit of help I have managed to write the code for the case that there is no condition.

Sub SortUniqueValues1()

Dim i As Variant
Dim j As Variant

With Sheets("Sheet1")
j = Application.Transpose(.Range("H2", .Range("H" & Rows.Count).End(xlUp)))
End With

    With CreateObject("Scripting.Dictionary")
        For Each i In j
            .Item(i) = i
        Next
     Cells(3, 1).Resize(.Count) = Application.Transpose(.Keys)
    End With

Range("A3:M100000").Sort Key1:=Range("A3"), Order1:=xlAscending, Header:=xlNo

End Sub

I am trying to find out the following:

  1. How can I integrate into the code the Status Condition?
  2. Is there any way to create some sort of dropdown menu that will turn on/off conditions?
  3. How can I convert A3:M100000 to A3 until the last value in column M?

Thank you in advance!

Aucun commentaire:

Enregistrer un commentaire