title: Dynamic Array with (1) Value - VBA Excel
body: <p>I have a quick question that I hope will be easy for anyone familiar with arrays to answer.</p>
  
  <p>I've got a single column with a varying number of lists separated by a space. I have the program adding the numbers up. When it hits an empty cell, it should add that number to an array, increase the number of spaces in the array by (1) and, set the total counter back to zero, and skip to the next iteration of the loop its in and begin counting till it hits a space again. Over time there will be varying amounts of lists. I don't imagine that there will ever be more than 3-4, but I'd like to program it to handle it if there are more than that in the future.</p>
  
  <p>So, for instance, cells C4:C10 contain numbers adding up to 200, then cell C11 is empty. Cells C12:C14 contain numbers adding up to 150. I'd like the array to store 200 in the first spot and 150 in the second spot. Then I'd like message boxes to pop up telling me "List 1 is 200", List 2 is 150", etc... </p>
  
  <p>Right now, I'm getting subscript out of bounds, but I think it's because I didn't declare an array size. I'd like for it to be dynamic, but I didn't know how to have an array only contain (1) slot. It's always something like arr(0 to 1). I don't just see a single value in the examples I'm looking at and since it's in a for loop, if I did a loop for lbound to ubound, I think it would say something like "List 1 is 200", "List 2 is " with no value on the second one, because didn't have data if there is only one list.</p>
  
  <p>I may be over thinking this or going about it the wrong way, but I've never really used arrays before. I think it will be helpful in this program. Thanks for any help you can provide.</p>
  
  <p>I'm going to provide the mess I have below. I haven't cleaned this up and commented it well, yet, so you can ask if you have questions. I'm sure I'm doing it wrong, I'm just trying to give you an idea of where I'm going.</p>
  
  <pre><code>Private Sub cmdTest_Click()
      Dim wb As Workbook
      Dim ws As Worksheet
      Dim intCounter As Integer
      Dim intValue As Integer
      Dim strCell As String
      Dim arr() As Long
      Dim intArr As Integer
  
      Set wb = Application.Workbooks("ListTotal.xlsm")
      Set ws = wb.Worksheets("Main")
  
      Call LastRowWithData_xlUp_1(lastColRow)
  
      MsgBox "Number of lists is: " &amp; WorksheetFunction.CountIf(Range("C3:C" &amp; lastColRow), "") + 1
  
      Call FindLastRow(LastRow)
  
      'Count pages left in each series
      intValue = 0
  
      'set number of values in array
      intArr = 1
  
      For i = 3 To LastRow
  
              strCell = "C" &amp; i
  
          If Range(strCell) = "" Then
              arr(0) = intValue
              intValue = 0
              GoTo NextIteration
          End If
  
                  openingParen = InStr(Range(strCell), "[")
                  closingParen = InStr(Range(strCell), "]")
                  enclosedvalue = Mid(Range(strCell), openingParen + 1,              closingParen - openingParen - 1)
                  intValue = intValue + enclosedvalue
                  MsgBox "The number of pages in this book is: " &amp; enclosedvalue
  
          NextIteration:
          MsgBox "The total pages left in the series is: " &amp; intValue
  
      Next
  
      For i = LBound(arr) To UBound(arr)
          MsgBox ("The number of pages left in Series " &amp; j &amp; " is:")  'arr(i))
      Next i
  
  End Sub
  </code></pre>