• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 64
  • Last Modified:

Array in loop

hi, experts.

I'm trying to define and use an array in a loop.
so I use
Dim MyArray() as string outside my loop

then in my loop I determine the size and
Redim MyArray (Mycount, 4)

when the loop comes back this redim statement gives a 'subscript out range error'

I have tried adding
Erase MyArray() just before the next

didn't change anything

and I have also tried moving the Dim statement into the loop, same error.

the array is used to read a matrix of data (x rows, 4 columns) to place this on a report in another sheet...
it works fine for the first loop, but apparently the 'redefining' the array is giving the issue.

tx
0
Koen
Asked:
Koen
  • 3
  • 2
1 Solution
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Please provide your full code.
0
 
KoenAuthor Commented:
    Dim MyArray() As String

    For Count = 1 To ActiveSheet.UsedRange.Rows.Count - 1

    Sheets("Projectfiche").Select
    Range("d6").Value = MyProject
    MyProjectID = Range("D2").Value

    'Define number of Statusses
    Sheets("Status").Select
    j2 = ActiveSheet.UsedRange.Rows.Count
    MyCount = Application.WorksheetFunction.CountIf(Range("Table_owssvr_1[Project Name:ID]"), CStr(MyProjectID))

    ' read statusses into array
    ReDim MyArray(1 To MyCount, 4)
    For i1 = 1 To MyCount
        For i2 = j1 To j2
            If ActiveSheet.ListObjects("Table_owssvr_1").DataBodyRange(i2, 4) = CStr(MyProjectID) Then
                MyArray(i1, 0) = ActiveSheet.ListObjects("Table_owssvr_1").DataBodyRange(i2, 5)
                MyArray(i1, 1) = ActiveSheet.ListObjects("Table_owssvr_1").DataBodyRange(i2, 6)
                MyArray(i1, 2) = ActiveSheet.ListObjects("Table_owssvr_1").DataBodyRange(i2, 8)
                MyArray(i1, 3) = ActiveSheet.ListObjects("Table_owssvr_1").DataBodyRange(i2, 7)
                MyArray(i1, 3) = Right(MyArray(i1, 3), Len(MyArray(i1, 3)) - 2) 'trim return from start
                Exit For
            End If
        Next
        j1 = j1 + 1
    Next

    'place array on projectfiche
    Sheets("Projectfiche").Select
    Range("B49").Select
    For i1 = 1 To MyCount
        ActiveCell.Offset(i1 - 1, 0) = CDate(MyArray(i1, 0))
        ActiveCell.Offset(i1 - 1, 2).Value = MyArray(i1, 1)
        ActiveCell.Offset(i1 - 1, 3).Value = MyArray(i1, 2)
        ActiveCell.Offset(i1 - 1, 5).Value = MyArray(i1, 3)
    Next

    Erase MyArray()
    
Next

Open in new window

0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Make sure that MyCount is not evaluated to 0 before using ReDim MyArray(1 To MyCount, 4).
So wrap your code within an IF statement like this.....

If MyCount > 0 Then
   ReDim MyArray(1 To MyCount, 4)
   For i1 = 1 To MyCount
   '
   '
   '
   '
   '
   '
End If

Open in new window

0
 
KoenAuthor Commented:
oh man, now I feel stupid... :-(
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Don't worry. It happens with all of us. :)
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now