Printable View
I have an array called "Result" that contains a variable number of columns and 252 rows. Assuming there are 2 columns I'd like to use Transpose to place the array into column CJ where the array columns are are concatenated. For example if Result(0,0) is 66 and Result(1,0) = 52 then CJ1 should be 6652. The concatenation of Result(0,1) is 66 and Result(1,1) should follow that, etc.
What about 3 (or more) columns?
Result(0,0)=66
Result(1,0)=52
Result(2,0)=18
should then be "665218" in CJ1?
Yes.
Questions:
1) are we talking VBA or Excel functions?
2) the values in that array are all longs?
3) 1st dimension is rows, 2nd columns?
4) any ready to use bogus sample-data?
I need VBA.
The array looks like this (truncated).
Attachment 187168
The values in the array are the result of generating all the combinations of several numbers like 66 52 59 17 15 70 38 30 24 16 14 taken 2 at a time. In some situations I need to take them 3, 4, 5 etc at a time.
It also would be very useful later on to be able to calculate the "54" value.
Quick and dirty
Code:Sub main()
'4 Rows, 10 Columns
Dim Result(0 To 3, 0 To 9) As Long
Dim t() As String
Dim i As Long
Dim j As Long
Dim s As String
'Init some Bogus-Data
For i = 0 To 9
Result(0, i) = 66
'Random Integers between 10 and 99
Result(1, i) = Int(10 + Rnd * 90)
Result(2, i) = Int(10 + Rnd * 90)
Result(3, i) = Int(10 + Rnd * 90)
Next
ReDim t(0 To UBound(Result, 1)) 'Get Ubound of Rows
For j = 0 To UBound(Result, 2) 'Get UBound of Columns
For i = 0 To UBound(t)
t(i) = CStr(Result(i, j))
Next
s = Join(t, "") 'Assign "s" to your target-cell
Debug.Print s
Next
End Sub
Thanks.