VBA Excel Cell Combination Script 1004 Error

Question

This script should be combining 2 sets of four columns each of cells and printing each combination on the adjacent cells. I have had this working for 2 sets of three cells, but now I am getting a

1004 Application Defined or Object Defined erorr

It highlights:

Set out_1 = Range("I1", Range("L1").Offset(UBound(c1) * UBound(c2) * UBound(c3) * UBound(c7)))

I am not really sure how to fix this, can anyone help?

Sub Combinations()

Dim c1() As Variant
Dim c2() As Variant
Dim c3() As Variant
Dim c4() As Variant
Dim c5() As Variant
Dim c6() As Variant
Dim c7() As Variant
Dim c8() As Variant
Dim out1() As Variant
Dim out2() As Variant
Dim j, k, l, m, n As Long

Dim col1 As Range
Dim col2 As Range
Dim col3 As Range
Dim col4 As Range
Dim col5 As Range
Dim col6 As Range
Dim col7 As Range
Dim col8 As Range
Dim out_1 As Range
Dim out_2 As Range

Set col1 = Range("A1", Range("A1").End(xlDown))
Set col2 = Range("C1", Range("C1").End(xlDown))
Set col3 = Range("E1", Range("E1").End(xlDown))
Set col4 = Range("B1", Range("B1").End(xlDown))
Set col5 = Range("D1", Range("D1").End(xlDown))
Set col6 = Range("F1", Range("F1").End(xlDown))
Set col7 = Range("G1", Range("G1").End(xlDown))
Set col8 = Range("H1", Range("H1").End(xlDown))

c1 = col1
c2 = col2
c3 = col3
c4 = col4
c5 = col5
c6 = col6
c7 = col7
c8 = col8

Set out_1 = Range("I1", Range("L1").Offset(UBound(c1) * UBound(c2) * UBound(c3) * UBound(c7)))
Set out_2 = Range("M1", Range("P1").Offset(UBound(c4) * UBound(c5) * UBound(c6) * UBound(c8)))
out1 = out_1
out2 = out_2

j = 1
k = 1
l = 1
m = 1
n = 1

Do While j <= UBound(c1)
    Do While k <= UBound(c2)
        Do While l <= UBound(c3)
            Do While m <= UBound(c7)
                out1(n, 1) = c1(j, 1)
                out1(n, 2) = c2(k, 1)
                out1(n, 3) = c3(l, 1)
                out1(n, 4) = c7(m, 1)
                n = n + 1
                m = m + 1
            Loop
            m = m
            l = l + 1
        Loop
        l = 1
        k = k + 1
    Loop
    k = 1
    j = j + 1
Loop

j = 1
k = 1
l = 1
m = 1
n = 1


Do While j <= UBound(c4)
    Do While k <= UBound(c5)
        Do While l <= UBound(c6)
            Do While m <= UBound(c8)
                out1(n, 1) = c1(j, 1)
                out1(n, 2) = c2(k, 1)
                out1(n, 3) = c3(l, 1)
                out1(n, 4) = c8(m, 1)
                n = n + 1
                m = m + 1
            Loop
            m = m
            l = l + 1
        Loop
        l = 1
        k = k + 1
    Loop
    k = 1
    j = j + 1
Loop

out_1.Value = out1
out_2.Value = out2
End Sub

You might shorten that a bit by creating one two-dimensional array instead of 10 one dimensional arrays. Here's a good site to use for working code needing review: Code Review. — Brian
– Brian, Commented Aug 23, 2016 at 13:39
Are you sure that none of the columns are empty, thus making the Ubound over 1 million? This would cause the offset to be larger than the number of rows allowed on an excel sheet. Or for that matter that the multiplication of all the Ubounds do not exceed the number of rows allowed? — Scott Craner
– Scott Craner, Commented Aug 23, 2016 at 13:42
I would recommend debug.printing all your UBound(cN)'s to see what the values are. — Tim
– Tim, Commented Aug 23, 2016 at 13:44
If your Ubounds are more than 32 then it will exceed the allowed number of columns. Are you sure you do not want to add the ubounds instead? — Scott Craner
– Scott Craner, Commented Aug 23, 2016 at 13:47

user3598756 · Accepted Answer · 2016-08-23 13:50:15Z

1

Consider @ScottCraner's comment.

Furthermore even if you're sure that all columns have at least one non empty cell, if this is in row 1 then the xlDown approach would make Ubound() return 1 million!

in this case replace:

Set col1 = Range("A1", Range("A1").End(xlDown))
Set col2 = Range("C1", Range("C1").End(xlDown))
...

with :

Set col1 = Range("A1", Cells(Rows.Count, 1).End(xlUp))
Set col2 = Range("C1", Cells(Rows.Count, 3).End(xlUp)) '<--keep numeric column index in "Cells(Rows.Count,...)" synchronized with string column index in Range("...1")
...

answered Aug 23, 2016 at 13:50

user3598756

29.5k4 gold badges20 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Scott Craner Over a year ago

The second comment needs to be addressed. If the four columns multiplied together is greater than 1048576 then it will also cause the error. This only takes 32 rows to cause this. I think the answer should be to add the ubounds and not multiply them.

user3598756 Over a year ago

@ScottCraner: the sum over a product is actually a possible part of the answer combined with the xlDown vs xlUp approach. But it's impossible to dig out of OP's description the real goal. Let's wait for his feedback to both your comment and my answer

Collectives™ on Stack Overflow

VBA Excel Cell Combination Script 1004 Error

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related