"Mark Stephens" wrote:
This one is really puzzling me as it seems pretty basic but
obviously [...] I am doing something wrong.
I am actually trying to assign a range to an array like so:
Dim MyVar as variant
MyVar = ActiveWorkbook.Sheets(1).Range(Cells(1, 1), Cells(27, 11))
For some strange reason it will not do the allocation.

When you have an "inexplicable" problem, it is essential that you provide
code exactly as it is written in its original form and with sufficient
context, not overly excerpted with "equivalent" substitutions.

And it is essential that you provide actual error messages, not your
interpretation of the misbehavior.

I do not know what "it will not do the allocation" means (to you). If you
are getting an error message, what is it exactly? If there is no explicit
error, __precisely__ how is the code misbehaving? That is, what makes you
think it "will not do the allocation"?

Also, I notice that "as" and "variant" are not capitalized. So I presume
you did not copy-and-paste the code fragment, as you should. So I wonder if
the root cause of the problem is in the actual syntax of the code fragment,
lost in translation.

The prefix "ActiveWorkbook." is usually not necessary. But if it is, note
that Cells(1,1) and Cells(27,11) might refer to another workbook.
(Unlikely!)

Similarly, if the prefix "Sheets(1)." is necessary because that is not
ActiveSheet, note that Cells(1,1) and Cells(27,11) might refer to a
different worksheet. That does cause a runtime error (1004): "method Range
of _Worksheet failed". Perhaps you should write one of the following:

MyVar = Sheets(1).Range(Sheets(1).Cells(1, 1), Sheets(1).Cells(27, 11))

or (caveat: note the leading periods):

With Sheet(1)
MyVar = .Range(.Cells(1, 1), .Cells(27, 11))
End With

Some people believe the latter is more effient.

Finally, the constant references Cells(1,1) and Cells(27,11) are suspicious.
Why not simply write Sheets(1).Range("a1:k27")?

I wonder if Cells(1,1) and Cells(27,11) are written differently in the
actual code -- e.g. Cells(i,j) and Cells(m,n) -- and the root cause of the
problem is elsewhere; for example, i, j, m or n is not what you expect.

If none of these comments leads to a solution, please copy-and-paste the
offending code and relevant context, and please provide the actual error
message or precise description of the misbehavior.