vba excel 计数

vba excel counta

提问by DevilWAH

Cells(4, x) = Application.WorksheetFunction.COUNTA(Workbooks(""DB_Report.xls"").Sheets(x).Range(A:A))

I am trying to get the above function to work.

我正在尝试使上述功能正常工作。

I am calling the script from the workbook DB_report.xls

我正在从工作簿中调用脚本 DB_report.xls

This creates a new workbook ("month") and starts filling in the values.

这将创建一个新工作簿（“月”）并开始填充值。

What I am trying to get to is where

我想要去的是哪里

cell 4,1in months has the counta of sheet 1 from DB_report

4,1以月为单位的单元格具有来自 DB_report 的工作表 1 的计数

cell 4,2in months has the counta of sheet 2 from DB_report

4,2以月为单位的单元格具有来自 DB_report 的工作表 2 的计数

Can anyone reword the line above so when "months is the active work sheet I can call counta from DB_Report

任何人都可以改写上面的行，所以当“月是活动工作表时，我可以从 DB_Report 调用 counta

The line before this is

在此之前的行是

NameSH = Workbooks("DB_Report.xls").Sheets(x).Name and this works fine and returns the name of work sheet x

Thanks

谢谢

Aaron

亚伦

Ok for a bit further explicanation

好的，进一步解释一下

the steps I want to do go some thing like this

我想做的步骤是这样的

select workbook months.xls select sheet(1) cell (x,y) = counta( of range A:A , in worksheet("DB_Report") of worksheet (DB_report.xls)

选择工作簿months.xls select sheet(1) cell (x,y) = counta( of range A:A , in worksheet("DB_Report") of worksheet (DB_report.xls)

Now I know

现在我知道了

Cells(4, x) = Application.WorksheetFunction.COUNTA(sheet(3).range(a:A)

Cells(4, x) = Application.WorksheetFunction.COUNTA(sheet(3).range(a:A)

will work with in the active work sheet. So if the active sheet is sheet 1 then that would count up he numbe of cells in sheet 3 of the same workbook. I wanted to know if as well as refrenced sheet and cells in the function I can also refrence a workbook by name.

将在活动工作表中使用。因此，如果活动工作表是工作表 1，那么它将计算同一工作簿的工作表 3 中的单元格数量。我想知道除了函数中的引用表和单元格之外，我是否还可以通过名称引用工作簿。

of course i could swqap to book "DB_Report" save the value to a varible and then swap back to book "Month" and copy it to the cell.

当然，我可以 swqap 预订“DB_Report”，将值保存到变量中，然后交换回预订“Month”并将其复制到单元格中。

or could I do workbook("month").sheet(y).cells(a,b) = Application.WorksheetFunction.COUNTA(sheet(3).range(a:A)

或者我可以做 workbook("month").sheet(y).cells(a,b) = Application.WorksheetFunction.COUNTA(sheet(3).range(a:A)

while in workbook "month"

在工作簿“月”中

so really what i need is how do you refrence workbook,sheet and cells all with in a function?

所以我真正需要的是你如何在一个函数中引用工作簿、工作表和单元格？

回答by jonsca

I don't think this is exactly what you were trying to do, but it comes close and is a bit more generalized. It counts up the worksheets in what would be DB_Report.xlsand uses that to specify that number of cells in months.xls

我不认为这正是您想要做的，但它很接近并且更笼统。它计算工作表中的内容，DB_Report.xls并使用它来指定中的单元格数量months.xls

If you are running the macro from the DB_Report.xlsyou don't need to specify anything about that workbook or sheets.

如果您从 运行宏，则DB_Report.xls无需指定有关该工作簿或工作表的任何内容。

Sub counts()
    Dim sheetcounts As Integer
    Dim countas() As Integer
    Dim index As Integer
    Dim wksht As Worksheet
    Dim newbook As Workbook

    sheetcounts = ActiveWorkbook.Sheets.Count
    ReDim countas(sheetcounts)

    For Each wksht In ActiveWorkbook.Sheets
        countas(index) = Application.WorksheetFunction.CountA(wksht.Range("A:A"))
        index = index + 1
    Next

    Set newbook = Workbooks.Add
    newbook.Activate
    newbook.ActiveSheet.Range(Cells(4, 1), Cells(4, sheetcounts)) = countas
    newbook.SaveAs ("months.xls")

End Sub

It will require any error checking or verification that you need to put into it.

它将需要您进行任何错误检查或验证。

回答by DevilWAH

Hi Cheers for the comments, but i finaly worked out what the problem was.

嗨，为评论干杯，但我最终弄清楚了问题所在。

it was simple really I was just missing some formatting

真的很简单，我只是缺少一些格式

the line below works correctly

下面的行正常工作

cell(x,y) = Application.WorksheetFunction.CountA(Workbooks("DB_Report.xls").Sheets(x).Range("A:A"))

cell(x,y) = Application.WorksheetFunction.CountA(Workbooks("DB_Report.xls").Sheets(x).Range("A:A"))