Trouble Assigning Values to an array

[blairintern]

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.

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

Dim AllIssues(1 to 1000) as IssueClass

Again, any help is welcome!

 

[Andrew Poulsom]

Do you need?

Redim Preserve AllIssues(1 to n)
Set AllIssues = Deal

 

[blairintern]

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..

 

[blairintern]

Ok, that rid my code of that error. However, now I have a new one (of course)!
My code now looks like this:

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.

 

[Andrew Poulsom]

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.