Thank you for being a valued part of the CNET community. As of December 1, 2020, the forums are in read-only format. In early 2021, CNET Forums will no longer be available. We are grateful for the participation and advice you have provided to one another over the years.

Thanks,

CNET Support

General discussion

Excel Formula Counting Question

Sep 10, 2004 12:46AM PDT

This is kinda an odd question but, is there anyway excel can count the number of bold words in a row. I have 12 consultants working this week on site out of 15. I have the 12 in bold and the 3 not in bold. I dont want to count with my hands on the screen for each week. Can i get excel to count the bold across the row? thank you

matt

Discussion is locked

- Collapse -
Re: Excel Formula Counting Question
Sep 10, 2004 6:23AM PDT

Write some VBA-code. Loop through the cells, and count the number where the bold-attribute is set or unset. Try to make a function like count_bold (range) or attrib_count (range, attrib_code, attrib_value) to make it more general.

The coding would be very much like the coding provided by swisse in http://reviews.cnet.com/5208-6129-0.html?forumID=29&threadID=32317&messageID=371236
This sums the values in the cell with a certain background color. Summing and counting aren't very different, nor are background-color and bold/normal attribute.

Hope this helps, and let us know.


Kees

- Collapse -
Re: Excel Formula Counting Question
Sep 10, 2004 7:12AM PDT

Kees

Thanks for responding, Is there anyway possible to provide a step by step way of implementing the code that swisse or u wrote up. I am not that familiar with VBA yet. Thanks for your help already

Thanks, Matt

- Collapse -
Re: Excel Formula Counting Question
Sep 10, 2004 3:09PM PDT

Simply copy this code into a blank procedure in the Microsoft Visual Basic for Apps and save it.
Here is the code you asked for:

Public Sub AddAllBold() 'Procedure adding all bold words
Dim c As Integer 'This is your counter
Dim i As Integer 'array counter
Dim rc As Integer 'row counter
Dim h As Integer
Dim x 'array
c = 0 'Initialize c to zero
i = 0 'initialize i to zero
rc = 0 'Initialize rc to zero

x = Array("B2", "B3") 'You could add as many rows you want

Range("B2").Select 'If you start the row at B2
Do Until ActiveCell.Value = ""
rc = rc + 1
ActiveCell.Offset(1, 0).Select
Loop

Range(x(i)).Select
For h = 1 To rc
Do Until ActiveCell.Value = ""
If ActiveCell.Font.Bold = True Then
c = c + 1
End If
ActiveCell.Offset(0, 1).Select
Loop
ActiveCell.Value = c
c = 0
i = i + 1
If i >= rc Then Exit Sub
Range(x(i)).Select
Next h
End Sub

- Collapse -
Re: Excel Formula Counting Question
Sep 10, 2004 9:22PM PDT

I should have explained earlier how to assign a macro to a button. Ok, we'll do it step by step.

1. Open Excel.
2. Once in Excel press Alt + F11, then MS VBA opens.
3. On the left side of MS VBA you will see the Project Explorer. Look for the item ThisWorkbook and doubleclick it.
4. Click the menu item Insert, then click Procedure.
5. A small window opens asking for the name of the procedure. Write AddAllBold. For the Type choose Sub, and Scope Public. Then click OK.
6. The procedure AddAllBold() will be generated automatically and will appear at the center where you paste this code:

Dim c As Integer 'This is your counter
Dim i As Integer 'array counter
Dim rc As Integer 'row counter
Dim h As Integer
Dim x 'array
c = 0 'Initialize c to zero
i = 0 'initialize i to zero
rc = 0 'Initialize rc to zero

x = Array("B2", "B3") 'You could add as many rows you want

Range("B2").Select 'If you start the row at B2
Do Until ActiveCell.Value = ""
rc = rc + 1
ActiveCell.Offset(1, 0).Select
Loop

Range(x(i)).Select
For h = 1 To rc
Do Until ActiveCell.Value = ""
If ActiveCell.Font.Bold = True Then
c = c + 1
End If
ActiveCell.Offset(0, 1).Select
Loop
ActiveCell.Value = c
c = 0
i = i + 1
If i >= rc Then Exit Sub
Range(x(i)).Select
Next h

7. Make sure that the code is inside the Public Sub AddAllBold() ... End Sub.
8. Save your work, and go back to Excel.
9. Remember we are now in Excel. Now go to the menu item View, click it, go downwards, and click the item Toolbars. Put a check before the item Forms. The Forms window now appears on the screen.
10. On the Forms window click the button object, then bring the cursor on the Worksheet. Notice that the cursor has changed into a small cross.
11. Press the left mouse button, drag it downwards then to the right, and release it. A window with the name Assign Macro opens asking you to assign a macro to this button.
12. In the middle window you will see the item ThisWorkbook.AddAllBold. Choose this one, mark it, then click OK. Now you have assigned the macro to a button.

Hope I have explained thoroughly, and that you understood me.

- Collapse -
Re: Excel Formula Counting Question
Sep 11, 2004 1:09AM PDT

Swisse,

Wouldn't it be easier to use if you put the code inside a user-defined function, so you could simply use a formula =countbold(). Or would it need to have the range as a parameter, giving =countbold (B2:B5)? But I'm certain you can code that also.

Somehow, it seems more 'Excel-like' to me that way.

Kees

- Collapse -
Re: Excel Formula Counting Question
Sep 11, 2004 4:13AM PDT

Kees,
I don't think it's possible. You can use COUNTA(B2:K2) to count the cells in a row containing text, but you can't use it to count the cells containing bold text. Maybe there is some other way around, which I don't know.
The code that I wrote works fine. It will start with B2 if you start the row at B2, or you could change that. It will then check if the content of the cell is a bold text, which will count as one. If not it will simply jump to the next cell to the right until all cells are checked if they contain data/number. At the end of the row where there is a blank cell, it will stop counting and post the total on the next blank cell. Then it will jump to the next line which is B3 and then start all over again. If you add more entries to the array then it will go down further, until it finds the last entry in the array.
Hope this clarifies what the code actually does.

Swisse

- Collapse -
And I think it can be done ...
Sep 11, 2004 5:17AM PDT

or I wouldn't have suggested it.

The very first hit on my excel vba function range in google was http://www.ozgrid.com/Excel/count-sum-cell-color.htm which gives an example of a function counting the number of cells with a specified background color in a range given as parameter.

You won't find it very difficult to change the boolean expression testing the background color of a cell to one testing the bold property of the font (method, I assume, but I'm not sure of that), i.e. font.bold, of the cell, given this example. Which makes =countbold (range) a viable formula, just like =countcolor (refcell, range) in this example.

I think taylorm would appreciate a fully working example in stead of this hint, but I'll leave that to you. I'm a very beginning Excel/Word programmer (only know my way around MS Access).

Hope you agree with my opinion.


Kees

- Collapse -
Re: And I think it can be done ...
Sep 11, 2004 6:33AM PDT

What you mean is a Excel VBA function not a excel function. A Excel VBA macro can be a function or a sub, but not a function in excel where you could just put equations in it. In your example instead of using ColorIndex replace it with bold like Font.Bold. With that code in your link you have to define a range or mark an area to define a range. What taylorm asked for was to make a macro that would count the number of bold text in a row, place the total at the end of the row, jump to the next line count again, and so on and so forth until all lines have been counted. That was what my code was for. I have tested my code, and it worked just as I wanted it to.

- Collapse -
Re: And I think it can be done ...
Sep 12, 2004 12:38PM PDT

i just want to thank you guys for responding as you have...tomorrow at work i am going to attempt to get this done. As you can tell im no programmer just a business guy. So we will see how it goes, i will post as i go, thanks again

matt

- Collapse -
Re: And I think it can be done ...
Sep 13, 2004 12:37AM PDT

One more question, I have entered the data in. and followed the directions to add the macro. How do i get it to run and where do i have it show the results. How can i make sure it uses the rows 3-420? Thanks so much

Matt

- Collapse -
Re: Excel Formula Counting Question
Sep 13, 2004 12:19AM PDT

ok i am on the second part in MVB, If i want to do a range say B2-B420, how would i be entering this paragraph. I woul leave out the array correct, and move to the Range instead? Thanks for your continued help

Matt

- Collapse -
Re: Excel Formula Counting Question
Sep 13, 2004 12:39AM PDT

ok correction, i figure out how to run the macro, new problem, i have an error message that says subscript out of range. What might this problem be?


Thanks

Matt

- Collapse -
Re: Excel Formula Counting Question
Sep 13, 2004 5:18AM PDT

Hello Matt,
Sorry to have you kept waiting. I just came home from work. OK, the error "subscript out of range" means that you have not added additional entries to the array. But that would be a lot of work for you to do. Imagine you will have to type B2 until B420 to complete the array. I have rewritten the code to accommodate this need. Instead the code will go downwards the B column until the last entry. So in the future even if you have a thousand rows in column B you won't be worrying of ever changing the code to accommodate the new entries. So I'm giving you the modified code. Just copy the code and replace the old one within the Public Sub AddAllBold() ... End Sub.
Tell me how it goes.

Here's the modified code:

Dim c As Integer 'This is your counter
Dim rc As Integer 'row counter
Dim h As Integer
Dim d As Integer
c = 0 'Initialize c to zero
rc = 0 'Initialize rc to zero
d = 2 'This equates to B2

Range("B2").Select 'If you start the row at B2
Do Until ActiveCell.Value = ""
rc = rc + 1
ActiveCell.Offset(1, 0).Select
Loop


Range("B2").Select
For h = 1 To rc
Do Until ActiveCell.Value = ""
If ActiveCell.Font.Bold = True Then
c = c + 1
End If
ActiveCell.Offset(0, 1).Select
Loop
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = c
c = 0
Range("B" & d + h).Select
Next h

- Collapse -
Re: Excel Formula Counting Question
Sep 13, 2004 5:39AM PDT

Swisse

This macro ran without any error messages! But there are still a few things im unsure about. The first is, is there a way to get this code to count these bolds and then put the count in a column or last row of the data? Second it appears that although the macro ran it only counte 1 bold. My spreadsheet is setup with the names of consultants across the top row. As you scroll down each week is listed with the days in between, across the row is the location of where these consultants are. The locations are bold which is what this code shoul be counting. This allows me to know right away which consultants are working on which weeks an days out of the total group. Finally does it matter which row we start the macro at aka b2? my dates are in the A column. Thank you for bearing with me so far you have been great...thanks again

matt

- Collapse -
Re: Excel Formula Counting Question
Sep 13, 2004 5:44AM PDT

Upon further review i have noticed that when entering B as the ranged it counts all bold in the column of B perfectly. I need it to count the bold words from columns E-AE. I hope I am making sense. Thanks, matt

- Collapse -
Re: Excel Formula Counting Question
Sep 13, 2004 6:24AM PDT

OK, I have again rewritten the code. This should be counting only bold text. Even if the names are all in capitals but they are not bold they will not be counted. At the end of each row there is a subtotal of the counted bold text, and at the end of the row for example A450 the total of the counted bold text will be posted. One thing to note there should be no empty cell in your workarea, otherwise the macro will stop counting. You could start the macro at A1 if you wish.

Here the modified code again:

Dim c As Integer 'This is your counter
Dim rc As Integer 'row counter
Dim h As Integer
Dim d As Integer
Dim total As Integer
c = 0 'Initialize c to zero
rc = 0 'Initialize rc to zero
d = 1 'This equates to B2

Range("A1").Select 'If you start the row at A1
Do Until ActiveCell.Value = ""
rc = rc + 1
ActiveCell.Offset(1, 0).Select
Loop


Range("A1").Select
For h = 1 To rc
Do Until ActiveCell.Value = ""
If ActiveCell.Font.Bold = True Then
c = c + 1
total = total + 1
End If
ActiveCell.Offset(0, 1).Select
Loop
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = c
c = 0
Range("A" & d + h).Select
Next h

ActiveCell.Offset(1, 0).Select
ActiveCell.Value = "Total"
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = "="
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = total

- Collapse -
Re: Excel Formula Counting Question
Sep 13, 2004 6:37AM PDT

Swisse,
I believe possible that the macro is backwards with rows an columns...it adds the column not each row across. I can tell that we are really close to solving this problem and I just want to say thank you for this help it has really been awesome. Im hoping to get this figured out if you need any more explanation please let me know.

- Collapse -
Re: Excel Formula Counting Question
Sep 13, 2004 7:01AM PDT

Matt,
Could you please run the macro again, mark the area including the totals and send me an email at aylilynandrizzo@freesurf.ch? Didn't you change anything in the code? You should have not if you did.

Swisse

- Collapse -
Re: Excel Formula Counting Question
Sep 13, 2004 7:21AM PDT

Email sent with the original sheet to give you a better idea what im working with..if you add that macro it will help show what happens..thanks

matt

- Collapse -
Re: Excel Formula Counting Question
Sep 13, 2004 7:49AM PDT

Matt,
You have many empty cells between bold text. The macro will stop counting once it encounters empty cells. I'll rewrite the code tomorrow. I'll have to go to bed now. Don't worry after we're through with this problem I'll delete the sheet. Until tomorrow ...

Swisse

- Collapse -
Re: Excel Formula Counting Question
Sep 15, 2004 1:00AM PDT

Swisse

I put that code in and it run exactly how we want it to. My only problems still are the count totals. They seem to be different than the totals counted by hand...did u find this problem too? Let me know..maybe i can send you an updated spreadsheet.

Matt

- Collapse -
Re: Excel Formula Counting Question
Sep 14, 2004 10:27AM PDT

Matt,
I've rewritten the code, and adjusted it to apply to your table. I've made the macro count the cells between column E and column AP - this is just arbitrary. That means if you later add more columns to your workarea then you could extend beyond the AP column, if your workarea extends beyond AP. This is the line cc = Range("E1:AP1").Count. The total of each row falls on column AR and is also in bold text. The total of all bold text is at the bottom of the workarea somewhere at E428 or E420 and it is in bold text too and underlined. So here again the modified code. Just replace the old code between the Public AddAllBold() ... End Sub with this new one.

Dim c As Integer 'bold text counter
Dim rc As Integer 'row counter
Dim cc As Integer 'column counter
Dim h As Integer
Dim total As Integer 'total number of bold text
Dim nc As Integer 'new counter
Const d As Integer = 5

c = 0 'Initialize c to zero
rc = 0 'Initialize rc to zero
cc = 0
nc = 0

Range("AR1").Select
With ActiveCell.Font
.Bold = True
End With
ActiveCell.Value = "Subtotal"

Range("A5").Select 'If you start the row at A
Do Until ActiveCell.Value = ""
rc = rc + 1
ActiveCell.Offset(1, 0).Select
Loop

cc = Range("E1:AP1").Count 'counts cells between E1 and AP1

Range("E5").Select
For h = 1 To rc
Do While nc < cc
If ActiveCell.Value <> "" And ActiveCell.Font.Bold = True Then
c = c + 1
total = total + 1
End If
nc = nc + 1
ActiveCell.Offset(0, 1).Select
Loop
ActiveCell.Offset(0, 1).Select
With ActiveCell.Font
.Bold = True
End With
ActiveCell.Value = c
c = 0
nc = 0
Range("E" & d + h).Select
Next h

ActiveCell.Offset(1, 0).Select
ActiveCell.Value = "Total"
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = "="
ActiveCell.Offset(0, 1).Select
With ActiveCell.Font
.Bold = True
.Underline = True
End With
ActiveCell.Value = total

Hope this solves the problem. If you have any more suggestions just tell me. Please post as soon as possible.

Swisse

- Collapse -
Re: Excel Formula Counting Question
Sep 15, 2004 6:20AM PDT

Matt,
Yes please send me an updated spreadsheet.

Swisse

- Collapse -
Re: Excel Formula Counting Question
Sep 16, 2004 5:28AM PDT

Matt,
I checked your spreadsheet and I noticed that you have cells filled with black color, and hidden ones. I managed to remove the black color from these cells, and to unhide the hidden cells, and tried to add them manually. The macro was right in its calculation! Do you want the macro not to count the filled cells and the hidden ones?

Swisse

- Collapse -
Re: Excel Formula Counting Question
Sep 18, 2004 6:53AM PDT

Matt,
I revised the code again and instead of the first row coming up with a sum of 22 it has now 18. I also did a manual count which also resulted to 18. I included the cells filled with black since some of them are also in bold text. I included a new procedure: Sub CloseHiddenColumns(). This procedure finds hidden columns, unhides them, and deletes them. I have to delete them so that they won't get counted - that's why the total of the first row summed up to 18 instead of the earlier 22. You have to make the entries like Jan Q1 '04 YTD and 0%s in columns AD and AG regular not bold text, otherwise they too would be counted and you would have false results. Anyway if the results don't tally yours just post again. And tell me what should still be done. So here again the revised code:

Dim c As Long 'bold text counter
Dim rc As Long 'row counter
Dim cc As Long 'column counter
Dim h As Long
Dim total As Long 'total number of bold text
Dim nc As Long 'new counter
Dim Col As Range
Const d As Integer = 5

Public Sub AddAllBold()

c = 0 'Initialize c to zero
rc = 0 'Initialize rc to zero
cc = 0
nc = 0
h = 0

Range("AR1").Select
With ActiveCell.Font
.Bold = True
End With
ActiveCell.Value = "Subtotal"

Range("A5").Select 'If you start the row at A
Do Until ActiveCell.Value = ""
rc = rc + 1
ActiveCell.Offset(1, 0).Select
Loop

cc = Range("E1:AP1").Count 'counts cells between E1 and AP1

Range("E5").Select
For h = 1 To rc
On Error Resume Next
For nc = 1 To cc
If ActiveCell.Value <> "" And ActiveCell.Font.Bold = True Then
c = c + 1
total = total + 1
End If
ActiveCell.Offset(0, 1).Select
Next nc
ActiveCell.Offset(0, 1).Select
With ActiveCell.Font
.Bold = True
End With
ActiveCell.Value = c
c = 0
nc = 0
Range("E" & d + h).Select
Next h

ActiveCell.Offset(1, 0).Select
ActiveCell.Value = "Total"
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = "="
ActiveCell.Offset(0, 1).Select
With ActiveCell.Font
.Bold = True
.Underline = True
End With
ActiveCell.Value = total

c = 0
total = 0

End Sub

Public Sub CloseHiddenColumns() 'the new procedure

Range("A1:IV500").Select
For Each Col In ActiveSheet.UsedRange.Columns
If Col.Hidden Then
Col.Hidden = False
Col.Delete
End If
Next Col

End Sub

Swisse