title: Sum columns in excel using VB
body: <p>OK, I've got a straight-forward 2-d block of data in excel: row 1 and column 1 are labels, the rest are numbers.  My task right now is to put the sum of each column in the first empty cell(row) underneath.  </p>
  
  <p>Whereas my practice dataset is of known dimensions, the actual datasets I'll be using this program on will have a variable number of rows and columns.  To this end, I can't just say "=SUM(B2:B20)" because the last filled cell won't always be B20 (for example).  The easiest way to total each column, I thought, would be a FOR..NEXT loop, but I just can't get VS to accept the summation formula.  Here's what I've got so far:</p>
  
  <pre><code>  `With xlWsheet2 'check for last filled row and column of transposed data'
          If xlApp.WorksheetFunction.CountA(.Cells) &lt;&gt; 0 Then
              lRow2 = .Cells.Find(What:="*",
                                 After:=.Cells(1, 1),
                                 LookAt:=Excel.XlLookAt.xlPart,
                                 LookIn:=Excel.XlFindLookIn.xlFormulas,
                                 SearchOrder:=Excel.XlSearchOrder.xlByRows,
                                 SearchDirection:=Excel.XlSearchDirection.xlPrevious,
                                 MatchCase:=False).Row
          Else : lRow2 = 1
          End If
  
          If xlApp.WorksheetFunction.CountA(.Cells) &lt;&gt; 0 Then
              lCol2 = .Cells.Find(What:="*",
                                 After:=.Range("A1"),
                                 LookAt:=Excel.XlLookAt.xlPart,
                                 LookIn:=Excel.XlFindLookIn.xlFormulas,
                                 SearchOrder:=Excel.XlSearchOrder.xlByRows,
                                 SearchDirection:=Excel.XlSearchDirection.xlPrevious,
                                 MatchCase:=False).Column
          Else : lCol2 = 1
          End If
  
  
          lastcell2 = xlWsheet2.Cells(lRow2, lCol2) 'defines last row, column of transposed data'
          emptyRow1 = xlWsheet2.Rows(lRow2).Offset(1) 'defines the first empty row'
  
  
          'add in cell of SUM underneath each column'
          For i As Integer = 2 To lCol2
              colTop = xlWsheet2.Cells(2, i)
              colBot = xlWsheet2.Cells(lRow2, i)
  
              ELtotal = xlWsheet2.Range(emptyRow1, i)
              ELtotal = .Sum(.Range(colTop, colBot))
          Next i
      End With
  </code></pre>
  
  <p>`
  Now, the ELtotal statements used to be one long line, but I was trying to see what part VS had a problem with.  It breaks at the first one, .Range(emptyRow1, i).  Here's other iterations of that equation I've tried that weren't accepted:</p>
  
  <pre><code>.Range(emptyRow1, I).Formula = "=SUM(colTop, colBot)"
  .Range(emptyRow1, I).Formula = "=SUM(.cells(2,i), (lRow2,i))"
  .Range(emptyRow1, I).Formula = .sum(.range(colTop, colBot)
  .Range(emptyRow1, I).Value = etc...
  </code></pre>
  
  <p>ad inifintum</p>
  
  <p>PS- I'm pretty new to this, so I'm probably going about this whole process the wrong way...</p>