title: Excel VBA - How to get cell value at repeating fixed interval from a dynamic range
body: <p>I have an Excel worksheet with the following format:</p>
  
  <pre><code>    Club A  Total:    ##        Club B  Total:    ##         Club C   Total:    ##
              Account Placement           Account Placement             Account Placement
              Value:    ##                Value:    ##                  Value:    ##
  
                                          Account Placement
                                          Value:    ##
  
      Club D  Total:    ##        Club E  Total:    ##         Club F  Total:     ##
              Account Placement           Account Placement            Account Placement
              Value:    ##                Value:    ##                 Value:    ##      
  
              Account Placement 
              Value:    ##
  
              Account Placement 
              Value:    ##
  </code></pre>
  
  <p>For any club, they may have more than one account placement added later on, aligned to respective column as above. My objective is to calculate the Total for each club, which will automatically account for all Account Placement under a club, with the calculation goes as:</p>
  
  <p>Eg. Total of Club B = Value of Account Placement 1 + Value of Account Placement 2 + ...</p>
  
  <p>Same goes with other clubs. I have managed to locate each club and the value of first account using the following code:</p>
  
  <pre><code>Dim ra As Range 
  For Each ra In ActiveSheet.UsedRange
      If InStr(1, ra.Text, "Account Placement") &gt; 0 Then
          accvalue = Cells(ra.Row + 1, ra.Column + 1).Value
      End If
  Next ra
  </code></pre>
  
  <p>The above code finds "Account Placement" horizontally, ie. it will get 1st value of Club A, then 1st value of Club B, then 1st value of Club C, then 2nd value of Club B, then 1st value of Club D etc. with respect to the above illustrated worksheet layout. </p>
  
  <p>This make it hard to get the sum of Value for each Club. How do you get around this problem? Any help is appreciated. </p>