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
(E-Mail Removed)
"Andrew at Fleet" <(E-Mail Removed)> wrote in message
news:E247C286-1775-43FB-9757-(E-Mail Removed)...
> 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
> (E-Mail Removed)
> "Andrew at Fleet" <(E-Mail Removed)> wrote in message
> news:E247C286-1775-43FB-9757-(E-Mail Removed)...
> > 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