Home |
Search |
Today's Posts |
|
#1
|
|||
|
|||
How do I add the count function to the toolbar?
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can I count in an array based on a function? | Excel Worksheet Functions | |||
PivotTable Macro Reverts to Count Function | Excel Worksheet Functions | |||
Stubborn toolbars in Excel | Excel Discussion (Misc queries) | |||
How can I use count function in excel where I have several criter. | Excel Worksheet Functions | |||
Sum and Count Function | Excel Worksheet Functions |