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:
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: