In message
..com of Thu, 23 Feb 2012 07:05:06 in microsoft.public.excel.programming
, merjet writes
1. A Range is a class *within* another class, Worksheet. You can use
Union to combine two Ranges, both from the same Worksheet, but you
can't make one Range from two different Worksheets.
Thank you for that perception. It NOW makes sense to me.
The output of Union is a Range.
Therefore that must fit the criteria for a Range.
i.e. It must only refer to one sheet/

2. Since Prices is a Range, you should have used Set Prices = ...
You're right. I find Set quite unnatural, and regularly fall over my
failure to use it. I do not understand why Prices = Range("B2", "D4")
does not give a compilation error. Instead it gives a "Run-time error
'91': Object variable or With block variable not set".
Dim foo2 As Long: foo2 = "Hello, World!" also does not give a
compilation error. I guess VBA authors decided to economise on
compilation checks. The logic is that you don't check because you can't
always check. I feel better for composing those thoughts. ;)


3. I think you will have to settle for creating an array that is not a
Range, e.g. as follows.

Dim dArr(1 To 3, 1 To 6) As Double
Dim iRow As Integer
Dim iCol As Integer
For iRow = 1 To 3
For iCol = 1 To 3
dArr(iRow, iCol) = Sheets("A-D").Cells(1 + iRow, 1 +
iCol).Value
Next iCol
For iCol = 4 To 6
dArr(iRow, iCol) = Sheets("E-K").Cells(1 + iRow, iCol -
2).Value
Next iCol
Next iRow

For this I changed your X's to 0's. If you want to use X's, declare
dArr as Variant.


The problem with that useful example is that it makes many accesses to
the sheet. I was going to ask how I could find the dimensions of a
variant array after filling it.
[Why is Set not needed in the code below?]

Dim PricesA_D As Variant
PricesA_D = Sheets("A-D").Range(
"B2",
Sheets("A-D").Cells.SpecialCells(xlCellTypeLastCell).Address )

After running that code, In the Immediate Window, I showed:
?ubound(PricesA_D)
623
?ubound(PricesA_D,1)
623
?lbound(PricesA_D)
1
?ubound(PricesA_D,2)
151
?lbound(PricesA_D,2)
1

and in the Locals Window, I showed
PricesA_D Variant/Variant(1 to 623, 1 to 151)
and
PricesA_D(1) Variant(1 to 151)
....
PricesA_D(623) Variant(1 to 151)

I now have enough information to code my problem as I wish to do.
Thank you very much to merjet for help.
--
Walter Briscoe