Thanks again. That helps a lot.

Michael

Alan Beban wrote:
I just reread your last post and noted a possible misimpression that
might confuse you. It's not "c+the number of rows" that is multiplied by
(r-1); it's only the number of rows that's multiplied by (r-1). I.e.,
it's not

(c + UBound(DataVals, 1)) * (r - 1)

It's

c + (UBound(DataVals, 1)) * (r - 1))

Alan Beban

Alan Beban wrote:
Sure. For simplicity of discussion, let's consider the simple 3-row
illustration you originally posted (although I wrote the code to
accommodate the general case).

In your double loop, while r=1 c loops thru 1,2,3; then with r=2 c loops
thru 1,2,3; ditto for when r=3. While this is happening, you want the
array row index to be 1 thru 9 (the square of the number of rows, which
is the number of possible pairs). That's what the expression you're
asking about does. I.e.,

when r=1 and c=1, expression=1
when r=1 and c=2, expression=2
when r=1 and c=3, expression=3
when r=2 and c=1, expression=4
when r=2 and c=2, expression=5
when r=2 and c=3, expression=6
when r=3 and c=1, expression=7
when r=3 and c=2, expression=8
when r=3 and c=3, expression=9

You should check to make sure it works with a selection of any number of
rows; that was my intent but I only ran it on the 3-row selection.

Alan Beban


qpg wrote:
This works great. Im not sure I understand what the "r-1" does in the
two asignment statements and would like to undertand it not just use
it.

DataOut(c + UBound(DataVals, 1) * (r - 1), 1) = DataVals(r, 1)
DataOut(c + UBound(DataVals, 1) * (r - 1), 2) = DataVals(c, 2)

also (c + UBound(DataVals, 1) gets you c+ number or rows, right? Im not
sure I understand this either. Why c+ number of rows times row index -
1 (r-1)?

If you have a minute for some additonal explaination it would be
appreciated.

Thanks again.



Alan Beban wrote:
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