Trouble Assigning Values to an array

blairintern

New Member
Joined
Jul 16, 2007
Messages
21
I have been having some trouble assigning a user-defined class of mine (IssueClass) variable into my IssueClass array called "AllIssues"

I keep receiving an "Error 9: Subscript Out of Range" error when I run in the debug mode at the assignment of the array portion.

I am using a variable as the index for the array. Any light that could be shed on this problem would be very welcome.
Rich (BB code):
Sub MakeIssue()
    Application.ScreenUpdating = False
    Dim i As Integer ' indicates the index of the loop
    Dim n As Integer ' indicates what issue loop is on
    Dim AllIssues() As IssueClass
    ' redim AllIssues with loop to detect each issue. GAH!
    Dim Deal As IssueClass
    i = 0
    n = 1
    Do
        With Sheets(1).Range("A1")
            If .Offset(i, 0) Is Nothing Then
                Set Deal = New IssueClass
                i = i + 1
            Else
                Set Deal = New IssueClass
                Deal.Purpose = .Offset(i, 2) & " " & .Offset(i, 4) _
                & " " & .Offset(i, 5)
                Deal.DatedDate = .Offset(i, 0)
                Deal.Series = .Offset(i, 3)
                Deal.Par = .Offset(i, 7)
                Deal.CallDate = .Offset(i, 8)
                Deal.CallPrice = .Offset(i, 9)
                Deal.Fitch = .Offset(i, 10)
                Deal.Moody = .Offset(i, 11)
                Deal.SandP = .Offset(i, 12)
                Deal.Underwriter = .Offset(i, 16)
                Deal.Counsel = .Offset(i, 17)
                n = n + 1
                i = i + 1
            End If
        End With
       Set AllIssues(n) = Deal <---Problem is here
    Loop Until ActiveCell.Range("A1").Offset(i, 0).Value = "Session Det"
End Sub

Given the nature of the error, it sounds like my "n" value becomes something invalid during the loop.

I also had the same error when I included a size of the array in the declaration of the AllIssues array
Rich (BB code):
Dim AllIssues(1 to 1000) as IssueClass

Again, any help is welcome!
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
Now that would then maintain the current content of the array and extend it to the new index n?

This code would then go into the loop and work properly? I will tryit shortly as soon as I get some type mismatch errors hammered out.

Thanks for the help, I will let you know how it works..
 
Upvote 0
Ok, that rid my code of that error. However, now I have a new one (of course)!
My code now looks like this:
Code:
Sub MakeIssue()
    Application.ScreenUpdating = False
    Dim i As Integer ' indicates the index of the loop
    Dim n As Integer ' indicates what issue loop is on
    i = 0
    n = 1
    Dim AllIssues() As IssueClass
    ' ReDim Preserve AllIssues(1 To n)
    ' redim AllIssues with loop to detect each issue.
 
    Do
        With Sheets(1).Range("A1")
            If .Offset(i, 0) Is Nothing Then
                Set Deal = New IssueClass
            Else
                Set Deal = New IssueClass
                Deal.Purpose = .Offset(i, 2) & " " & .Offset(i, 4) _
                & " " & .Offset(i, 5)
                Deal.DatedDate = .Offset(i, 0)
                Deal.Series = .Offset(i, 3)
                Deal.Par = .Offset(i, 7)
                Deal.CallDate = .Offset(i, 8)
                Deal.CallPrice = .Offset(i, 9)
                Deal.Fitch = .Offset(i, 10)
                Deal.Moody = .Offset(i, 11)
                Deal.SandP = .Offset(i, 12)
                Deal.Underwriter = .Offset(i, 16)
                Deal.Counsel = .Offset(i, 17)
                n = n + 1
            End If
            i = i + 1
        End With
       ReDim Preserve AllIssues(1 To n)
       Set AllIssues(n) = Deal
    Loop Until IsEmpty(ActiveCell.Range("A1:A40").Offset(i, 0))
End Sub

I am now recieving an error: "Error 6: Overflow" and it indicates that my N value has become 32,767 through the loop. Any ideas?

Thanks again for your help.
 
Upvote 0
What is this supposed to be testing?

Loop Until IsEmpty(ActiveCell.Range("A1:A40").Offset(i, 0))

You are not changing the ActiveCell in your loop.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top