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

Hi Andrew,

I am not sure that I fully understand. Firstly you said that you recorded
ActiveWorkbook.Names.Add Name:="Counting", RefersToR1C1:="='B ACC'!C13"
I think the recording would have shown the cell reference as R13C3.

Now I am assuming that strAsheet is the variable that you want to use in
place of the actual sheet name in defining a name for cell C13 so here is a
little sample code that might help.

Dim strAsheet As String

strAsheet = "MySheet" 'You can assign the string from your table.

ActiveWorkbook.Names.Add Name:="Counting", _
RefersToR1C1:=Sheets(strAsheet).Range("C13")

Note that the above does not use the same syntax as applied when recording
the code. Also note the space and underscore at the end of a line is a line
break in an otherwise single line of code.

Feel free to get back to me if you are still having problems

--
Regards,

OssieMac


"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

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

Have you tried this simple approach. Change variables to suit or use an
array
Sub placeformula()
strASheet.Range("b2").Formula = "=counta(sheet1!m:m)"
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Andrew at Fleet" wrote in message
...
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

Or just use a formula in column J:

This would go in J2:
=counta(indirect("'"&d2&"'!m:m"))-1

Or to check for a missing worksheet:
=if(iserror(cell("address",indirect("'"&d2&" acc'!a1"))),"missing",
counta(indirect("'"&d2&" acc'!m:m"))-1)


Don Guillett wrote:

Have you tried this simple approach. Change variables to suit or use an
array
Sub placeformula()
strASheet.Range("b2").Formula = "=counta(sheet1!m:m)"
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Andrew at Fleet" wrote in message
...
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

To OssieMac, Dave & Don,

Thanks for your help. I'll give these suggestions a try, & see what I can
make of them.

BTW Dave, I've been setting up the Declarations in my macros so that the
make a little more sense to me. The Public range TabACell is the A cell, in
a row of the Table sheet, usually starting at A2. I've got a number of
macros to do my work for me, & I'm trying to introduce some sort of
consistency to them. But, don't tell my boss that Excell is doing my work,
otherwise I'll lose all of my infamy at work... :)

Thanks once again.
--
Andrew
Telstra Fleet
Melbourne
Australia


"Dave Peterson" wrote:

Or just use a formula in column J:

This would go in J2:
=counta(indirect("'"&d2&"'!m:m"))-1

Or to check for a missing worksheet:
=if(iserror(cell("address",indirect("'"&d2&" acc'!a1"))),"missing",
counta(indirect("'"&d2&" acc'!m:m"))-1)


Don Guillett wrote:

Have you tried this simple approach. Change variables to suit or use an
array
Sub placeformula()
strASheet.Range("b2").Formula = "=counta(sheet1!m:m)"
End Sub

--

<<snip

Hello OssieMac,

I've changed the code to the following;
ActiveWorkbook.Names.Add Name:="Counting",
RefersToR1C1:=Sheets(strASheet).Range("M:M")

It works well. Thanks for your help. Now, as I copy data to strASheet, the
Counting cell will keep the total number of lines copied. When the COUNTA
value equals the COUNTIF value, which counts the lines I expect to copy, I'll
End out of the Loop, then go to the next customer in the report. Job's
almost done!

As for your initial comment, I understand what you mean, so I recorded the
procedure again, and it still refers to the range as RefersToR1C1:="='B
ACC'!C13". I'm hoping that C13 is actually Column 13 (or "M:M"), & not the
cell "C13".

See ya
--
Andrew
Telstra Fleet
Melbourne
Australia


"OssieMac" wrote:

Hi Andrew,

I am not sure that I fully understand. Firstly you said that you recorded
ActiveWorkbook.Names.Add Name:="Counting", RefersToR1C1:="='B ACC'!C13"
I think the recording would have shown the cell reference as R13C3.

Now I am assuming that strAsheet is the variable that you want to use in
place of the actual sheet name in defining a name for cell C13 so here is a
little sample code that might help.

Dim strAsheet As String

strAsheet = "MySheet" 'You can assign the string from your table.

ActiveWorkbook.Names.Add Name:="Counting", _
RefersToR1C1:=Sheets(strAsheet).Range("C13")

Note that the above does not use the same syntax as applied when recording
the code. Also note the space and underscore at the end of a line is a line
break in an otherwise single line of code.

Feel free to get back to me if you are still having problems

--
Regards,

OssieMac


"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