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!

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.

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

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

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.

as FGRNames_Report is a range object
try

Code:

---

Select Case FGRNames_Report

---

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

---

Quote:

---

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

Quote:

---

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

Quote:

---

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

Attachment 96709

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!

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

---