VBA to count number of cells with exact text

rbone

New Member
Joined
Aug 19, 2019
Messages
20
Hello,
I'm looking for a VBA script to find the total number of cells in a worksheet with the text "VOIP", but not "VOIP*", and output the count cell "S5"

Any assistance is greatly appreciated.

The following is giving me all instances of VOIP, including VOIP*

Code:
Range("S5").Select
    ActiveCell.FormulaR1C1 = "=COUNTIF(R1C1:R3057C13,""VOIP"")"
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
Hi & welcome to MrExcel
That will only give you the count of cells the contain exactly "VOIP", it won't count "VOIPS" or anything like that.
 
Upvote 0
Hi & welcome to MrExcel
That will only give you the count of cells the contain exactly "VOIP", it won't count "VOIPS" or anything like that.

Thank you for your response!
When I run the code it gives me the count for cells that have "VOIP" only and cells that have "VOIP CALL....." I'm trying to get the count for only "VOIP" My total should be 92, but it's giving me 106 since it's including the "VOIP CALL"
 
Upvote 0
It is not including cells that have VOIP CALL, just VOIP.
Note that it is not case sensitive


Book1
BCDEFGHIJKLMNOPQRS
1
2VOIP
3
4voip callvoip
5VOIP CALL2
Test
Cell Formulas
RangeFormula
S5=COUNTIF($A$1:$M$3057,"VOIP")
 
Upvote 0
If I create a small test sheet, it works perfectly.

When I run the macro on the full report it gives a greater number.

If I use Find all and check "Match entire cell contents" it gives the correct count, but I don't know how to creat a macro to use Find all and output the count.

Here's my full macro
Code:
Sub BackupFormat()

    Sheets("Backup").Select
    Cells.Select
    With Selection
        .VerticalAlignment = xlTop
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = False
        .MergeCells = False
    End With
 
    With ActiveWorkbook.Worksheets("Backup").Sort
        .SetRange Range("A1:IV320")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
    Range("R2").Select
    ActiveCell.FormulaR1C1 = "Backup Totals"
    Range("R3").Select
    ActiveCell.FormulaR1C1 = "WPHase1"
    Range("S3").Select
    ActiveCell.FormulaR1C1 = "=COUNTIF(R1C1:R9997C13,""WPH1"")"
    Range("S4").Select
    Range("R4").Select
    ActiveCell.FormulaR1C1 = "WPHase2"
    Range("S4").Select
    ActiveCell.FormulaR1C1 = "=COUNTIF(R1C1:R9997C13,""WPH2"")"
    Range("S5").Select
    Range("R5").Select
    ActiveCell.FormulaR1C1 = "VoiceOIP"
    Range("S5").Select
    ActiveCell.FormulaR1C1 = "=COUNTIF(R1C1:R9997C13,""VOIP"")"
    Range("S6").Select
End Sub
 
Upvote 0
I don't know how to creat a macro to use Find all and output the count.
You don't need to. The formula IS giving you the correct count.
It will ONLY count cells that contain "VOIP" and nothing else.
 
Upvote 0
You don't need to. The formula IS giving you the correct count.
It will ONLY count cells that contain "VOIP" and nothing else.

Thanks for the help! I must have some hidden values somewhere in the real report.
 
Upvote 0
I found the problem. I turned off filter mode and it counted properly. the script was correct, the "Find all" and manual count were missing the filtered cells.

I apologize for missing that.
 
Upvote 0
Try
Code:
Sub rbone()
   With Range("A1:M9997")
      .Replace "VOIP", True, xlWhole, , False, , False, False
      Range("T5").Value = .SpecialCells(xlConstants, xlLogical).Count
      Range("U5").Value = .SpecialCells(xlConstants, xlLogical).Address
      .Replace True, "VOIP", xlWhole, , False, , False, False
   End With
End Sub
This will give the count in T5 & the address of the cells in U5
 
Upvote 0
I found the problem. I turned off filter mode and it counted properly. the script was correct, the "Find all" and manual count were missing the filtered cells.

I apologize for missing that.

No problem, it's easily done.
Do you just want to count the visible cells?
 
Upvote 0

Forum statistics

Threads
1,213,559
Messages
6,114,302
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top