BG- - 9 months ago 92

Vb.net Question

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.

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:

``With xlWsheet2 'check for last filled row and column of transposed data'`

If xlApp.WorksheetFunction.CountA(.Cells) <> 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) <> 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

`

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:

`.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...

ad inifintum

PS- I'm pretty new to this, so I'm probably going about this whole process the wrong way...

Answer

Based on what you told me about the row and column headings, I believe that this code will do what you want, namely put a single column sum in the first empty cell underneath.

```
Sub find()
Dim lastrow As Long, lastcol As Long, thiscol As Long
lastrow = Cells(Rows.Count, 1).End(xlUp).Row
lastcol = Cells(1, Columns.Count).End(xlToLeft).Column
For thiscol = 2 To lastcol
Cells(lastrow + 1, thiscol).Select
ActiveCell.Value = WorksheetFunction.Sum(Range(Cells(1, ActiveCell.Column), ActiveCell))
Next
End Sub
```

Best of luck.

Source (Stackoverflow)