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 |
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 |
All times are GMT +1. The time now is 04:14 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com