Results 1 to 11 of 11

Thread: Cannot debug 'Subscription out of range error'

  1. #1

    Thread Starter
    New Member
    Join Date
    Apr 2021
    Posts
    5

    Cannot debug 'Subscription out of range error'

    Hi I am writing a VBA code for getting data from different workbooks and combining them in a single one. However, I am getting the 'Subscription out of range error' in the bold line of code below. I have properly defined the variables and the files are also present the folder. Can someone please help me?

    Sub RalphieReactor()
    Dim filenames As Variant
    Dim i, j, k, m, nw As Integer
    Dim a() As Variant
    Dim r As Range
    Dim tWb, aWb As Workbook
    Set tWb = ThisWorkbook
    InputBox ("enter files which you want to import data from")
    filenames = Application.GetOpenFilename(Filefilter:="Excel Filter(*.csv),*csv", Title:="Open File(s)", MultiSelect:=True)
    nw = UBound(filenames)
    ReDim a(nw, 4) As Variant
    For i = 1 To nw
    Workbooks.Open filenames(i)
    Workbooks(2).Activate
    Set aWb = ActiveWorkbook
    If i = 1 Then
    Set r = Application.InputBox("mention the range", , , , , , , 8)
    End If
    For j = 1 To 4
    a(i, j) = aWb.Sheets("Sheet1").r.Cells(j, 1)
    Next j
    Workbooks(2).Close
    Next i
    For k = 1 To nw
    For m = 1 To 4
    tWb.Sheets("Data").Cells(k, m) = a(k, m)
    Next m
    Next k
    End Sub

  2. #2
    PowerPoster Arnoutdv's Avatar
    Join Date
    Oct 2013
    Posts
    4,385

    Re: Cannot debug 'Subscription out of range error'

    You first ask for a range, which is stored in r (a range variable)
    Then I think the code should be the following
    Code:
    a(i, j) = aWb.Sheets("Sheet1").Range(r).Cells(j, 1)

  3. #3

    Thread Starter
    New Member
    Join Date
    Apr 2021
    Posts
    5

    Re: Cannot debug 'Subscription out of range error'

    Quote Originally Posted by Arnoutdv View Post
    You first ask for a range, which is stored in r (a range variable)
    Then I think the code should be the following
    Code:
    a(i, j) = aWb.Sheets("Sheet1").Range(r).Cells(j, 1)
    But the line Set r = Application.InputBox("mention the range", , , , , , , 8) already stores a range in the variable r, doesn't it?
    The type 8 in the application.inputbox mentioned above is for taking inputs in form of range. I verified it in the locals window too.

  4. #4
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    2,424

    Re: Cannot debug 'Subscription out of range error'

    What are the values for i and j when your code throws the error?
    And how many Columns and Rows does that Range have?
    If you have a Range, say A3:B4, you have 2 columns and 2 rows, but your Loop runs to 4

    Nota Bene:
    Clean up your Dim's!
    i,j,k and m are variants, not integers
    tWb is a variant, not a workbook
    Why dim "filenames" as variant instead of array of string? Dim filenames() As String. Or is it because your getting it back from the FileOpen-Dialogue? Never used that one, so might be wrong here
    One System to rule them all, One IDE to find them,
    One Code to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    For health reasons i try to avoid reading unformatted Code

  5. #5
    PowerPoster Arnoutdv's Avatar
    Join Date
    Oct 2013
    Posts
    4,385

    Re: Cannot debug 'Subscription out of range error'

    But the line Set r = Application.InputBox("mention the range", , , , , , , 8) already stores a range in the variable r, doesn't it?
    It stores the range text specification in a variable.
    This should be a string variable, that's what inputbox returns
    Then you use the range specification in the Range object as a parameter

    Like in "Sheet1" in: Sheets("Sheet1")

    Code:
    Dim sSheetName As String
    sSheetName = "Sheet1"
    
    With aWb.Sheets("Sheet1")
    ' ..
    End With

  6. #6
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    2,424

    Re: Cannot debug 'Subscription out of range error'

    Quote Originally Posted by Arnoutdv View Post
    It stores the range text specification in a variable.
    This should be a string variable, that's what inputbox returns
    Then you use the range specification in the Range object as a parameter
    Eh, no!
    The OP correctly states using Type 8 in the Inputbox
    Look here: https://docs.microsoft.com/en-us/off...ation.inputbox
    One System to rule them all, One IDE to find them,
    One Code to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    For health reasons i try to avoid reading unformatted Code

  7. #7
    PowerPoster Arnoutdv's Avatar
    Join Date
    Oct 2013
    Posts
    4,385

    Re: Cannot debug 'Subscription out of range error'

    Ah clear, I'm not familiar with VBA specific functions and methods.

    But even then you can not concatenate variables with references.
    Or is this also possible in VBA?
    Code:
    Dim r As Range
    
    aWb.Sheets("Sheet1").r.Cells(j, 1)

  8. #8
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    2,424

    Re: Cannot debug 'Subscription out of range error'

    Yes, that's possible to "interpose" the Range-Object there.
    The Cells following after it are then refering to the Range, not the Sheet (Think as in "offset").
    Cells(1,1) would be the Top-Left cell of the Range, independent where that Range is on the Sheet
    One System to rule them all, One IDE to find them,
    One Code to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    For health reasons i try to avoid reading unformatted Code

  9. #9
    PowerPoster wqweto's Avatar
    Join Date
    May 2011
    Posts
    2,967

    Re: Cannot debug 'Subscription out of range error'

    Quote Originally Posted by Zvoni View Post
    Yes, that's possible to "interpose" the Range-Object there.
    No, that cannot be possible!

    When you have this variable "r"

    Dim r As Range
    Set r = Application.InputBox("mention the range", , , , , , , 8)


    . . . but then this named range "r"

    a(i, j) = aWb.Sheets("Sheet1").r.Cells(j, 1)


    . . . has nothing to do with the variable "r".

    You might have a named range "r" which is defined outside this code though.

    cheers,
    </wqw>

  10. #10
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    2,424

    Re: Cannot debug 'Subscription out of range error'

    Yes, you're right.
    Got confused with it. I rarely use Ranges in my VBA
    One System to rule them all, One IDE to find them,
    One Code to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    For health reasons i try to avoid reading unformatted Code

  11. #11
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,313

    Re: Cannot debug 'Subscription out of range error'

    For j = 1 To 4
    a(i, j) = aWb.Sheets("Sheet1").r.Cells(j, 1)
    Next j
    as r is returned as a fully qualified range you can not specify it on some sheet anyway
    unless the sheet is also entered in the inputbox, range r will always be on the active sheet when the inputbox is shown

    should be just
    Code:
    For j = 1 To 4
        a(i, j) = r.Cells(j, 1)
    Next j
    if i = 1 then
    each time you open a workbook the r range is no longer valid as it was in the first workbook (fully qualified range), now closed
    as you have no control over the range that is entered by the user, in the input box, you should check that the range entered matches at least the 4 rows required

    i would do the code more like
    Code:
    For i = 1 To nw
    set aWB =Workbooks.Open(filenames(i))
      If i = 1 Then
        Set rs = Application.InputBox("mention the range", , , , , , , 8).address  '  where rs is a string
      End If
      For j = 1 To 4
        a(i, j) = aWb.Sheets("Sheet1").range(rs)
      Next j
      aWB.Close
    Next i
    Last edited by westconn1; Apr 16th, 2021 at 06:10 AM.
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

Posting Permissions

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



Click Here to Expand Forum to Full Width