Where am I going wrong? Cells reference to a range appears to havestopped working!

[Mark Stephens]

Hi,

This one is really puzzling me as it seems pretty basic but obviously (unless VBA in excel 2013 has stopped supporting it which is unlikely) 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.

I thought that maybe I had gotten the reference notation wrong so I tried the following to test it:

ActiveWorkbook.Sheets(1).Range(Cells(1, 1), Cells(27, 11)).Select

Lo and behold that wont work either, which suggests that's the problem.

However I have looked up the notation to check, and even pasted an example from Microsoft into my code to test and that wont work either, which suggests it may be something else but I am at a complete loss to know what that something else might be. Can anyone either see anything wrong with the code above or if not suggest what is wrong/why it isn't working?

Any help much appreciated, it's almost embarrassing to be asking such a simple thing but I've tried everything I can to fix it and it has held me up for long enough so I thought someone on the user group may be able to help me.

Thanks in advance, Mark

 

[Cimjet]

Hi Mark
I'm no VBA expert and I'm using XL2003 but your script is okay. Close that workbook.
Open a new Workbook and try it again.
Sub test() ' This should select the range(array)
Dim MyVar As Variant
MyVar = ActiveWorkbook.Sheets(1).Range(Cells(1, 1), Cells(27, 11)).Select
End Sub
or
Sub test()
Dim MyVar As Variant ' This will copy your array
MyVar = ActiveWorkbook.Sheets(1).Range(Cells(1, 1), Cells(27, 11))
Range("M1:W27") = MyVar
End Sub

I never copied this way before !!! but it works.
The problem could be what you are trying to do with the variable.
It's Easter Sunday morning and I'm leaving but I'm sure someone else will be able to help if you don't find the problem first.
You've got one wheel stock in the mud and going in circle.
Close everything and clear your mind.
It does wonders
Happy Easter
John

 

[joeu2004]

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.

 

[joeu2004]

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.

To that end, it might also be useful to know what kind of module contains
the code fragment: worksheet object or regular module.

You might get to a worksheet object by right-clicking on a worksheet tab in
Excel and clicking on View Code. You might create a regular module by
clicking on Insert, then Module in VBA.

The difference is: in a worksheet object, Cells(1,1) is equivalent to
Me.Cells(1,1), where Me refers to the worksheet containing the code, not
necessarily ActiveSheet.

In contrast, in a regular module, Cells(1,1) is equivalent to
ActiveSheet.Cells(1,1). This is the case even if the procedure in the
regular module is called from a procedure in a worksheet object.

If you have any doubts, one of the following might be useful for determining
exactly what Cells(1,1) refers to:

MsgBox Cells(1,1).Address(external:=True)

Debug.Print Cells(1,1).Address(external:=True)

The output for the latter can found in the Immediate Window (press ctrl+G).

 

[GS]

In addition to joeu2004's advice, I assume you are aware the resulting
array containing the specified range data is 2D (1 To 27, 1 To 11) and
so your code accesses it accordingly!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[Mark Stephens]

Hi Mark

I'm no VBA expert and I'm using XL2003 but your script is okay. Close that workbook.

Open a new Workbook and try it again.

Sub test() ' This should select the range(array)

Dim MyVar As Variant

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

End Sub

or

Sub test()

Dim MyVar As Variant ' This will copy your array

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

Range("M1:W27") = MyVar

End Sub



I never copied this way before !!! but it works.

The problem could be what you are trying to do with the variable.

It's Easter Sunday morning and I'm leaving but I'm sure someone else willbe able to help if you don't find the problem first.

You've got one wheel stock in the mud and going in circle.

Close everything and clear your mind.

It does wonders

Happy Easter

John

Hi guys, thanks for all your inputs, apologies I had to run shortly after posting this so never got chance to follow up and in the end I 'spit out thedummy' and sued the code I had before that was working (I got a bee in my bonnet about it and was trying to replace 12 lines that worked with 2 linesthat turned out not to. I will check it out, out of courtesy (and I might even learn something!) but I should learn to leave well alone when something is working as my goal is not to become the world's most knowledgeable excel VBA programmer nor to write the world's most elegant and compact code, Ishould learn that saving a nanosecond (literally) of time in some code that works perfectly well is more a signal I have psychological problems thanit is that I need o solve a problem, so in short, thanks to all and apologies to all as always I really appreciate everyone's time and concern and input.

Kind regards, Mark