Consulting

Results 1 to 15 of 15

Thread: Subscript out of range problem

  1. #1
    VBAX Regular
    Joined
    Apr 2019
    Location
    London
    Posts
    25
    Location

    Subscript out of range problem

    Can you see why the following code generates a "subscript out of range" error at the last line?
    The Sheets(Sh) convention works elsewhere. The Rng1A.copy seems to have taken place ok.


    Dim Rng1A, Rng2A, Rng1I, Rng2I As Range   'range blocks to copy
    Dim N as Long, D As Date, Sh As String
    N = ActiveWorkbook.Sheets("NAVIGATION").Range("G12")   ' value is correct
            D = ActiveWorkbook.Sheets("NAVIGATION").Range("H13")    ' value is correct
            Sh = "Sheet" & N                                                                ' value is correct
    With ActiveWorkbook.Sheets("INV ACC")
                Set Rng1A = Range("D15:I214") 
                Set Rng2A = Range("M15:W214") 
            End With
    With ActiveWorkbook.Sheets("INV INPUT")
                Set Rng1I = Range("H15:L214")  
                Set Rng2I = Range("S15:Y214")  
            End With
    Rng1A.Copy
                Workbooks("Centris Periods.xlsm").Sheets(Sh).Range("D15:I214").PasteSpecial xlPasteValuesAndNumberFormats
    Thank very much.
    Last edited by Aussiebear; 04-18-2019 at 12:16 AM. Reason: wrapped submitted code in tags

  2. #2
    Knowledge Base Approver VBAX Guru
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    4,506
    Is the workbook Centris Periods.xlsm open?
    Is Centris Periods.xlsm the name of the workbook exactly right?
    Is what's in Sh (a sheet name)? Is it spelt exactly correctly (no missing/extra leading/trailing spaces)?
    p45cal - - - - -This is my signature, it appears after all my posts. Below is not directed at anyone in particular - so don't take offence! - (You might guess why it's there though)
    If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  3. #3
    VBAX Regular
    Joined
    Apr 2019
    Location
    London
    Posts
    25
    Location
    Yes, yes, yes, yes

  4. #4
    VBAX Tutor david000's Avatar
    Joined
    Mar 2007
    Location
    Chicago
    Posts
    274
    Location
    Workbooks("Centris Periods.xlsm")
    Have you tried it without the file extension?
    "To a man with a hammer everything looks like a nail." - Mark Twain

  5. #5
    VBAX Mentor 大灰狼1976's Avatar
    Joined
    Dec 2018
    Location
    SuZhou China
    Posts
    377
    Location
    Hi Kerry!
    Although the value of variable Rng1A may be wrong, it does not affect the running of the program.
    There are only a few reasons for errors
    1. target workbook was not opened
    2. Wrong target workbook name
    3. the value of variable Sh is wrong

    Why not upload attachments?


    --Okami

  6. #6
    Knowledge Base Approver VBAX Guru
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    4,506
    It's 99% certain to be one of the things mentioned by Okami/myself.

    Try adding these three lines directly after your .Copy line (but before the PasteSpecial line):
    Workbooks("Centris Periods.xlsm").Activate 'errors if you have the workbook name wrong or the book isn't open.
    ActiveWorkbook.Sheets(Sh).Activate 'errors if the sheet name is wrong.
    Range("D15:I214").Select 'this will fail if the code is in a sheet's code module and that sheet isn't Workbooks("Centris Periods.xlsm").Sheets(Sh)
    These three lines are just temporary for debugging, you should remove them later.


    As an aside, both your With…End With blocks are currently redundant; you're missing some dots:
    With ActiveWorkbook.Sheets("INV ACC")
      Set Rng1A =  .Range("D15:I214")
      Set Rng2A =  .Range("M15:W214")
    End With
    With ActiveWorkbook.Sheets("INV INPUT")
      Set Rng1I =  .Range("H15:L214")
      Set Rng2I =  .Range("S15:Y214")
    End With
    Last edited by p45cal; 04-18-2019 at 03:53 AM.
    p45cal - - - - -This is my signature, it appears after all my posts. Below is not directed at anyone in particular - so don't take offence! - (You might guess why it's there though)
    If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  7. #7
    Knowledge Base Approver VBAX Guru
    Joined
    Apr 2012
    Posts
    4,461
    Why not simply using ?

    ActiveWorkbook.Sheets("INV ACC")Range("D15:I214").copy Workbooks("Centris Periods.xlsm").Sheets("sheet" & [navigation!G12]).Range("D15")
    or

    Workbooks("Centris Periods.xlsm").Sheets("sheet" &  [navigation!G12]).Range("D15:I214")=ActiveWorkbook.Sheets("INV ACC")Range("D15:I214").value

  8. #8
    VBAX Regular
    Joined
    Apr 2019
    Location
    London
    Posts
    25
    Location
    Quote Originally Posted by p45cal View Post
    It's 99% certain to be one of the things mentioned by Okami/myself.

    Try adding these three lines directly after your .Copy line (but before the PasteSpecial line):
    Workbooks("Centris Periods.xlsm").Activate 'errors if you have the workbook name wrong or the book isn't open.
    ActiveWorkbook.Sheets(Sh).Activate 'errors if the sheet name is wrong.
    Range("D15:I214").Select 'this will fail if the code is in a sheet's code module and that sheet isn't Workbooks("Centris Periods.xlsm").Sheets(Sh)
    These three lines are just temporary for debugging, you should remove them later.


    As an aside, both your With…End With blocks are currently redundant; you're missing some dots:
    With ActiveWorkbook.Sheets("INV ACC")
      Set Rng1A =  .Range("D15:I214")
      Set Rng2A =  .Range("M15:W214")
    End With
    With ActiveWorkbook.Sheets("INV INPUT")
      Set Rng1I =  .Range("H15:L214")
      Set Rng2I =  .Range("S15:Y214")
    End With
    Do you mean write it as:

    Rng1I = Workbooks("Centris").Sheets("INV INPUT").Range("H15:L214")

  9. #9
    Knowledge Base Approver VBAX Guru
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    4,506
    Quote Originally Posted by Kerry H View Post
    Do you mean write it as:

    Rng1I = Workbooks("Centris").Sheets("INV INPUT").Range("H15:L214")
    Most definitley NOT. For 2 reasons:
    1. Your current code is only missing 4 dots (as shown in red).
    2. Your line's missing the Set word.
    and possibly a 3rd: Is the workbook called Centris, or Centris Periods?

    How did you get on with adding those 3 temporary debugging lines?
    p45cal - - - - -This is my signature, it appears after all my posts. Below is not directed at anyone in particular - so don't take offence! - (You might guess why it's there though)
    If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  10. #10
    VBAX Regular
    Joined
    Apr 2019
    Location
    London
    Posts
    25
    Location
    I was referring to your comment about redundancy, which I didn't understand.
    I did add the dots to the code I had previously.

    Alternatively,does this work?

    Set Rng1I = Workbooks("Centris").Sheets("INV INPUT").Range("H15:L214")

    If so, are 2 of these statements better than the "With ...End" With structure?
    BTW I didn't need the error tracking code. But I've saved it for another time!
    When is the "Set" instruction used, generally?

    Thanks foryour help.
    Last edited by Kerry H; 04-18-2019 at 08:16 AM. Reason: left out sometrhing

  11. #11
    VBAX Regular
    Joined
    Apr 2019
    Location
    London
    Posts
    25
    Location
    Quote Originally Posted by snb View Post
    Why not simply using ?

    ActiveWorkbook.Sheets("INV ACC")Range("D15:I214").copy Workbooks("Centris Periods.xlsm").Sheets("sheet" & [navigation!G12]).Range("D15")
    or

    Workbooks("Centris Periods.xlsm").Sheets("sheet" &  [navigation!G12]).Range("D15:I214")=ActiveWorkbook.Sheets("INV ACC")Range("D15:I214").value
    I guess I'm trying to keep things straight in my mind, as a newbie to VBA. Never sure at this point in my experience what will work and what not. Thankis for the suggestions.
    Kerry

  12. #12
    Knowledge Base Approver VBAX Guru
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    4,506
    Quote Originally Posted by Kerry H View Post
    I was referring to your comment about redundancy, which I didn't understand.
    I did add the dots to the code I had previously.
    Good, that's all you needed to do to stop the redundancy; you used With ~~~, but never used the dot notation that goes with it, so the With…End With wouldn't have worked.

    Quote Originally Posted by Kerry H View Post
    Alternatively,does this work?

    Set Rng1I = Workbooks("Centris").Sheets("INV INPUT").Range("H15:L214")
    It should do except you've got to get the workbook's name correct; is it Centris or Centris Periods?
    It's little inaccuracies like these that lead to Subscript out of range errors.

    Quote Originally Posted by Kerry H View Post
    If so, are 2 of these statements better than the "With ...End" With structure?
    They save you time when writing the code in several ways; you don't have to keep retyping the references, just use a dot. If you want to change something in the references you only need do it it one place. I've heard that it's less resource hungry because the references don't have to be interpreted every time.
    Quote Originally Posted by Kerry H View Post
    BTW I didn't need the error tracking code. But I've saved it for another time!
    WHAT??!! The subject of this thread is Subscript out of range problem. Those 3 lines of code were designed to find which of the subscripts was out of range… nothing else. Have you solved the problem without telling us?!
    Quote Originally Posted by Kerry H View Post
    When is the "Set" instruction used, generally?
    Something along the lines of Set being needed when the variable is an object variable as opposed to being a plain old variable. The variable in this case is very probably an object, because it looks like you intended them to be ranges with the line:
    Dim Rng1A, Rng2A, Rng1I, Rng2I As Range   'range blocks to copy
    which only Dims Rng2I as a range. You probably intended:
    Dim Rng1A As Range, Rng2A As Range, Rng1I As Range, Rng2I As Range   'range blocks to copy
    p45cal - - - - -This is my signature, it appears after all my posts. Below is not directed at anyone in particular - so don't take offence! - (You might guess why it's there though)
    If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  13. #13
    VBAX Regular
    Joined
    Apr 2019
    Location
    London
    Posts
    25
    Location
    Quote Originally Posted by p45cal View Post
    Good, that's all you needed to do to stop the redundancy; you used With ~~~, but never used the dot notation that goes with it, so the With…End With wouldn't have worked.

    It should do except you've got to get the workbook's name correct; is it Centris or Centris Periods?
    It's little inaccuracies like these that lead to Subscript out of range errors.
    The names of the WBs are correct. As I indicated earlier I added the dots.

    They save you time when writing the code in several ways; you don't have to keep retyping the references, just use a dot. If you want to change something in the references you only need do it it one place. I've heard that it's less resource hungry because the references don't have to be interpreted every time.
    Will adopt this approach.

    WHAT??!! The subject of this thread is Subscript out of range problem. Those 3 lines of code were designed to find which of the subscripts was out of range… nothing else. Have you solved the problem without telling us?!
    As part of earlier post I thanked everyone for their replies and indicated that the problem had been solved. I had the wrong WB active when running the Sub. That message doesn't seem to have "taken". Perhaps part of a reply was erased before I sent it.

    Something along the lines of Set being needed when the variable is an object variable as opposed to being a plain old variable. The variable in this case is very probably an object, because it looks like you intended them to be ranges with the line:
    Dim Rng1A, Rng2A, Rng1I, Rng2I As Range   'range blocks to copy
    which only Dims Rng2I as a range. You probably intended:
    Dim Rng1A As Range, Rng2A As Range, Rng1I As Range, Rng2I As Range   'range blocks to copy
    I have read that "Dim x, y, z as Long" works. Not with Ranges?

    Thanks very much for your help, p45cal, as always. It is very much appreciated.
    Thanks also to all who replied. Your suggestions will be useful in the future.

  14. #14
    VBAX Wizard
    Joined
    Apr 2007
    Posts
    6,503
    Location
    I have read that "Dim x, y, z as Long" works. Not with Ranges?

    For ALL Dim-ed variables unless you explicitly state "As ……." the default is to type them as Variant

    It will compile and probably run, but it might not be what you wanted

    In the above, "x" and "y" as Variants, and "z" is a Long



    Dim Rng1A, Rng2A, Rng1I, Rng2I As Range 'range blocks to copy
    Likewise. Rng1A, Rng2A, and Rng1I are Variants, while Rng2I is a Range

    You lose the Intellisense if you do not type objects (especially) as what the really are


    All object variables need 'Set'
    Paul

    ------------------------------------------------------------------------------------------------------------------------
    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s)
    (multiple files can be selected while holding Ctrl key) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  15. #15
    VBAX Regular
    Joined
    Apr 2019
    Location
    London
    Posts
    25
    Location
    Thanks Paul.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •