Count cells with a Certain Custom Number Format?

Mike

I see a couple of ways. Given the setup in this sheet where the data is in column B and the yellow cells are formatted with your Custom Format and the others are not.

</td></tr></table></td></tr></table>




Method 1 (vba)

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> Count_Format()<br>    <SPAN style="color:#00007F">Dim</SPAN> myFormat <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> myRange <SPAN style="color:#00007F">As</SPAN> Range, c <SPAN style="color:#00007F">As</SPAN> Range<br>    <SPAN style="color:#00007F">Dim</SPAN> Counter <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    myFormat = "0.00""*"""<br>    <SPAN style="color:#00007F">Set</SPAN> myRange = Range("B3", Range("B" & Rows.Count).End(xlUp))<br>    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> c <SPAN style="color:#00007F">In</SPAN> myRange<br>        <SPAN style="color:#00007F">If</SPAN> c.NumberFormat = myFormat <SPAN style="color:#00007F">Then</SPAN><br>            Counter = Counter + 1<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN> c<br>    MsgBox Counter<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

Method 2 (GET.CELL)
Assuming the next column (C in this case) is available to use as a helper column.

a) Insert|Name|Define...|Names in workbook: myNumFormat|Refers to: =GET.CELL(7,OFFSET(INDIRECT("RC",FALSE),0,-1))|OK

b) Formula in C3 (copied down): =myNumFormat

c) Your format typed into F1

d) Formula as shown in E1

I just realised that my suggestion counts the cells with the Custom Format. If you only want to count cells with that Format and that contain numbers (or are not blank) then slight modifications would be needed. Post back with more details if you need help with that.

mgirvin,




The function in cell C3:
=CountMyFormat(A1:A10)



Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Adding the Macro
1. Copy the below Function code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel

Peter_SSs,

Wow! Once again you have helped me out greatly! The GET.CELL is just what I needed. I did not know about this old Macro 4 trick. I just did a Google search and found some great infro about this (the first hit was a Mr Excel Message Board post from 2002)!!!!!

Totally cool trick!

hiker95,

Thanks for your code!

mgirvin said:

I just did a Google search and found some great infro about this (the first hit was a Mr Excel Message Board post from 2002)!!!!!

Click to expand...

Probably this one which I referred to in drafting my reply.

Perhaps a neater way on the sheet would have been this:

That's it! That is the post I found also.

I just took your advice about taking into consideration the fact that some of the cells could be blank and have the Custom Number Formatting. Those cells should not be counted and so I used:

=--AND(C4<>"",myNumFormat="0.00""*""")

where C4 is one cell to the left of the cell with the formula.

Thanks again!

mgirvin said:

I just took your advice about taking into consideration the fact that some of the cells could be blank and have the Custom Number Formatting. Those cells should not be counted and so I used:

=--AND(C4<>"",myNumFormat="0.00""*""")

Click to expand...

Yes, that's the sort of thing I had in mind. Or the same thing a different way:
=(C4<>"")*(myNumFormat="0.00""*""")

I messed around with this some more (because it is such a great trick) and instead of using:

=GET.CELL(7,OFFSET(INDIRECT("RC",FALSE),0,-1))

I have replaced the "one cell to my left" formula construct:

OFFSET(INDIRECT("RC",FALSE),0,-1)

With:

!A4

given that the cell B4 was selected when I opened define name to get this shorter version:

=GET.CELL(7,!A4)

I have used this "global relative cell reference" trick in other defined names (and it works just fine), but so far, it also seems to be working with this xl 4 macro function, GET.CELL.

Thanks again, Peter_SSs!