-
Apr 15th, 2021, 04:14 AM
#1
Thread Starter
New Member
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
-
Apr 15th, 2021, 04:58 AM
#2
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)
-
Apr 15th, 2021, 06:32 AM
#3
Thread Starter
New Member
Re: Cannot debug 'Subscription out of range error'
Originally Posted by Arnoutdv
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.
-
Apr 15th, 2021, 07:35 AM
#4
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
Last edited by Zvoni; Tomorrow at 31:69 PM.
----------------------------------------------------------------------------------------
One System to rule them all, One Code to find them,
One IDE 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.
---------------------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad
-
Apr 15th, 2021, 07:49 AM
#5
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
-
Apr 15th, 2021, 08:04 AM
#6
Re: Cannot debug 'Subscription out of range error'
Originally Posted by Arnoutdv
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
Last edited by Zvoni; Tomorrow at 31:69 PM.
----------------------------------------------------------------------------------------
One System to rule them all, One Code to find them,
One IDE 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.
---------------------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad
-
Apr 15th, 2021, 08:24 AM
#7
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)
-
Apr 15th, 2021, 09:06 AM
#8
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
Last edited by Zvoni; Tomorrow at 31:69 PM.
----------------------------------------------------------------------------------------
One System to rule them all, One Code to find them,
One IDE 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.
---------------------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad
-
Apr 15th, 2021, 09:29 AM
#9
Re: Cannot debug 'Subscription out of range error'
Originally Posted by Zvoni
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>
-
Apr 16th, 2021, 01:01 AM
#10
Re: Cannot debug 'Subscription out of range error'
Yes, you're right.
Got confused with it. I rarely use Ranges in my VBA
Last edited by Zvoni; Tomorrow at 31:69 PM.
----------------------------------------------------------------------------------------
One System to rule them all, One Code to find them,
One IDE 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.
---------------------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad
-
Apr 16th, 2021, 05:50 AM
#11
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
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|