Subscript Out of Range (But It's Not)

brand_nubian

New Member
Joined
Apr 27, 2015
Messages
4
Hello all,

I have referred to previous posts on this issue and have seen people suggest (sensibly) that if you're getting a 'Subscript out of range' message while trying to call a sheet, it's because either the workbook you're referencing isn't active, or because it doesn't contain a sheet with the name matching what you're trying to call. I'm running into an issue where I'm cycling through a list of cell values that correspond to the names of the sheets that I want to call (I actually populated the list by cycling through sheets and assigning their names as values to a range of cells on my starting tab), and while the program is successfully able to switch back and forth between workbooks and call any tab whose name contains both letters and numbers as characters, it returns the 'Subscript out of range' error for tabs whose names contain just numbers.

I've tried a simple macro to test whether or not those cells containing just number values actually correspond to the sheet names - they do, as the program that doesn't toggle between workbooks, successfully calls the sheets with just numbers in the name. Any guidance would be greatly appreciated! The code is below:


Code:
Sub ReportingUpdate()
Dim n, i, p, q, numbermonths, enddata, startcolumn As Integer
Dim filenombre, filenombre2, filenombre3, filenombre4, currentfilenombre, sheetnombre, policynumber, xirr_address, startdata_address, startdates_address, startirrvalues_address, startdates_address_column, startirrvalues_address_column As String
Dim xirrvalue As Double


Application.ScreenUpdating = False


numbermonths = Sheets("Input").Range("NumberMonths").Value
currentfilenombre = Sheets("Input").Range("CurrentFileNombre").Value
filenombre = Sheets("Input").Range("FileNombre").Value
filenombre2 = Sheets("Input").Range("FileNombre").Offset(1, 0).Value
filenombre3 = Sheets("Input").Range("DestinationFileNombre").Value
filenombre4 = Sheets("Input").Range("DestinationFileNombre").Offset(1, 0).Value
xirr_address = Sheets("Input").Range("XIRRAddress").Value
startdata_address = Sheets("Input").Range("StartDataAddress").Value
startdates_address = Sheets("Input").Range("StartDatesAddress").Value
startirrvalues_address = Sheets("Input").Range("StartIRRValuesAddress").Value
startdates_address_column = Sheets("Input").Range("StartDatesAddressColumn").Value
startirrvalues_address_column = Sheets("Input").Range("StartIRRValuesAddressColumn").Value


i = Sheets("Input").Range("NumberQuarters").Value


Workbooks.Open (filenombre)
Workbooks.Open (filenombre3), UpdateLinks:=True




Workbooks(currentfilenombre).Activate


For Each c In Sheets("Input").Range("PolicyList")
If c.Value <> "" Then






policynumber = c.Value




Workbooks(filenombre2).Sheets("Input").Activate
Range("Policy").Value = policynumber




Workbooks(currentfilenombre).Sheets(policynumber).Activate


'Adjust formulas in the annualized return sheet related to dates, XIRR calc, and Inflows/Outflows below


Range(startdates_address).End(xlDown).Copy Destination:=Sheets(policynumber).Range(Cells(Sheets(policynumber).Range(startdates_address).End(xlDown).Row + 1, Sheets(policynumber).Range(startdates_address).Column), Cells(Sheets(policynumber).Range(startdates_address).End(xlDown).Row + numbermonths, Sheets(policynumber).Range(startdates_address).Column))
Range(startirrvalues_address).End(xlDown).Copy Destination:=Range(startirrvalues_address).End(xlDown).Offset(numbermonths, 0)
Range(startirrvalues_address).End(xlDown).Offset(-1, 0).Copy Destination:=Range(Cells(Range(startirrvalues_address).End(xlDown).Row, Range(startirrvalues_address).Column), Cells(Range(startirrvalues_address).End(xlDown).Offset(numbermonths - 1, 0).Row, Range(startirrvalues_address).Column))
If Range(startirrvalues_address).Value = 0 Then
Range(xirr_address).Formula = "=XIRR(" & startirrvalues_address_column & Range(startirrvalues_address).Row + 1 & ":" & startirrvalues_address_column & Range(startdates_address).End(xlDown).Row & "," & startdates_address_column & Range(startirrvalues_address).Row + 1 & ":" & startdates_address_column & Range(startdates_address).End(xlDown).Row & ")"
End If
If Range(startirrvalues_address).Value <> 0 Then
Range(xirr_address).Formula = "=XIRR(" & startirrvalues_address_column & Range(startirrvalues_address).Row & ":" & startirrvalues_address_column & Range(startdates_address).End(xlDown).Row & "," & startdates_address_column & Range(startirrvalues_address).Row & ":" & startdates_address_column & Range(startdates_address).End(xlDown).Row & ")"
End If


p = 0


Do While p < i


'This is finding the right spot to pour in results from the data summary workbook


sheetnombre = Sheets("Input").Range("StartUpdate").Offset(p, 0).Value
Workbooks(currentfilenombre).Sheets(policynumber).Activate


enddata = Range(startdata_address).End(xlDown).Row
startcolumn = Range(startdata_address).Column
Range(startdata_address).End(xlDown).Copy Destination:=Range(Cells(enddata + 1, startcolumn), Cells(enddata + 3, startcolumn))




q = 0
Do While q < 3
'This is actually pulling in the appropriate data from the data summary workbook, month-by-month


Cells(enddata + q + 1, startcolumn + 1).Value = Workbooks(filenombre2).Sheets(sheetnombre).Cells(Workbooks(filenombre2).Sheets(sheetnombre).Range("U1").Value, 3 + (6 * q)).Value
Cells(enddata + q + 1, startcolumn + 2).Value = Workbooks(filenombre2).Sheets(sheetnombre).Cells(Workbooks(filenombre2).Sheets(sheetnombre).Range("U1").Value, 4 + (6 * q)).Value
Cells(enddata + q + 1, startcolumn + 3).Value = Workbooks(filenombre2).Sheets(sheetnombre).Cells(Workbooks(filenombre2).Sheets(sheetnombre).Range("U1").Value, 5 + (6 * q)).Value
Cells(enddata + q + 1, startcolumn + 4).Value = Workbooks(filenombre2).Sheets(sheetnombre).Cells(Workbooks(filenombre2).Sheets(sheetnombre).Range("U1").Value, 6 + (6 * q)).Value
Cells(enddata + q + 1, startcolumn + 5).Value = Workbooks(filenombre2).Sheets(sheetnombre).Cells(Workbooks(filenombre2).Sheets(sheetnombre).Range("U1").Value, 7 + (6 * q)).Value
Cells(enddata + q + 1, startcolumn + 7).Value = Workbooks(filenombre2).Sheets(sheetnombre).Cells(Workbooks(filenombre2).Sheets(sheetnombre).Range("U1").Value, 8 + (6 * q)).Value


q = q + 1
Loop


p = p + 1
Loop


'This is sending the XIRR calculated value to the sheet in which the presentation is contained


xirrvalue = Sheets(policynumber).Range(xirr_address).Value


Workbooks(filenombre4).Sheets("Presentation").Activate


Range("Policy").Value = policynumber
Cells(Range("StartPolicies").Row + Range("PolicyLocator").Value - 1, Range("SinceInception").Column).Value = xirrvalue


End If
Next

End Sub
 
Last edited by a moderator:

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,079
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
There is a big difference between Sheets(1) and Sheets("1"). The former refers to the first sheet in the workbook, the latter to a sheet named "1". Since your sheetname variable is a variant, you need to coerce it to a string:

Code:
Workbooks(currentfilenombre).Sheets(CStr(policynumber)).Activate
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
Which line is having the error?

You're using variables in your sheet references, for 1 example policynumber.
You assign that value like
policynumber = c.Value
Then you call the sheet like Sheets(policynumber)

While that's all and good, but if the policynumber variable is a NUMBER, say 10
Then that would be looking for the 10th sheet in the book, not necessarily a sheet literally named "10"

You have to make sure that variable is represented as a TEXT String, not a NUMBER.

And I think the problem lies here.
Dim filenombre, filenombre2, filenombre3, filenombre4, currentfilenombre, sheetnombre, policynumber, xirr_address, startdata_address, startdates_address, startirrvalues_address, startdates_address_column, startirrvalues_address_column As String

Logic would tell us that every variable in that line is being Dimmed as a String
But in fact it's NOT.
VBA requires EACH and EVERY variable to be explicitly declared, otherwise it becomes a Variant by default.

So this
Dim x, y, z As String
Only the variable z becomes a String, x and y are Variants.

You would have to do that like this
Dim x As String, y As String, z As String


So, since your variable policynumber has been Dimmed as a Variant
And the c.Value is a Numeric Value, then policynumber is in fact a Numeric Variable, not a String.
Sheets(policynumber)
is referring to the INDEX Number of your sheets, not the Tab name.


Hope that helps
 
Last edited:

brand_nubian

New Member
Joined
Apr 27, 2015
Messages
4
I want to add 2 points:

1) While I think this might be solved by using indexed positions to call sheets rather than calling them based on their names, for the sake of those who go on to use this workbook, I want them to be able to simply modify the list of tab names to be included in the program and not have to worry about worksheet order to avoid any errors.

2) The error message pops up when the following line of code is run:
Workbooks(currentfilenombre).Sheets(policynumber).Activate
 

brand_nubian

New Member
Joined
Apr 27, 2015
Messages
4
Thanks very much for the quick response! I thought the problem might have been caused by something along these lines and the Cstr() function, plus calling policynumber as a string, has solved this issue.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,122,694
Messages
5,597,571
Members
414,157
Latest member
MrBJBones

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top