I have bits and pieces of a VBA macro I'm trying to finish and I'm trying to tie it all together. I'm a novice at this and could really use some help finishing it so it all works smoothly.
I currently have a working VBA macro to take a range of values in row2, and transpose and paste it vertically as many times as the value in B2. This works great already(see attached spreadsheet).
From here I'm attempting to take these transposed vertical ranges, and use VBA recursively to spit out the combinations(permutations?) of the ranges the first macro came up with. I have working code that a friend had given to me, but I'm having difficulty tailoring it to my needs to be able to adjust it based on the variables I use for the first (working) macro. For example, in the spreadsheet linked below, I'd like the macro to select A5:A9, B5:B9, C5:C9, D5,D9 individually, and make every combination of values from these variables, and past that into G4.
Here's my code so far:
Sub CombinationSample() 'this is what I'm struggling with!! Dim i As Long, j As Long, k As Long, l As Long Dim CountComb As Long, lastrow As Long CountComb = 0: lastrow = 4 For i = 1 To 5: For j = 1 To 5 ' This is set to a fixed 4 months with 5 rows of data below. How do I adjust this to match my variables in my TransPaste sub? To take into account the total nonblank cells in row 2 as well as as many variables as the # in B1? For k = 1 To 5: For l = 1 To 5 Range("G" & lastrow).Value = Range("A" & i).Value & "/" & _ Range("B" & j).Value & "/" & _ Range("C" & k).Value & "/" & _ Range("D" & l).Value ' How do I avoid selecting the entire row of A, and instead select underneath each Month# as the start of each range to find the combination ranges? lastrow = lastrow + 1 CountComb = CountComb + 1 Next: Next Next: Next Range("G1").Value = CountComb End Sub
Then, once this range is pasted in G4, I wanted to select this range, and text to columns delimit by "/" somehow thru VBA (as it's a pain in the ass to keep doing this manually) if possible. Then select this whole range that was just delimited, copy, and paste into the "Analysis" tab starting in cell A1.
I was hoping someone could at least point me in the right direction with a recursive algorithm. I'd like to make it dynamic enough to take varying numbers of columns and rows that my working macro already uses. I'd be really appreciative for any help at all!