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

Discussion in 'Excel Programming' started by Mark Stephens, Apr 20, 2014.

  1. 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
    Mark Stephens, Apr 20, 2014
    1. Advertisements

  2. Mark Stephens

    Cimjet Guest

    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
    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
    Cimjet, Apr 20, 2014
    1. Advertisements

  3. Mark Stephens

    joeu2004 Guest

    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.

    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, Apr 20, 2014
  4. Mark Stephens

    joeu2004 Guest

    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).
    joeu2004, Apr 20, 2014
  5. Mark Stephens

    GS Guest

    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!


    Free usenet access at http://www.eternal-september.org
    Classic VB Users Regroup!
    GS, Apr 20, 2014
  6. 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
    Mark Stephens, Apr 23, 2014
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.