Selection to Array and Array to Sheet
The slow down and focus was because the DataOut data you originally
posted did not really describe what you wanted. Check it out.
The following deposits the DataOut data immediately to the right of the
selected data.
Alan Beban
Sub MakeScenarios2()
Dim DataVals As Variant
Dim r As Integer
Dim c As Integer
Dim DataOut() As Double
DataVals = Selection.Value
ReDim DataOut(1 To UBound(DataVals, 1) ^ 2, 1 To 2)
For r = 1 To UBound(DataVals, 1)
For c = 1 To UBound(DataVals, 1)
DataOut(c + UBound(DataVals, 1) * (r - 1), 1) = DataVals(r, 1)
DataOut(c + UBound(DataVals, 1) * (r - 1), 2) = DataVals(c, 2)
Next
Next
Selection.Offset(0, 2).Resize(UBound(DataVals, 1) ^ 2, 2).Value = _
DataOut
End Sub
qpg wrote:
Slow down, focus, and take a break are always good advise but I think
that is what I want. The code below generates what I want in the debug
window but I cant seem to figure out how to fill a new array with that
so I can dump it to a different location on the sheet.
Sub MakeScenerios2()
Dim DataVals As Variant
Dim r As Integer
Dim c As Integer
Dim DataOut() As Double
Dim valx As Double
Dim valy As Double
DataVals = Selection.Value
For r = 1 To UBound(DataVals, 1)
For c = 1 To UBound(DataVals, 1)
valx = DataVals(r, 1)
valy = DataVals(c, 2)
Debug.Print valx & "," & valy
Next
Next
End Sub
for each value in one collum I want to generate all the pairs in the
other collum. Does not really matter which is which.
Any other pointers.
Thanks.
Alan Beban wrote:
Slow down and focus! The "New Array" below is not at all what you really
want.
Alan Beban
qpg wrote:
I guess I did not explain myself quite right. What I am trying to do is
take a 2 dimensional array and expand it like the example below, and
then write it out on a different part of the sheet. Basically I need a
list that is all the permutations of the original value pairs.
Original selected array
ColA, ColB
1, 111
2, 222
3, 333
New Array would be this:
ColA, CalB
1, 111
2, 111
3, 111
2, 111
2, 222
2, 333
3,111
3, 222
3, 333
Any additional help would be great. Thanks.
Alan Beban wrote:
qpg wrote:
I am looking for and example of how to create a multidimential array (2
dimientions) from a selection and then how to write that array data
(with some modifications) to another location on the same sheet.
any help would be appreciated.
Thank you
Range("C3:E5").Select
arr = Selection
'do whatever
Range("G1:I3").Value = arr
I don't know why you're selecting. You get the same result with
arr=Range("C3:E5")
'do whatever
Range("G1:I3").Value = arr
Alan Beban
|