时间戳

首页 > 解决方案 > 跨多个工作表计算数据

excel - 跨多个工作表计算数据

问题描述

我想计算一列中包含数据的单元格的数量。

我必须从所有工作簿中计算这些单元格。

例如，我有 2 个工作簿：WB1 和 WB2。

这个怎么算？

Option Explicit

Private Sub CommandButton1_Click()

    Dim paths(), wbs() As Workbook
    Dim x As Integer
    Dim sh, rn, k

    paths = Application.GetOpenFilename(FileFilter:="Excel Files (*.XLSX), *.XLSX", _
      MultiSelect:=True, Title:="Please browse all the Rawdata files")

    For x = 1 To UBound(paths)
        ReDim wbs(UBound(paths))
        Set wbs(x) = Workbooks.Open(paths(x))

        Set sh = wbs(x).Sheets("Role ID - Description")
        Set rn = sh.UsedRange
        k = rn.Rows.Count + rn.Row - 1 - 1
        wbs(x).Close
    Next

End Sub

标签： excelvba

解决方案

您没有在工作簿的工作表之间维护您的计数器（例如，k = k + 1 迭代 1）。在您的工作簿中，您需要以下内容：

dim i as long, k as long, lr as long
With Workbook("blah")
    For i = 1 to .sheets.count
        With .Sheets(i)
            lr = .cells(.rows.count,1).end(xlup).row
            k = k + lr
        End with
    Next i
    'output k to some destination
    k=0
End with

编辑1：

根据 Kubie 的建议添加计数场景（未经测试）：

dim i as long, k as long, cnta as long
With Workbook("blah")
    For i = 1 to .sheets.count
        With .Sheets(i)
            cnta = application.counta(.columns(1))
            k = k + cnta
        End with
    Next i
    'output k to some destination
    k=0
End with

推荐阅读