Subscript Out of Range (But It's Not)

[brand_nubian]

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:

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

 

[RoryA]

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:

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

 

[Jonmo1]

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

 

[brand_nubian]

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]

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.