vba: Array within Do Loop not working

Question

I am trying to create an array with project numbers. The code below loops through a list of programs within countries (in the program content review tab). For each program it switches to a tab with a list of projects (Project Summary Stats) and I want it to create an array with the project numbers so that in separate code I can call on it. The code below identifies the activeprogramnum correctly but spits out all kinds of strange project numbers. Help!

Sub test_array()
Dim ActiveProgramNum
Sheets("ProgramContentReview").Select
Range("b2").Select ' country

Do Until IsEmpty(ActiveCell)

    Dim ProjectNumArray(10)
    Dim l
    l = 0

    If (Sheets("Introduction").Range("A15").Value & " ") = ActiveCell.Value Then ' This identifies the country

     ActiveProgramNum = ActiveCell.Offset(0, 2)

            Sheets("ProjectSummaryStats").Select
            ' Select cell A2, *first line of data*.
            Range("D2").Select
        Do Until IsEmpty(ActiveCell) ' loop through projects (at program level)

             If ActiveProgramNum = ActiveCell.Value Then

                  ProjectNumArray(l) = ActiveCell.Offset(0, 2).Value ' this is the column with the project numbers
                  l = l + 1
                    MsgBox (ActiveProgramNum)
                    MsgBox (ProjectNumArray(l))

              End If

          ActiveCell.Offset(1, 0).Select
          Loop
End If
Sheets("ProgramContentReview").Select
ActiveCell.Offset(1, 0).Select
Loop

End Sub

I did that and it didn't change anything. I believe the do loop is working correctly, as I use it elsewhere in my code... — spaindc, Oct 29 '14 at 23:16
I suspect it's an `Active` cell problem. I'd suggest following the [advice from this post](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) and get rid of all your `Active` & `Select` statements. — Automate This, Oct 29 '14 at 23:23

score 0 · Answer 1 · answered Oct 30 '14 at 20:04

The code below works!!!! I only included the key changes.

    Dim ProjectNumArray() As String
    Dim l As Integer
    l = 0

   'If (Sheets("Introduction")...
   'If ActiveProgramNum = ...

                  l = l + 1
                  ReDim ProjectNumArray(1 To l)
                  ProjectNumArray(l) = ActiveCell.Offset(0, 5).Value

   'Rest is the same

vba: Array within Do Loop not working

1 Answers1