For each loop with data validation lists

[Waimea]

I have cross posted this on excelforums.com. https://www.excelforum.com/excel-pr...each-loop-count-the-number-of-occurences.html


I found this thread and I think this is what I need and if someone could help me adapt it.

https://www.mrexcel.com/forum/excel...-vba-loop.html?highlight=loop+data+validation

I would like to use a for each loop with the condition that there are 4 drop downs data validation lists.

For each loop I would like to count the number of symbols ✓ ✕ that occur in a specific range in 1 worksheet and write that number in a different worksheet.

I would like to iterate though all possible combinations of my 4 drop down lists.

Any help is very much aprecciated.

 

[Waimea]

<code style="margin: 0px; padding: 0px; font-style: inherit; font-weight: inherit; line-height: 12px;">Sub LoopThroughList()
Dim Dropdown1, Dropdown2, Dropdown3, Dropdown4 As String
Dim Range1, Range2, Range3, Range4 As Range
Dim option1, option2, option3, option4 As Range


Dim Counter As Long


Counter = 1


Set Range1 = Evaluate(Range("B3").Validation.Formula1)
Set Range2 = Evaluate(Range("H3").Validation.Formula1)
Set Range3 = Evaluate(Range("B9").Validation.Formula1)
Set Range4 = Evaluate(Range("H9").Validation.Formula1)


For Each option1 In Range1
    For Each option2 In Range2
        For Each option3 In Range3
            For Each option4 In Range4


                Sheets(2).Cells(Counter, 1) = option1
               Sheets(2).Cells(Counter, 2) = option2
                Sheets(2).Cells(Counter, 3) = option3
                Sheets(2).Cells(Counter, 4) = option4
                Counter = Counter + 1
                Debug.Print option1, option2
                Debug.Print option1, option2, option3, option4
            Next option4
       Next option3
    Next option2
Next option1




End Sub



</code>

How can I improve this code?

 

[Waimea]

This code gives me a type mismatch because I am using INDEX and MATCH for the dependant drop down data validation lists.

Any suggestions on how to continue with code snippet?

 

[Waimea]

From this thread https://stackoverflow.com/questions/51170356/for-each-loop-with-data-validation-lists I have the following code:

Sub ExtractDataValidationList(Source As Range, Optional TargetWorkSheet As Worksheet)
    Dim cell As Range, rValidation As Range
    Dim list As Object, item As Variant, values As Variant
    On Error Resume Next
    Set rValidation = Source.SpecialCells(xlCellTypeAllValidation)
    On Error GoTo 0


    If rValidation Is Nothing Then
        MsgBox "No Data Validation Found"
    Else
        Set list = CreateObject("System.Collections.ArrayList")
        For Each cell In rValidation
            On Error Resume Next
            values = Range(cell.Validation.Formula1).Value
            If Err.Number <> 0 Then values = Split(cell.Validation.Formula1, ",")
            On Error GoTo 0


            For Each item In values
                If Not list.Contains(item) Then list.Add item
            Next
        Next


        If list.Count = 0 Then
            MsgBox "No Items in Data Validation Formula1"
        Else
            list.Sort
            If TargetWorkSheet Is Nothing Then Set TargetWorkSheet = Worksheets.Add
            TargetWorkSheet.Range("A1").Resize(list.Count).Value = WorksheetFunction.Transpose(list.ToArray)
        End If
    End If


End Sub

I am uncertain on how to call the procedure?