The syntax for this line:
ActiveWorkbook.Names.Add Name:="Counting", RefersToR1C1:="='strASheet'!C13"
would look like:
ActiveWorkbook.Names.Add Name:="Counting", _
RefersToR1C1:="="'" & strASheet & "'!C13"

but you don't have to add a name to do the count.


Dim TabACell as range
dim WksAcc as worksheet

'it scares me when I use the activecell?
'What happens if the user isn't in the right cell???
Set TabACell = ActiveCell ' Company
'maybe use column A of the row with the activecell?
Set tabAcell = activecell.entirerow.cells(1)
Set TabDCell = TabACell.Offset(0, 3) ' Company Initial
Set TabJCell = TabACell.Offset(0, 9) ' Count Copied

set wksacc = nothing
on error resume next
set wksacc = worksheets(tabdcell.value & " ACC")
on error goto 0
if wksacc is nothing then
msgbox "No ACC sheet for " & tabdcell.value & " Found!"
exit sub '???
end if

with tabjcell
.formula = "=counta(" & wksacc.range("M:M").address(external:=true) & ")-1"
.value = .value 'convert to values???
end with
'or just do the count in code???
tabjcell.value = application.counta(wksacc.range("M:M")) - 1


Uncompiled and untested. Watch for typos.


Andrew at Fleet wrote:

To whom it may concern,

I'm trying to insert a COUNTA funtion in a cell, which refers to a range set
in different worksheets, but I'm having problems, & was hoping to find some
help.

I have a macro that I'm writing for Excell 2003, which creates multiple
reports, with 4 types of sheets each. I'm using cell values from a Table
sheet to create Strings, which will select different sheets, with the
following code;
Set TabACell = ActiveCell ' Company
Set TabDCell = TabACell.Offset(0, 3) ' Company Initial
Set TabJCell = TabACell.Offset(0, 9) ' Count Copied
strASheet = TabDCell.Value & " ACC"
strFSheet = TabDCell.Value & " FLEET"
, etc. Then, I want to count the non-empty cells in a column in each sheet.
I recorded the following macro;
Sheets("B ACC").Select
Columns("M:M").Select
ActiveWorkbook.Names.Add Name:="Counting", RefersToR1C1:="='B ACC'!C13"
Sheets("Table").Select
ActiveCell.FormulaR1C1 = "=COUNTA(Counting)"

Then changed it to suit my table sheet;
Sheets(strASheet).Select
Columns("M:M").Select
ActiveWorkbook.Names.Add Name:="Counting", RefersToR1C1:="='strASheet'!C13"
Sheets("Table").Select
TabJCell.FormulaR1C1 = "=COUNTA(Counting)-1"

This seemed to be a good solution to me, as the TabDCell values change for
each report. I would then delete the Name created, & loop the code for the
next report. But, the macro is having trouble with the new reference that I
have written. Considering that the string values will change. How can I
re-write RefersToR1C1:="='B ACC'!C13" so that it uses the string created from
the table values?

TIA
--
Andrew
Telstra Fleet
Melbourne
Australia

--

Dave Peterson