Hello -
I'm still a novice to VBA programming and I have written a piece of code (see below) that - in theory does the following:
- Takes multiple ranges of integer numbers from a worksheet and puts them a single array
- Then, takes the resulting array and ingests it into Excel's Histogram function. The output is 2-diemension histogram array in pareto format.
Hopefully this example will better illustrate what I am trying to do.
My integer numbers are contained in a spreadsheet that looks like this:
1 3 4
5 7 6
4 8 9
1 4 6
2 3 8
8 1 6
Each number is in its own cell, s,o for example, the first 3-number data set, 1 3 4, is housed in cells A1, B1 and C1 respectively.
Using the above 3-number data sets, I would like to make 4-way "pseudo-permutation" combinations of all 3-number data sets shown above. For example, this is what would be contained in one of the resulting "pseudo-permutated" combination arrays:
1 3 4 5 6 7 4 8 9 1 4 6 (Note: I added additional spaces between 3-number data set shown in my examples to help the reader better visualize the four, 3-number data sets that would contained one array. Of course the actual array would not have such spacing.)
I say "pseudo-permutation" because I want my VBA loops to permit duplicate 3-number data sets to occur in any array but that each combination be unique. So to clarify, this array would be ok,
1 3 4 1 3 4 5 7 6 4 8 9
but this array
1 3 4 5 7 6 816 4 8 9 would not be ok, if this combination
1 3 4 5 6 7 4 8 9 816 had been generated earlier. This is because they both contain the same four 3-number data sets only arranged differently.
Since each "pseudo-permutated" combination array is comprised of four, 3-number data sets - and each 3-number data set is a range of Excel cells - I went online to see how I could merge multiple excel ranges into one array. I found this site (excel vba how to copy the value of multiple non-contiguous ranges into an array - Stack Overflow) and tried to adapt the code to my situation. When I try and run the macro, it results in the following error
Run-time error '1004':
Method 'Range' of object'_Worksheet'failed
and the debugger takes me to the commented area of my code (see below code). Can I use the loop indexes to denote the ranges within my spreadsheet? Yes? No? Can someone tell me what is not working with my attempt to merge multiple Excel ranges into a single array?
Finally, using Excel's record macro option, I generated a pareto histogram that I later cut/paste/adapted into the below code. I'd appreciate it if someone could tell me if I have edited this correctly. A couple of notes on this:
• summaryTempArray() is the input array (AKA "Input Range")into the histogram function. It is the same is the same array that contains the four 3-number data sets.
• ("$T$1:$AC$1") points to the spreadsheet values containing the "Bin Range" needed to form the histogram
• Is there a way to get a 2-D array out of the histogram function showing the bun # and corresponding frequencies?
Thanks in advance for any help you can give me. I really appreciate you taking the time to see what I am not seeing with my code.
Sub Four3numberDataSetPermutation ()
Dim summaryTempArray() As Variant
Dim RangeArray(1, 10) As Long
Dim i As Long
Dim j As Long
Dim k As Long
Dim l As Long
Dim m As Long
Dim n As Long
Dim iStartRow
Dim jStartRow
Dim kStartRow
Dim mStartRow
Dim NumberOfColumns
Dim NumberOfRows
iStartRow = 1
jStartRow = 1
kStartRow = 1
mStartRow = 1
NumberOfColumns = 3
NumberOfRows = 120
For i = iStartRow To NumberOfRows Step 1
For j = jStartRow To NumberOfRows Step 1
For k = kStartRow To NumberOfRows Step 1
For m = mStartRow To NumberOfRows Step 1
With Sheet12
' the next line of code is where my macro seems to fail
ReDim summaryTempArray(1 To .Range("iStartRow:NumberOfColumns,jStartRow:NumberOfColumns,kStartRow:NumberOfColumns, mStartRow:NumberOfColumns").Areas.Count)
For n = 1 To .Range("iStartRow:NumberOfColumns,jStartRow:NumberOfColumns,kStartRow:NumberOfColumns, mStartRow:NumberOfColumns").Areas.Count
summaryTempArray
= .Range("iStartRow:NumberOfColumns,jStartRow:NumberOfColumns,kStartRow:NumberOfColumns, mStartRow:NumberOfColumns").Areas
Next n
End With
' This is where the histogram is generated
Application.Run "ATPVBAEN.XLA!Histogram", summaryTempArray(), _
"", .Range("$T$1:$AC$1"), True, False, False, False
Next m
mStartRow = mStartRow + 1
Next k
kStartRow = kStartRow + 1
Next j
jStartRow = jStartRow + 1
Next i
MsgBox ("Done")
End Sub
I'm still a novice to VBA programming and I have written a piece of code (see below) that - in theory does the following:
- Takes multiple ranges of integer numbers from a worksheet and puts them a single array
- Then, takes the resulting array and ingests it into Excel's Histogram function. The output is 2-diemension histogram array in pareto format.
Hopefully this example will better illustrate what I am trying to do.
My integer numbers are contained in a spreadsheet that looks like this:
1 3 4
5 7 6
4 8 9
1 4 6
2 3 8
8 1 6
Each number is in its own cell, s,o for example, the first 3-number data set, 1 3 4, is housed in cells A1, B1 and C1 respectively.
Using the above 3-number data sets, I would like to make 4-way "pseudo-permutation" combinations of all 3-number data sets shown above. For example, this is what would be contained in one of the resulting "pseudo-permutated" combination arrays:
1 3 4 5 6 7 4 8 9 1 4 6 (Note: I added additional spaces between 3-number data set shown in my examples to help the reader better visualize the four, 3-number data sets that would contained one array. Of course the actual array would not have such spacing.)
I say "pseudo-permutation" because I want my VBA loops to permit duplicate 3-number data sets to occur in any array but that each combination be unique. So to clarify, this array would be ok,
1 3 4 1 3 4 5 7 6 4 8 9
but this array
1 3 4 5 7 6 816 4 8 9 would not be ok, if this combination
1 3 4 5 6 7 4 8 9 816 had been generated earlier. This is because they both contain the same four 3-number data sets only arranged differently.
Since each "pseudo-permutated" combination array is comprised of four, 3-number data sets - and each 3-number data set is a range of Excel cells - I went online to see how I could merge multiple excel ranges into one array. I found this site (excel vba how to copy the value of multiple non-contiguous ranges into an array - Stack Overflow) and tried to adapt the code to my situation. When I try and run the macro, it results in the following error
Run-time error '1004':
Method 'Range' of object'_Worksheet'failed
and the debugger takes me to the commented area of my code (see below code). Can I use the loop indexes to denote the ranges within my spreadsheet? Yes? No? Can someone tell me what is not working with my attempt to merge multiple Excel ranges into a single array?
Finally, using Excel's record macro option, I generated a pareto histogram that I later cut/paste/adapted into the below code. I'd appreciate it if someone could tell me if I have edited this correctly. A couple of notes on this:
• summaryTempArray() is the input array (AKA "Input Range")into the histogram function. It is the same is the same array that contains the four 3-number data sets.
• ("$T$1:$AC$1") points to the spreadsheet values containing the "Bin Range" needed to form the histogram
• Is there a way to get a 2-D array out of the histogram function showing the bun # and corresponding frequencies?
Thanks in advance for any help you can give me. I really appreciate you taking the time to see what I am not seeing with my code.
Sub Four3numberDataSetPermutation ()
Dim summaryTempArray() As Variant
Dim RangeArray(1, 10) As Long
Dim i As Long
Dim j As Long
Dim k As Long
Dim l As Long
Dim m As Long
Dim n As Long
Dim iStartRow
Dim jStartRow
Dim kStartRow
Dim mStartRow
Dim NumberOfColumns
Dim NumberOfRows
iStartRow = 1
jStartRow = 1
kStartRow = 1
mStartRow = 1
NumberOfColumns = 3
NumberOfRows = 120
For i = iStartRow To NumberOfRows Step 1
For j = jStartRow To NumberOfRows Step 1
For k = kStartRow To NumberOfRows Step 1
For m = mStartRow To NumberOfRows Step 1
With Sheet12
' the next line of code is where my macro seems to fail
ReDim summaryTempArray(1 To .Range("iStartRow:NumberOfColumns,jStartRow:NumberOfColumns,kStartRow:NumberOfColumns, mStartRow:NumberOfColumns").Areas.Count)
For n = 1 To .Range("iStartRow:NumberOfColumns,jStartRow:NumberOfColumns,kStartRow:NumberOfColumns, mStartRow:NumberOfColumns").Areas.Count
summaryTempArray
= .Range("iStartRow:NumberOfColumns,jStartRow:NumberOfColumns,kStartRow:NumberOfColumns, mStartRow:NumberOfColumns").AreasNext n
End With
' This is where the histogram is generated
Application.Run "ATPVBAEN.XLA!Histogram", summaryTempArray(), _
"", .Range("$T$1:$AC$1"), True, False, False, False
Next m
mStartRow = mStartRow + 1
Next k
kStartRow = kStartRow + 1
Next j
jStartRow = jStartRow + 1
Next i
MsgBox ("Done")
End Sub
