CODEWITHSUNDEEP
vbareferencecountexcel
DevilWAH
DevilWAH

Reputation: 2643

vba excel counta

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?

Upvotes: 1

Views: 23080

Answers (2)

DevilWAH
DevilWAH

Reputation: 2643

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

Upvotes: 1

jonsca
jonsca

Reputation: 10381

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.

Upvotes: 1

Related Questions