Macro to Print All Possible Combinations of 3 fields

[luke2442]

Hi All,

I've been trying to put together (read plagiarise!) a macro that will give me all the possible combinations of 3 columns of fields, which are "size", "colour", and "piping". Giving the results comma separated so i can split them with text to columns. This is what I have so far:

Sub com() 
Dim size As Range 
Dim colour As Range 
Dim piping As Range 

Dim combination As Range 
Dim counter 
Set combination = Worksheets(1).Range("D:D") 
counter = 1 
With Worksheets(1) 
    For Each size In .Range("A1", .Range("A" & .Rows.Count).End(xlUp)) 
        For Each colour In .Range("B1", .Range("B" & .Rows.Count).End(xlUp)) 
            For Each piping In .Range("C1", .Range("C" & .Rows.Count).End(x1Up)) 
                combination(counter) = size.Value & " , " & colour.Value & "," & piping.Value 
                counter = counter + 1 
             
         Next size 
        Next colour 
      Next piping 

End With 
End Sub

I get a "Invalid next control reference variable" error when I run it though....

Any Thoughts? Thanks!

 

[VoG]

Try this http://www.mrexcel.com/forum/showthread.php?t=379323

 

[luke2442]

Try this http://www.mrexcel.com/forum/showthread.php?t=379323

I really wouldn't know where to start to customise that for my fields... wanted something a little simpler that I can manipulate afterwards.

 

[VoG]

Then maybe like this

Sub com()
Dim size As Range
Dim colour As Range
Dim piping As Range

Dim counter As Long

counter = 1
With Worksheets(1)
    For Each size In .Range("A1", .Range("A" & .Rows.Count).End(xlUp))
        For Each colour In .Range("B1", .Range("B" & .Rows.Count).End(xlUp))
            For Each piping In .Range("C1", .Range("C" & .Rows.Count).End(xlUp))
                Range("D" & counter).Value = size.Value & " , " & colour.Value & "," & piping.Value
                counter = counter + 1
         Next size
        Next colour
      Next piping
End With
End Sub

 

[luke2442]

I get the same error message with that, unfortunately. it highlights the following

Next size

 

[shg]

There's a workbook that will do this with formulas at http://www.box.net/shared/ubd28tissi

 

[VoG]

Try

Sub com()
Dim size As Range
Dim colour As Range
Dim piping As Range

Dim counter As Long

counter = 1
With Worksheets(1)
    For Each size In .Range("A1", .Range("A" & .Rows.Count).End(xlUp))
        For Each colour In .Range("B1", .Range("B" & .Rows.Count).End(xlUp))
            For Each piping In .Range("C1", .Range("C" & .Rows.Count).End(xlUp))
                .Range("D" & counter).Value = size.Value & " , " & colour.Value & "," & piping.Value
                counter = counter + 1
         Next piping
        Next colour
      Next size
End With
End Sub

 

[luke2442]

WOOOHOO! thanks Vog. That's just saved me roughly 5 days of manual labour.

HERO