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

[Carey123]

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

 

[Andrew Poulsom]

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]

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]

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]

You could use

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

 

[Carey123]

Andrew,

Thank you that worked.

 

[MasterMiyagi]

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?

 

[Andrew Poulsom]

Make sure that A3 and B3 are blank.