Excel VBA: Using a variable when defining a list as a String

Question

Pretty new to VBA but I'm learning quickly.

I'm finally getting used to using loops to perform repetitive tasks, and in this case, I want each pass through the loop to define a different variable. I'd be defining a list as a string and pushing a value to each part of the list for each loop.

Obviously, the number of loops is variable, so I need the end point of the defined list to be a variable. From what I've searched, that's not possible? I'm getting a "constant expression required" error.

Here is the code (lastRow is already defined):

NextAverage = 0
section = 1
Dim AverageSection(1 To section) As String
For section = 1 To PhraseSections

ActiveCell.Formula = "=MATCH(""average"",A" & NextAverage + 1 & ":A" & lastRow & ",0)"
Selection.Offset(1, 0).Select
ActiveCell.Formula = "=SUM(G1:G" & section & ")"
NextAverage = ActiveCell.Value
AverageSection(section) = ActiveCell.Value

Next section

Any help would be greatly appreciated!

Answer 1

Try using Redim Preserve:

Dim AverageSection() As String
For section = 1 To PhraseSections
    Redim Preserve AverageSection(section)
    ...
Next section

Does this help?