使用VB对excel，列的总和只有一个单元格 [英] Using VB on excel, sum of column is only one cell

问题描述

$ $ $ $

xlWorkbook = xlApp.Workbooks.Open（文件名：=C： \Users\d3p823\Desktop\test data.xlsx）'打开工作簿'
xlWsheet2 = xlWorkbook.Sheets（Sheet4）'设置活动工作表'
xlApp.Visible = True

与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
对于thiscol = 2 To lastcol
xlWsheet2.Cells（lastrow + 1，thiscol）.Select（）
xlApp .ActiveCell.Value = xlApp.WorksheetFunction.Sum（xlWsheet2.Cells（2，xlApp.ActiveCell.Column），xlApp.ActiveCell）
下一个
结束

确定，所以我在电子表格中有一个矩形块的数据，其中第1行和第1列为标签（文本和数字），d来自细胞（2,2）的ata。数据文件的行数和列数可以变化，但所有的数据都是连续的：没有空单元格。 @Clif和我已经提出了上面的代码找到最后一行和数据列，然后总结每列下面的第一个空单元格中的每列。

我不会收到错误，谢谢，但是添加到第一个空行中的总和并不总是整列，只是第一个单元格。这使我觉得FOR-NEXT循环不是进行第一次迭代，但是我看不出为什么会发生这种情况。

解决方案

您的代码中有几个问题：

• 您将两个单独的单元格传递给Sum，而不是Range。


• 您创建一个With块，然后不要使用它，


• 使用Select / ActiveCell既不必要也不需要


• 使用整数而不是 long

...

 使用xlWsheet2 
 lastrow = .Cells（.Rows.Count ，1）.End（Excel.XlDirection.xlUp）.Row 
 lastcol = .Cells（1，.Columns.Count）.End（Excel.XlDirection.xlToLeft）.Column 
对于thiscol = 2 To lastcol 
 .Cells（lastrow + 1，thiscol）.Value = _ 
 xlApp.Sum（.Range（.Cells（2，thiscol））.Cells（lastrow，thiscol）））
 Next 
结束
  

    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.

解决方案

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

这篇关于使用VB对excel，列的总和只有一个单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！