vba excel counta

Question

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

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

What I am trying to get to is where

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

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

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

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)

Now I know

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.

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.

or could I do 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?

jonsca · Accepted Answer · 2011-05-10 01:42:47Z

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.xls and uses that to specify that number of cells in months.xls

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

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.

il_raffa · Accepted Answer · 2021-12-22 08:42:13Z

1

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"))

edited Dec 22, 2021 at 8:42

il_raffa

5,175145 gold badges35 silver badges41 bronze badges

answered May 10, 2011 at 10:33

DevilWAH

2,65314 gold badges45 silver badges58 bronze badges

Collectives™ on Stack Overflow

vba excel counta

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related