I have an excel sheet with two columns, each with ten values, as shown in the diagram below. The 10 values from columns A and B are combined in a drop-down menu in columns E and F. I want a drop-down menu in column D, "Result," to display 100 distinct possible permutations of the data. I attempted to write a macro, but I kept getting lost. EDIT: I've added the error I'm experiencing. I sincerely appreciate any assistance. An illustration of what to expect (remember columns E and F are dropdowns)

Below is the macro I have tried:

```Sub Combination()
Dim arr1 As Variant
Dim arr2 As Variant
Dim i As Long, j As Long, k As Long
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Sheet1")
arr1 = ws.Range("E1", ws.Range("E" & ws.Rows.Count).End(xlUp).Row).Value
arr2 = ws.Range("F1", ws.Range("F" & ws.Rows.Count).End(xlUp).Row).Value
ws.Range("D1").Value = "Result"
k = 1
For i = LBound(arr1, 1) To UBound(arr1, 1)
For j = LBound(arr2, 1) To UBound(arr2, 1)
ws.Range("D" & k + 1).Value = arr1(i, 1) & ", " & arr2(j, 1)
k = k + 1
If k = 101 Then Exit For
Next j
If k = 101 Then Exit For
Next i
End Sub
```

Debugger shows an error in this line of code:

```arr1 = ws.Range("E1", ws.Range("E" & ws.Rows.Count).End(xlUp).Row).Value
```

How else am I supposed to read the values in the drop-down?

Feb 13, 2023 in Others 749 views

## 1 answer to this question.

This work can be completed utilising dynamic spreadsheet functions rather than necessarily a VBA solution (if you have a relatively recent version of Excel). I believe that people use VBA far too frequently when they should first explore the possibilities of spreadsheet functions.

1. Calculate the permutations

Put this formula in cell H2:

`=LET(a,A2:A11,b,B2:B10,na,ROWS(a),nb,ROWS(b),s,SEQUENCE(na*nb,,0),INDEX(a,1+(INT(s/nb))) & "," & INDEX(b,1+MOD(s,nb)))`

2. Set the Data Validation:

• 63,720 points

## Get random value in the range of plus/minus 10% of a cell value in Excel

Why not just use RANDBETWEEN(B2*0.9, B2*1.1) if ...READ MORE

## Excel-How can I get the address of a cell instead of a value?

There are various difficulties in this. Which ...READ MORE

## Excel VBA search based on cell values into folders and sub-folders to get the file path and data

This will create a listing of all ...READ MORE

## Sum the total of a column in excel and paste the sum to a different workbook

Get Column Sum From Closed Workbook Option Explicit Sub ...READ MORE

## Can a worksheet ActiveX ComboBox work on a Mac?

ActiveX is an outdated Windows technology that ...READ MORE

## Strikethrough in Excel VBA

When I enter the second date in ...READ MORE

## Excel VBA- Creation of a New datablock with criteria

To insert the dropdown, you can go ...READ MORE

## I want to compare two Excel files and highlight the differences with VBA

The workbook doesn't have the UsedRange property ...READ MORE