# Run-time error '6" Overflow on Macro for Combining 2 List into all Permultations

#### Carey123

##### New Member
This macro should take two list and create every combination of the two list in a third column

a1 b1
a2 b2

should give the following list (not sure what order)

a1 b1
a2 b1
a2 b1
a2 b2
b1 a1
b1 a2
b2 a1
b2 a2

The excel macro gives a Run-time error '6" Overflow on this line "Set rngOutB = Range("D" & (rngL1.Rows.Count * rngL2.Rows.Count * 2))".

Any suggestions on how to fix it?

This is the macro:

Sub CreatePermutations()
Dim rngL1 As Range
Dim rngL2 As Range
Dim rngA As Range
Dim rngB As Range
Dim rngOutA As Range
Dim rngOutB As Range
Set rngL1 = Range("A1", Range("A1").End(xlDown))
Set rngL2 = Range("B1", Range("B1").End(xlDown))
Set rngOutA = Range("D1")
Set rngOutB = Range("D" & (rngL1.Rows.Count * rngL2.Rows.Count * 2))
For Each rngA In rngL1.Cells
For Each rngB In rngL2.Cells
rngOutA = rngA.Value * " " & rngB.Value
rngOutB = rngB.Value & " " & rngA.Value
Set rngOutA = rngOutA.Offset(1, 0)
Set rngOutB = rngOutB.Offset(-1, 0)
Next
Next
End Sub

### Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

#### Andrew Poulsom

##### MrExcel MVP
Welcome to the Board.

I don't get an overflow, but I had to change * to & in:

rngOutA = rngA.Value * " " & rngB.Value

Make sure that A3 and B3 are blank.

#### Jonmo1

##### MrExcel MVP
I would think that this expression
rngL1.Rows.Count * rngL2.Rows.Count * 2

results in a number that is HIGHER than the maximum number of rows available in a sheet (65536 in XL2003)

#### Carey123

##### New Member
Andrew - I will try that. The actual lists are going to be random length. But I will make sure the last column is blank.

jonmo1 - Do you know how to limit the number of rows to 65536 for the expression
rngL1.Rows.Count * rngL2.Rows.Count * 2

#### Jonmo1

##### MrExcel MVP
You could use

Application.MIN(Rows.Count, rngL1.Rows.Count * rngL2.Rows.Count * 2)

#### Carey123

##### New Member
Andrew,

Thank you that worked.

#### MasterMiyagi

##### New Member
Im having the exact same issue

But I never had the * and always had the & sign. But I am still getting the exact same run time error as the OP.

#### Andrew Poulsom

##### MrExcel MVP
Make sure that A3 and B3 are blank.

Replies
15
Views
414
Replies
4
Views
161
Replies
9
Views
189
Replies
0
Views
75
Replies
24
Views
436

1,185,966
Messages
5,955,053
Members
438,181
Latest member
viv1

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back