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

Carey123

New Member
Joined
Sep 9, 2007
Messages
3
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
Joined
Jul 21, 2002
Messages
73,092
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
Joined
Oct 12, 2006
Messages
44,061
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
Joined
Sep 9, 2007
Messages
3
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
Joined
Oct 12, 2006
Messages
44,061
You could use

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

MasterMiyagi

New Member
Joined
Jan 25, 2011
Messages
1
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.

Any suggestions please?
 

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top