title: Excel 2010 VBA - Calculate average based on name in Column A
body: <p>I'm new to excel VBA and I've been searching for a few hours and I can't figure this out.  The <code>R1C1</code> method is confusing me (I have been researching it), but this is how the <code>record macro</code> started the formula.</p>
  
  <p>I have names in <code>Column 1</code> and I need to <code>average</code> all the values in <code>Column 4</code>, but only for each <code>Name</code>, then repeat for the other names.  I want the output in <code>Column 5</code>, Offset(0,1) from "Buy Day".  I have been searching online and putting together some code from other examples I found, but I still can't get it.  Any help would be appreciated!  Thanks!</p>
  
  <p><strong>Example Sheet (note: is there any way to insert a table?)</strong></p>
  
  <p><img src="https://i.stack.imgur.com/yF0Dt.jpg" alt="enter image description here"></p>
  
  <p><strong>Desired Outcome</strong></p>
  
  <p><img src="https://i.stack.imgur.com/J7UdN.jpg" alt="enter image description here"></p>
  
  <p><strong>Code (so far)</strong></p>
  
  <pre><code>Dim LastRow2 As Long
  Dim rng As Range
  Dim r1 As Long, r2 As Long
  Dim lNoRows As Long
  
  LastRow2 = Sheets("Sheet2").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
  Set rng = Sheets("Sheet2").Range("A2:D" &amp; LastRow2)
  lNoRows = rng.Rows.Count
  
  For r1 = 1 To lNoRows
      If rng(r1, 4) = "Buy Day" Then
          For r2 = 1 To lNoRows
              If rng(r1, 1) = rng(r2, 1) And r1 = r2 Then _
                  rng(r2, 5).FormulaR1C1 = "=AVERAGE(R" &amp; r2 &amp; "C[-1]:R" &amp; r2 &amp; "C[-1])"
          Next r2
      End If
  Next r1
  
  Range("K9").FormulaR1C1 = "=Average(R[-7]C[-7]:R[1]C[-7])"
  </code></pre>
  
  <p>Basically what I'm trying to recreate in the top Average formula is what's in the bottom one, except dynamically, not specific to one cell.  When it runs right now it just pulls in one value, it doesn't do any averaging.  In cell E6 it shows <code>=AVERAGE(D$5:D$5)</code>.</p>