Using VB on excel, sum of column is only one cell

问题:

    Dim lastrow As Integer
    Dim lastcol As Integer, thiscol As Integer

    xlWorkbook = xlApp.Workbooks.Open(Filename:="C:\Users\d3p823\Desktop\test data.xlsx") 'opens workbook'
    xlWsheet2 = xlWorkbook.Sheets("Sheet4") 'set active sheet'
    xlApp.Visible = True

    With xlWsheet2
        lastrow = xlWsheet2.Cells(xlWsheet2.Rows.Count, 1).End(Excel.XlDirection.xlUp).Row
        lastcol = xlWsheet2.Cells(1, xlWsheet2.Columns.Count).End(Excel.XlDirection.xlToLeft).Column
        For thiscol = 2 To lastcol
            xlWsheet2.Cells(lastrow + 1, thiscol).Select()
            xlApp.ActiveCell.Value = xlApp.WorksheetFunction.Sum(xlWsheet2.Cells(2, xlApp.ActiveCell.Column), xlApp.ActiveCell)
        Next
    End With

OK, so I've got a rectangular block of data on a spreadsheet that has row 1 and column 1 as labels (text and numbers), and data from cell(2,2) on. The data file will be variable in the number of rows and columns it has, but all the data is contiguous: no empty cells. @Clif and I have come up with the above code to find the last row and column of data, and then sum up each column in the first empty cell beneath each column.

I don't get errors anymore, thank goodness, but the sums added into the first empty row aren't totaling the entire column, just the first cell. This makes me think that the FOR-NEXT loop isn't progressing past the first iteration, but I can't see why that's occurring.

回答1:

There are several issues in your code:

• You are passing two individual cells to your Sum, rather than a Range.
• you create a With block then don't use it,
• use of Select/ActiveCell is neither necessary nor desirable
• use of Integer rather than long

…

With xlWsheet2
    lastrow = .Cells(.Rows.Count, 1).End(Excel.XlDirection.xlUp).Row
    lastcol = .Cells(1, .Columns.Count).End(Excel.XlDirection.xlToLeft).Column
    For thiscol = 2 To lastcol
        .Cells(lastrow + 1, thiscol).Value = _
          xlApp.Sum(.Range(.Cells(2, thiscol), .Cells(lastrow, thiscol)))
    Next
End With

回答2:

• By declaring your variables as integers, you limit them to 32,768. So if you are summing rows below that level, your macro will fail.

• Since you indicate your data area is contiguous, and rectangular, and starts at A1, you can just use CurrentRegion to obtain the range reference to your data area.

• For speed purposes, an operation like this will execute much more rapidly when done within a vba array, rather than on the worksheet.

• Some limited testing suggests looping to ADD executes more quickly (order of magnitude) than using the SUM worksheet function, at least on a limited data set. Whether that will be the case on your real data can only be resolved by testing.

• I would suggest something like the following (you will need to add code to open the workbook, and change the code setting the appropriate sheet)

-

Option Explicit
Sub AddColumns()
    Dim WS As Worksheet
    Dim RNG As Range
    Dim V As Variant
    Dim I As Long, J As Long
    Dim dTemp As Double

Set WS = Worksheets("sheet1")

'with offset, we will skip the labels row, and leave
'  one blank at the end for the total
Set RNG = Cells(1, 1).CurrentRegion.Offset(rowoffset:=1)
V = RNG

For I = 2 To UBound(V, 2) 'The "2" to skip the first column
    dTemp = 0
    For J = 1 To UBound(V, 1) - 1
        dTemp = dTemp + V(J, I)
    Next J
    V(UBound(V), I) = dTemp
Next I

RNG = V

End Sub

回答3:

You require a second For loop to go from row 2 to the lastrow.

Alternatively replace the statement before the Next statement with this

ActiveCell.FormulaR1C1 = "=SUM(R[" & -lastrow + 1 & "]C:R[-1]C)"

This uses the SUM function and eliminates the need for the second For loop.