Results 1 to 10 of 10

Thread: error 9 - subscript out of range problem

  1. #1

    Thread Starter
    New Member
    Join Date
    Feb 2013
    Posts
    6

    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!

  2. #2
    Member
    Join Date
    Nov 2012
    Posts
    62

    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.

  3. #3
    I'm about to be a PowerPoster! Joacim Andersson's Avatar
    Join Date
    Jan 1999
    Location
    Sweden
    Posts
    14,649

    Re: error 9 - subscript out of range problem

    Moved to the Office Development forum which is for VBA questions.

  4. #4

    Thread Starter
    New Member
    Join Date
    Feb 2013
    Posts
    6

    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

  5. #5
    Fanatic Member dmaruca's Avatar
    Join Date
    May 2006
    Location
    Jacksonville, FL
    Posts
    577

    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.

  6. #6
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    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

  7. #7

    Thread Starter
    New Member
    Join Date
    Feb 2013
    Posts
    6

    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

  8. #8
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    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

  9. #9

    Thread Starter
    New Member
    Join Date
    Feb 2013
    Posts
    6

    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!

  10. #10
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    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
  •  



Click Here to Expand Forum to Full Width