-
Feb 14th, 2013, 08:20 AM
#1
Thread Starter
New Member
error 9 - subscript out of range problem
hi everyone,
it's my first approach to VBA, so please be patient :-)
But back to business.
The below code shows error 9 on line:
worksheets(idx).activate
Could you please have a look and advise how to solve this issue?
Code:
Public Function UpdateRiskFigures()
Dim idx As Integer
Dim startDate As Integer
Dim Dates() As Integer
Dim count As Integer
Dim Cpties() As String
Dim FGRNames() As String
Dim idy As Integer
ReDim Cpties(0 To count)
Do While Not IsEmpty(Range("FGROverview").Offset(0, count))
count = count + 1
Loop
count = count - 1
ReDim Cpties(0 To count) ' returns counterparties' column
For idy = 0 To count
Cpties(idy) = Range("FGROverview").Offset(0, idy)
Next idy
ReDim Dates(startDate To count)
count = count + 1
Dim FGRNames_Report As Range ' defines column C in report's tabs as a dynamic array
Set FGRNames_Report = Range("C21").Offset(count, 0)
count = count + 1
For idx = startDate To UBound(Dates) 'enters FX forward, IRS and MM figures into template table
Select Case IsNumeric(idx)
Case True
Worksheets(idx).Activate
count = count + 1
Select Case Worksheets(idx).Range(FGRNames_Report).Value
Case "Foreign Exchange Forward"
Do Until Range("C21").Offset(count, 0) = "Interest RateSwap" Or "Money Market"
If Range("C21").Offset(count, 0) = Worksheets("Overview").Range(Cpties(idy)) Then _
Worksheets("Overview").Range(Cpties(idy)).Offset(Dates(idx - 1), 0) = Range("C21").Offset(count, 1)
Loop
Case "Interest RateSwap"
Do Until Range("C21").Offset(count, 0) = "Money Market"
If Range("C21").Offset(count, 0) = Worksheets("Overview").Range(Cpties(idy)) Then _
Worksheets("Overview").Range(Cpties(idy)).Offset(Dates(idx), 0) = Range("C21").Offset(count, 1)
Loop
Case "Money Market"
Do Until Range("C21").Offset(count, 0) = ""
If Range("C21").Offset(count, 0) = Worksheets("Overview").Range(Cpties(idy)) Then _
Worksheets("Overview").Range(Cpties(idy)).Offset(Dates(idx - 2), 0) = Range("C21").Offset(count, 1)
Loop
End Select
Case Else
MsgBox "Report not compatible!"
End Select
Next idx
Dim Cell As Range 'divides input by 1000000
Worksheets("Overview").Range("D10:AD78").Select
For Each Cell In Range("D10:AD78")
Cell.Value = Cell.Value / 1000000
Next
End Function
P.S. there are definately some other mistakes, but first things first...
huge thanks in advance!
-
Feb 14th, 2013, 09:52 AM
#2
Member
Re: error 9 - subscript out of range problem
I'm not 100% sure, but I think the worksheets collection starts at 1, and your startdate is still defaulted to 0 when you hit the for loop which is why you get the error.
try this:
Code:
For idx = startDate +1 To UBound(Dates) +1
Also, for ease of understanding what is going on, using the word date in a variable name that is an integer is very confusing.
-
Feb 14th, 2013, 01:49 PM
#3
Re: error 9 - subscript out of range problem
Moved to the Office Development forum which is for VBA questions.
-
Feb 15th, 2013, 03:55 AM
#4
Thread Starter
New Member
Re: error 9 - subscript out of range problem
thanks Welshman for that, it seems to be no problem with that anymore, yet another appeared and looks definately more serious.
line: "Select Case Worksheets(idx).Range(FGRNames_Report).Value"
error: 1004 Application-defined or object-defined error
any clue? office help literally says nothing specific on that one
P.S. sorry to bother with each issue I got, yet I'm a total beginner and fear of not meating deadline.
P.S. 2. Joacim Andersson: apologies for inconvenience
-
Feb 15th, 2013, 09:21 AM
#5
Fanatic Member
Re: error 9 - subscript out of range problem
Try Select Case Worksheets(idx).Range(FGRNames_Report.Address).Value instead. FGRNames_Report is a range object and the default property of a range is the value, not the address of the range. This will only work if all of the data on these sheets is lining up. I don't know how your data is set up.
-
Feb 15th, 2013, 03:53 PM
#6
Re: error 9 - subscript out of range problem
as FGRNames_Report is a range object
try
Code:
Select Case FGRNames_Report
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
-
Feb 20th, 2013, 05:50 AM
#7
Thread Starter
New Member
Re: error 9 - subscript out of range problem
Alright, I have made several changes in this code and now it seems better, but in my select case there has to be something wrong, as it's never true, while I desire it to be.
Could you please have a look at the below and advise. I assume Select Case statement is wrongly defined, so perhaps you have got an idea how to fix it:
Code:
Public Function UpdateRiskFigures()
Dim idx As Long
Dim startDate As Integer
Dim Dates() As Integer
Dim count As Integer
Dim Cpties() As String
Dim FGRNames() As String
Dim idy As Integer
ReDim Cpties(0 To count)
Do While Not IsEmpty(Range("FGROverview").Offset(0, count))
count = count + 1
Loop
count = count - 1
ReDim Cpties(0 To count) ' returns counterparties' column
For idy = 0 To count
Cpties(idy) = Range("FGROverview").Offset(0, idy)
Next idy
ReDim Dates(startDate To count)
count = count + 1
Dim FGRNames_Report As Range ' defines column C in report's tabs as a dynamic array
Set FGRNames_Report = Range("C21").Offset(count, 0)
count = count + 1
Dim rowIndex As Integer
Worksheets("Overview").Activate
For rowIndex = 12 To 78 Step 3
idx = Range("A" & rowIndex).Value
Dim idx2 As String
idx2 = CStr(idx)
Dim i As Long
For i = 1 To ThisWorkbook.Sheets.count
Select Case idx2 = ThisWorkbook.Sheets(i).Name
Case True
ThisWorkbook.Sheets(i).Activate
count = count + 1
Select Case ThisWorkbook.Sheets(i).Range("C21").Offset(count, 0).Value
Case "Foreign Exchange Forward"
Do Until Range("C21").Offset(count, 0) = "Interest RateSwap" Or "Money Market"
If Range("C21").Offset(count, 0) = Worksheets("Overview").Range(Cpties(idy)) Then _
Worksheets("Overview").Range(Cpties(idy)).Offset(Dates(idx - 1), 0) = Range("C21").Offset(count, 1)
Loop
Case "Interest RateSwap"
Do Until Range("C21").Offset(count, 0) = "Money Market"
If Range("C21").Offset(count, 0) = Worksheets("Overview").Range(Cpties(idy)) Then _
Worksheets("Overview").Range(Cpties(idy)).Offset(Dates(idx), 0) = Range("C21").Offset(count, 1)
Loop
Case "Money Market"
Do Until Range("C21").Offset(count, 0) = ""
If Range("C21").Offset(count, 0) = Worksheets("Overview").Range(Cpties(idy)) Then _
Worksheets("Overview").Range(Cpties(idy)).Offset(Dates(idx - 2), 0) = Range("C21").Offset(count, 1)
Loop
End Select
End Select
Next i
Next rowIndex
End Function
-
Feb 20th, 2013, 06:41 AM
#8
Re: error 9 - subscript out of range problem
Select Case idx2 = ThisWorkbook.Sheets(i).Name
as this can only be true or false an IF statement would be more appropriate, but should work
is idx2 in the same workbook as the code?
is the sheetname in column A = to actual sheetname, this would be case sensitive (sheet1 <> Sheet1)?
you should have exit for in the when true code to prevent looping the remaining sheets, as can only match sheet name once
For i = 1 To ThisWorkbook.Sheets.count
Select Case idx2 = ThisWorkbook.Sheets(i).Name
why use this at all?
set wbk = thisworkbook.sheets(range("a" & rowindex) should work
if not, you have some problem with which workbook the sheets are in, or similar
ThisWorkbook.Sheets(i).Activate
this is unnecessary if you work with fully qualified range objects, which i a m sure is where your problems lie, you assume that ranges you are working with are in the sheet you think is active at the time, which is not a good method when working with multiple sheets in different books
can you post a sample workbook that demonstrates the problem, saveAs .xls and zip before attaching
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
-
Feb 20th, 2013, 09:41 AM
#9
Thread Starter
New Member
Re: error 9 - subscript out of range problem
Risicoweging generator_working - onetabtest.zip
Thanks for comments, I will get through those and amend properly.
Few words about the attachment: Please have a look at module 'nn' as there is the version I'm working on right now. Please ignore parts of code marked as comments. These are working tries which I left just in case...
In case of any doubts I'd be more than happy to answer.
thanks!
-
Feb 20th, 2013, 03:37 PM
#10
Re: error 9 - subscript out of range problem
on testing your code
in first run
your select case does return true for the correct sheet in the first pass only
sheets(trim(range("a" & rowindex))) does work correctly, indicating there is a space before or after the text in the cell
on the second
For rowIndex = 12 To 78 Step 3
idx = Range("A" & rowIndex).Value
the range is not on the sheet you think it is (i guessed right above)
somewhere in the code you activated some other sheet
you should fully qualify all ranges and avoid activating or selecting anything
try
Code:
For rowIndex = 12 To 78 Step 3
idx = sheets("overview").Range("A" & rowIndex).Value
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
|