vba code counting specific word in multiples sheets

[azizls4]

hi
I want to counting number of a specific word in all sheets on column F only
I have a vba code but it only works on the first sheet, I need to modify it to counting number of ( word ) on all sheets

dim ran as range
dim cri as string, cri2 as string, cri3 as string
set ran = range(“f2:f100”)
cri = “*player*”
cri2 = “*teacher*”
cri2 = “*Engineer*”
userform1.label1 = worksheetfunction.countif(ran, cri )
userform1.label2 = worksheetfunction.countif(ran, cri2 )
userform1.label3 = worksheetfunction.countif(ran, cri3 )

 

[noveske]

For the results, do you want it to be a message box or have it display on a sheet somewhere?

 

[noveske]

So to test, I used Sheet 1, 2, 3.
Count Sheet is just there to count the occurrences. Not in the VBA code. Just used to verify it was correct.

Wasn't sure if the words needed the *'s or not. You can edit that in the script if you need to add them back.

I'm not sure if the code is just running choppy or if it's the computer I'm on. Might need some cleaning up. But it works.

Sub CountWords()
    Dim wordCount(1 To 3) As Long
    Dim cell As Range
    Dim word As Variant
    Dim ws As Worksheet
 
    wordCount(1) = 0 ' player
    wordCount(2) = 0 ' teacher
    wordCount(3) = 0 ' engineer
 
    For Each ws In ThisWorkbook.Sheets(Array(1, 2, 3))
        For Each cell In ws.Range("F2:F100")
            For Each word In Array("player", "teacher", "engineer")
                If InStr(1, cell.Value, word, vbTextCompare) > 0 Then
                    Select Case word
                        Case "player"
                            wordCount(1) = wordCount(1) + 1
                        Case "teacher"
                            wordCount(2) = wordCount(2) + 1
                        Case "engineer"
                            wordCount(3) = wordCount(3) + 1
                    End Select
                End If
            Next word
        Next cell
    Next ws
 
    Dim resultMsg As String
    resultMsg = "Occurrences:" & vbNewLine & _
                "Player: " & wordCount(1) & vbNewLine & _
                "Teacher: " & wordCount(2) & vbNewLine & _
                "Engineer: " & wordCount(3)
 
    MsgBox resultMsg, vbInformation, "Word Count Results (Version 1.2)"
 
    Exit Sub ' Exit the subroutine after displaying the message box
End Sub

Count Sheet:
The formula I used:

=SUMPRODUCT(COUNTIF(INDIRECT("Sheet"&{1,2,3}&"!F:F"), "player"))

=SUMPRODUCT(COUNTIF(INDIRECT("Sheet"&{1,2,3}&"!F:F"), "teacher"))

=SUMPRODUCT(COUNTIF(INDIRECT("Sheet"&{1,2,3}&"!F:F"), "engineer"))

If you use this script, this one will check for sheet Count or create one.
Then it will place the values in cells C3:C5.

Sub CountWords()
    Dim wordCount(1 To 3) As Long
    Dim cell As Range
    Dim word As Variant
    Dim ws As Worksheet
    Dim countSheet As Worksheet
 
    wordCount(1) = 0 ' player
    wordCount(2) = 0 ' teacher
    wordCount(3) = 0 ' engineer

    On Error Resume Next
    Set countSheet = ThisWorkbook.Sheets("Count")
    On Error GoTo 0
 
    If countSheet Is Nothing Then
        Set countSheet = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
        countSheet.Name = "Count"
        countSheet.Cells(1, 1).Value = "Word"
        countSheet.Cells(1, 2).Value = "Count"
    End If
 
    For Each ws In ThisWorkbook.Sheets(Array(1, 2, 3))
        For Each cell In ws.Range("F2:F100")
            For Each word In Array("player", "teacher", "engineer")
                If InStr(1, cell.Value, word, vbTextCompare) > 0 Then
                    Select Case word
                        Case "player"
                            wordCount(1) = wordCount(1) + 1
                        Case "teacher"
                            wordCount(2) = wordCount(2) + 1
                        Case "engineer"
                            wordCount(3) = wordCount(3) + 1
                    End Select
                End If
            Next word
        Next cell
    Next ws
 
    countSheet.Cells(3, 3).Value = wordCount(1) ' Player count
    countSheet.Cells(4, 3).Value = wordCount(2) ' Teacher count
    countSheet.Cells(5, 3).Value = wordCount(3) ' Engineer count
 

End Sub

 

[azizls4]

For the results, do you want it to be a message box or have it display on a sheet somewhere?

i want only number display on label1, label2 and label3 in userform , like this
userform1.label1 = worksheetfunction.countif(ran, cri )

for example
i will make label1( The number that i got from word player in all sheets ) and i make another label8 call it ( player ) near label1 , on userform

 

[noveske]

Can you share what/how the sheet is set up?
I guess I'm a little lost. From reading the original post, it sounded like you just wanted to have the script count the occurrences of your set words in range F2:F100 on all the sheets.
Isn't user form just for user input?

Can you show me the first sheet and user form?
I'm guessing it displays on the user form so you can view as you input.

Better yet, how about sharing the VBA that works. The complete one.

 

[noveske]

i want only number display on label1, label2 and label3 in userform , like this
userform1.label1 = worksheetfunction.countif(ran, cri )

for example
i will make label1( The number that i got from word player in all sheets ) and i make another label8 call it ( player ) near label1 , on userform

Sorry, I'm an idiot. I always tend to overthink and overcomplicate.
I reread the post.


It works, but only counts on the active sheet.
You want it to count all of the sheets.

Just change the range. Define worksheets. Then loop through each sheet.


Replace with this:

  Dim ws As Worksheet
    dim ran as range
    Dim cri As String, cri2 As String, cri3 As String
  

    cri = "*player*"
    cri2 = "*teacher*"
    cri3 = "*Engineer*"
 
 For Each ws In ThisWorkbook.Worksheets
        Set ran = ws.Range("F2:F100")
      
 
        ws.Label1 = WorksheetFunction.CountIf(ran, cri)
        ws.Label2 = WorksheetFunction.CountIf(ran, cri2)
        ws.Label3 = WorksheetFunction.CountIf(ran, cri3)
   Next ws

 

[azizls4]

Sorry, I'm an idiot. I always tend to overthink and overcomplicate.
I reread the post.


It works, but only counts on the active sheet.
You want it to count all of the sheets.

Just change the range. Define worksheets. Then loop through each sheet.


Replace with this:

  Dim ws As Worksheet
    dim ran as range
    Dim cri As String, cri2 As String, cri3 As String
 

    cri = "*player*"
    cri2 = "*teacher*"
    cri3 = "*Engineer*"
 
 For Each ws In ThisWorkbook.Worksheets
        Set ran = ws.Range("F2:F100")
     
 
        ws.Label1 = WorksheetFunction.CountIf(ran, cri)
        ws.Label2 = WorksheetFunction.CountIf(ran, cri2)
        ws.Label3 = WorksheetFunction.CountIf(ran, cri3)
   Next ws

I tried it, it doesn't work
i think here the problem

ws.Label3 = WorksheetFunction.CountIf(ran, cri3)

I tried another way
userform2.label1 = worksheetfunction.countif.(ran, cri)
The number that appears in the label on UserForm is 0

 

[azizls4]

this is work

Dim wb As Workbook
Dim ws As Worksheet
Dim ran As Range
Dim cri As String
Dim totalCount As Long

Set wb = ThisWorkbook 
cri = "*player*"
totalCount = 0

For Each ws In wb.Sheets
    Set ran = ws.Range("F2:F100")
    totalCount = totalCount + WorksheetFunction.CountIf(ran, cri)
Next ws

UserForm1.Label1.Caption =  totalCount