1

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!

Community
  • 1
  • 1
LeeLK
  • 39
  • 8

1 Answers1

0

Try using Redim Preserve:

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

Does this help?

Community
  • 1
  • 1
criticalfix
  • 2,870
  • 1
  • 19
  • 32
  • @cricialfix So simple! I'll look into what that means--hopefully it's not too over my head. Thanks very much! – LeeLK May 21 '13 at 18:07