subscript out of range

kanieli · Aug 26th 2005

hello
when I run the code below I receive the error message 9: subscript is out of range.
I'm trying to work with several workbooks using a macro in the personal.xls, and I'm pretty sure that I must have made a mistake in the way I refer to the workbooks. The problem is I've spent so much time over it, that I don't know what to look for. Can anybody help me?

the code:

Code

Public Sub CreateSheets()
On Error GoTo METHOD_ERROR
Const FOREIGN_TEMPLATE_SHEET_NAME As String = "Foreign Subsidiary Code"
Const US_TEMPLATE_SHEET_NAME As String = "US Parent Code"
Const COPY_SHEET_NAME As String = "Foreign Subsidiary Code (2)"
Const COL_INDEX_CODE As Integer = 1
Const COL_INDEX_NAME As Integer = 2
Const COL_INDEX_DOMICILE As Integer = 3
Const ROW_INDEX_START As Integer = 3
Const ROW_INDEX_US As Integer = 2
Const RENAME_SHEET_NAME As String = "ForeignEntitySheet"
Dim nRowIndex As Integer
Dim sCurrentCode As String
Dim sCurrentName As String
Dim sCurrentDomicile As String
Dim sPreviousCode As String
Dim wksTemplate As Worksheet
Dim i As Integer
Dim sourcewb As Workbook
Dim currentwb As Workbook
Dim sourcews As Worksheet
Dim currentws As Worksheet
Const adjust As Integer = 1000
''BLOCK: Rename US Sheet
Set wksTemplate = Worksheets(US_TEMPLATE_SHEET_NAME)
wksTemplate.Name = Cells(ROW_INDEX_US, COL_INDEX_CODE)
'initial value of variables
Set sourcewb = Workbooks("step1+2.xls")
Set currentwb = ActiveWorkbook
''BLOCK: Create Foreign Sheets
Set wksTemplate = Worksheets(FOREIGN_TEMPLATE_SHEET_NAME)
sPreviousCode = FOREIGN_TEMPLATE_SHEET_NAME
nRowIndex = ROW_INDEX_START
Do
sCurrentCode = Cells(nRowIndex, COL_INDEX_CODE)
sCurrentName = Cells(nRowIndex, COL_INDEX_NAME)
sCurrentDomicile = Cells(nRowIndex, COL_INDEX_DOMICILE)
If sCurrentCode = vbNullString Then
Exit Do
Else
Worksheets(FOREIGN_TEMPLATE_SHEET_NAME).Copy after:=Worksheets(sPreviousCode)
Set wksTemplate = Worksheets(COPY_SHEET_NAME)
wksTemplate.Name = sCurrentCode
wksTemplate.Cells(3, 1) = sCurrentName
wksTemplate.Cells(3, 2) = sCurrentDomicile
For i = 2 To sourcewb.Sheets("Datasource").UsedRange.Columns.Count
For Each currentws In currentwb.Worksheets
If currentws.Name = sourcewb.Sheets("Datasource").Cells(12, i).Value Then
currentws.Cells(4, 2).Value = sourcewb.Sheets("Datasource").Cells(18, i).Value / adjust
currentws.Cells(5, 2).Value = sourcewb.Sheets("Datasource").Cells(46, i).Value / adjust - sourcewb.Sheets("Datasource").Cells(17, i).Value / adjust
currentws.Cells(6, 2).Value = sourcewb.Sheets("Datasource").Cells(46, i).Value / adjust
currentws.Cells(7, 2).Value = sourcewb.Sheets End If
Next
Next i
End If
nRowIndex = nRowIndex + 1
sPreviousCode = sCurrentCode
Loop
Set wksTemplate = Nothing
ActiveWorkbook.SaveAs Filename:="C:\Documents and Settings\All Users\Desktop\XBS\ImportFinancials.xls", FileFormat _
:=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:= _
False, CreateBackup:=False
''BLOCK: Cleanup
Application.DisplayAlerts = False
Worksheets(FOREIGN_TEMPLATE_SHEET_NAME).Delete
Worksheets(RENAME_SHEET_NAME).Delete
Exit Sub
METHOD_ERROR:
MsgBox "Failed: " & Err.Number & " : " & Err.Description
End Sub

Display More

thanks

K.

Anonymous · Aug 26th 2005

Re: subscript out of range

Where in the code does the error occur? (Please, this will help us know where to look.)

kanieli · Aug 26th 2005

Re: subscript out of range

Hi thomach,
The code doesn't start the for loop in the second block, the block "create foreign sheets". Everything is ok until there.

the problem is that when the error appears, i don't even receive the offer of debugging. That's why I think this might be due to the fact that it thinks a workbook isn't open, despite the fact that they are open. I have checked the names, and they are right...

jkc72001 · Aug 26th 2005

Re: subscript out of range

Since you have the "On Error Goto METHOD_ERROR" statement, you probably cant see exactly where the code is stopping. Comment out that line and any other On Error statements in functions that may call this function.

Once you do that, the code will enter the debugger mode when it hits the error. Should help you isolate where the problem is.

Alternatively, you can step through your code in the debugger, but I find that annoying if you have loops.

Share