I want to add the count function to the toolbar (like the sum button). I
know how to customise the toolbar, but am unable to find the "count"
function. Am I missing something?

Thanks in advance for your help.

m

What version of Excel are you using? In 2002 you can click the down arrow to
the right of the AutoSum Button and select Count.

Rowan

"smeesh" wrote:

I want to add the count function to the toolbar (like the sum button). I
know how to customise the toolbar, but am unable to find the "count"
function. Am I missing something?

Thanks in advance for your help.

m

Sorry Rowan - should have included that. No unfortunately not using 2002.
Still on 2000 here.

Any other ideas?

thanks

m
"Rowan" wrote in message
...
What version of Excel are you using? In 2002 you can click the down arrow
to
the right of the AutoSum Button and select Count.

Rowan

"smeesh" wrote:

I want to add the count function to the toolbar (like the sum button).
I
know how to customise the toolbar, but am unable to find the "count"
function. Am I missing something?

Thanks in advance for your help.

m

You could add a Custom Macro button to your toolbar linked to a macro that
inserts the count function. I will try to post an example shortly.

Rowan

"smeesh" wrote:

Sorry Rowan - should have included that. No unfortunately not using 2002.
Still on 2000 here.

Any other ideas?

thanks

m
"Rowan" wrote in message
...
What version of Excel are you using? In 2002 you can click the down arrow
to
the right of the AutoSum Button and select Count.

Rowan

"smeesh" wrote:

I want to add the count function to the toolbar (like the sum button).
I
know how to customise the toolbar, but am unable to find the "count"
function. Am I missing something?

Thanks in advance for your help.

m

Okay here goes:

You can add a macro to your Personal Macros Workbook which will drive a
custom button on your toolbar. The Personal Macros Workbook is a hidden book
that is always available when you are in Excel. If you don't use this
allready then the easiest way to create one is to follow the steps below.

1. In Excel click on Tools Macro Record New Macro. This will display the
Record Macro Dialog.
2. Change the Macro Name to TempMacro and in the Store Macro In dropdown
select Personal Macro Workbook. Click OK.
3. Select any cell and then click on the Stop Recording button. This is a
blue square that should now be visible.
4. Goto the Visual Basic Editor by clicking Tools Macro Visual Basic
Editor.
5. There should be a Window on the left called Project - VBAProject. If this
is not visible click on View Project Explorer.
6. Click on the expand button (+) next to VBAProject (PERSONAL.XLS).
7. Click exand button next to Modules
8. Double click on Module1 to display code in Code window on right of screen.
9. Delete all code from (and including) the line "Sub TempMacro()" to (and
including) the line "End Sub".
10. Copy and Paste the code below into the code window and then click on the
Save button.

Code starts he

Sub MyCountButton()

Dim thisRow As Long
Dim thisCol As Integer
Dim firstRow As Long
Dim headVal As Variant
Dim nextVal As Variant

thisRow = ActiveCell.Row
thisCol = ActiveCell.Column

'Finds first used cell in column
If Cells(1, thisCol).Value = Empty Then
firstRow = Cells(1, thisCol).End(xlDown).Row
Else
firstRow = 1
End If

If firstRow < thisRow Then
headVal = Cells(firstRow, thisCol).Value
nextVal = Cells(firstRow + 1, thisCol).Value

'Checks for header row
If VarType(headVal) = 8 And VarType(headVal) _
< VarType(nextVal) Then
firstRow = firstRow + 1
End If

'Inserts formula
ActiveCell.FormulaR1C1 = "=COUNT(R[" & firstRow _
- thisRow & "]C:R[-1]C)"

'Enters cell for editing - mimic of SUM button
'Delete line below if you want to just accept
'range generated by macro
Application.SendKeys ("{F2}")
End If

End Sub

End of code.

11. Return to Excel sheet by closing Visual Basic Editor.
12. Customize toolbars adding a Custom Button - found in Commands - Macros.
13. While still in Customize mode right click the new button and select
Assign Macro. Select PERSONAL.XLS!MyCountButton. Click OK
(You can also change the Button Image here if you like).

And that's it. The new count button will now be available any time you are
in Excel.

Hope this helps
Rowan

Hi Rowan,

That works a treat thankyou. I wonder if you could show me a slight
modification to the macro code though.

It would be terrific if I could get the code to count to the last blank row.
I have a subtotal function happening at the moment, and now also need to
count multiple records based upon a supervisor ID (which is what the sort is
on). maybe I am going about this the wrong way, but if the count function
could instead of counting the whole column, be modified to count to the last
blank row I would really appreciate it.

Is that possible?

Thanks again.

m
"Rowan" wrote in message
...
Okay here goes:

You can add a macro to your Personal Macros Workbook which will drive a
custom button on your toolbar. The Personal Macros Workbook is a hidden
book
that is always available when you are in Excel. If you don't use this
allready then the easiest way to create one is to follow the steps below.

1. In Excel click on Tools Macro Record New Macro. This will display
the
Record Macro Dialog.
2. Change the Macro Name to TempMacro and in the Store Macro In dropdown
select Personal Macro Workbook. Click OK.
3. Select any cell and then click on the Stop Recording button. This is a
blue square that should now be visible.
4. Goto the Visual Basic Editor by clicking Tools Macro Visual Basic
Editor.
5. There should be a Window on the left called Project - VBAProject. If
this
is not visible click on View Project Explorer.
6. Click on the expand button (+) next to VBAProject (PERSONAL.XLS).
7. Click exand button next to Modules
8. Double click on Module1 to display code in Code window on right of
screen.
9. Delete all code from (and including) the line "Sub TempMacro()" to (and
including) the line "End Sub".
10. Copy and Paste the code below into the code window and then click on
the
Save button.

Code starts he

Sub MyCountButton()

Dim thisRow As Long
Dim thisCol As Integer
Dim firstRow As Long
Dim headVal As Variant
Dim nextVal As Variant

thisRow = ActiveCell.Row
thisCol = ActiveCell.Column

'Finds first used cell in column
If Cells(1, thisCol).Value = Empty Then
firstRow = Cells(1, thisCol).End(xlDown).Row
Else
firstRow = 1
End If

If firstRow < thisRow Then
headVal = Cells(firstRow, thisCol).Value
nextVal = Cells(firstRow + 1, thisCol).Value

'Checks for header row
If VarType(headVal) = 8 And VarType(headVal) _
< VarType(nextVal) Then
firstRow = firstRow + 1
End If

'Inserts formula
ActiveCell.FormulaR1C1 = "=COUNT(R[" & firstRow _
- thisRow & "]C:R[-1]C)"

'Enters cell for editing - mimic of SUM button
'Delete line below if you want to just accept
'range generated by macro
Application.SendKeys ("{F2}")
End If

End Sub

End of code.

11. Return to Excel sheet by closing Visual Basic Editor.
12. Customize toolbars adding a Custom Button - found in Commands -
Macros.
13. While still in Customize mode right click the new button and select
Assign Macro. Select PERSONAL.XLS!MyCountButton. Click OK
(You can also change the Button Image here if you like).

And that's it. The new count button will now be available any time you are
in Excel.

Hope this helps
Rowan

I am not totally sure that I have followed your requirement correctly but
what I think you want to do is count from first value down to last value
taking in any blank rows inbetween. If so try this code - just paste it over
the existing code:

Sub MyCountButton()

Dim thisRow As Long
Dim thisCol As Integer
Dim firstRow As Long
Dim lastRow As Long
Dim headVal As Variant
Dim nextVal As Variant

thisRow = ActiveCell.Row
thisCol = ActiveCell.Column

'Finds first used cell in column
If Cells(1, thisCol).Value = Empty Then
firstRow = Cells(1, thisCol).End(xlDown).Row
Else
firstRow = 1
End If

lastRow = Cells(thisRow, thisCol).End(xlUp).Row

If firstRow < thisRow And lastRow < thisRow Then
headVal = Cells(firstRow, thisCol).Value
nextVal = Cells(firstRow + 1, thisCol).Value

'Checks for header row
If VarType(headVal) = 8 And VarType(headVal) _
< VarType(nextVal) Then
firstRow = firstRow + 1
End If

'Inserts formula
ActiveCell.FormulaR1C1 = "=COUNT(R[" & firstRow _
- thisRow & "]C:R[" & lastRow - thisRow & "]C)"

'Enters cell for editing - mimic of SUM button
'Delete line below if you want to just accept
'range generated by macro
Application.SendKeys ("{F2}")
End If

End Sub


Regards
Rowan

Sorry Rowan,

I didnt make it very clear. What I meant was:

A1 4
A2 6
A3 9
A4
A5 7
A6 2
A7 1
A8

If I were to do an Autosum in (row) A8 it would automatically try to add the
figures from A5:A7 (noting the break in A4). This is what I would like the
count function to do. At the moment it is counting from A1.

Thanks again.

m
"Rowan" wrote in message
...
I am not totally sure that I have followed your requirement correctly but
what I think you want to do is count from first value down to last value
taking in any blank rows inbetween. If so try this code - just paste it
over
the existing code:

Sub MyCountButton()

Dim thisRow As Long
Dim thisCol As Integer
Dim firstRow As Long
Dim lastRow As Long
Dim headVal As Variant
Dim nextVal As Variant

thisRow = ActiveCell.Row
thisCol = ActiveCell.Column

'Finds first used cell in column
If Cells(1, thisCol).Value = Empty Then
firstRow = Cells(1, thisCol).End(xlDown).Row
Else
firstRow = 1
End If

lastRow = Cells(thisRow, thisCol).End(xlUp).Row

If firstRow < thisRow And lastRow < thisRow Then
headVal = Cells(firstRow, thisCol).Value
nextVal = Cells(firstRow + 1, thisCol).Value

'Checks for header row
If VarType(headVal) = 8 And VarType(headVal) _
< VarType(nextVal) Then
firstRow = firstRow + 1
End If

'Inserts formula
ActiveCell.FormulaR1C1 = "=COUNT(R[" & firstRow _
- thisRow & "]C:R[" & lastRow - thisRow & "]C)"

'Enters cell for editing - mimic of SUM button
'Delete line below if you want to just accept
'range generated by macro
Application.SendKeys ("{F2}")
End If

End Sub


Regards
Rowan

I follow you now. This code should do it:

Sub MyCountButton()

Dim thisRow As Long
Dim thisCol As Integer
Dim firstRow As Long
Dim headVal As Variant
Dim nextVal As Variant

thisRow = ActiveCell.Row
thisCol = ActiveCell.Column

If thisRow 1 Then
firstRow = Cells(thisRow - 1, thisCol).End(xlUp).Row
Else
firstRow = 1
End If

If firstRow < thisRow Then
headVal = Cells(firstRow, thisCol).Value
nextVal = Cells(firstRow + 1, thisCol).Value

'Checks for header row
If VarType(headVal) = 8 And VarType(headVal) _
< VarType(nextVal) Then
firstRow = firstRow + 1
End If

'Inserts formula
ActiveCell.FormulaR1C1 = "=COUNT(R[" & firstRow _
- thisRow & "]C:R[-1]C)"

'Enters cell for editing - mimic of SUM button
'Delete line below if you want to just accept
'range generated by macro
Application.SendKeys ("{F2}")
End If

End Sub

I will qualify this by pointing out that it is still not as smart as Excel's
sum button. In your example if you used the sum button to add a sum in cell
A4 and then used it again to add a sum to cell A8, A8 would be the sum of
A5-A7. Excel recognises that A4 contains a calculation. My function will not
so if you use if in A4 and then A8 A8 will count A1 - A7. However if you use
it in A8 while A4 is still blank it will (should) work as required. You could
then use it in A4.

Regards
Rowan

"smeesh" wrote:

Sorry Rowan,

I didnt make it very clear. What I meant was:

A1 4
A2 6
A3 9
A4
A5 7
A6 2
A7 1
A8

If I were to do an Autosum in (row) A8 it would automatically try to add the
figures from A5:A7 (noting the break in A4). This is what I would like the
count function to do. At the moment it is counting from A1.

Thanks again.

m
"Rowan" wrote in message
...
I am not totally sure that I have followed your requirement correctly but
what I think you want to do is count from first value down to last value
taking in any blank rows inbetween. If so try this code - just paste it
over
the existing code:

Sub MyCountButton()

Dim thisRow As Long
Dim thisCol As Integer
Dim firstRow As Long
Dim lastRow As Long
Dim headVal As Variant
Dim nextVal As Variant

thisRow = ActiveCell.Row
thisCol = ActiveCell.Column

'Finds first used cell in column
If Cells(1, thisCol).Value = Empty Then
firstRow = Cells(1, thisCol).End(xlDown).Row
Else
firstRow = 1
End If

lastRow = Cells(thisRow, thisCol).End(xlUp).Row

If firstRow < thisRow And lastRow < thisRow Then
headVal = Cells(firstRow, thisCol).Value
nextVal = Cells(firstRow + 1, thisCol).Value

'Checks for header row
If VarType(headVal) = 8 And VarType(headVal) _
< VarType(nextVal) Then
firstRow = firstRow + 1
End If

'Inserts formula
ActiveCell.FormulaR1C1 = "=COUNT(R[" & firstRow _
- thisRow & "]C:R[" & lastRow - thisRow & "]C)"

'Enters cell for editing - mimic of SUM button
'Delete line below if you want to just accept
'range generated by macro
Application.SendKeys ("{F2}")
End If

End Sub


Regards
Rowan