How to use the Substitute Function in VBA - I'm struggling to get code right

[netrixuser]

I receive a csv file on a daily basis where I need to count the number of times a user has an entry where there are 4 or more commas in a particular column.
I received help from this site in another thread where I used the Substitute function to to replace Commas with "nothing" and then subtracted the original Length from the Substituted length using Len. I went on to shade cells where the Comma count was 4 or more. - All good.
That was in Excel I now need to use similar code in VBA to keep a running total for users where a cell has 4 or more commas in it.

For mock up sheet below:
For each user in column C (User) I need to look in Column D (Call ID) and count the number of times that there are 4 or more commas.
For Clarity - the actual csv file will have hashed numbers separated by a comma, not just commas - similar to this:
17f4ccadbb664c8988f2d275ad545335:8DAF21CD89D89A5, 319fe73aa0aa44fd8aea47c060b841b2:8DAF21D0454EC02, 3aeec5b125dd4ea5acaa3b0caac7762f:8DAF219692151C4, 489928d43e2f4f129027ae58988416f4:8DAF218F7C57D72, 551edc48b19b45f1ab52aea502b0a58f:8DAF21A6386C63C, 8d39898ed8124d9aa299168b5987e1ee:8DAF218AF85095B, b5acbf712a064538a354c84ff7ee74e4:8DAF21D2FD38695
Once I have the count I need to place the number, as a running total, in Column H (Commas) - next to the User Name.

	Recording	User	Call ID	Active Users	MP4	Nothing	Commas	CdT
	aswed.mp4	Bill	,,,,,,	Chris
	.mp4	Bill	,,	Barry
	.opus	Charlie	,,	Bill	2
	.mp4	Charlie	,,,,	Nicolas
	.opus	Charlie	,,,,	Charlie	1
	.mp4	Harry	,	Frank
	.opus	Sophie	,	Robin
	.opus	Sophie	,,,,	Gill		1
	.	Gill	,,	Hasan
	.	Pete	,,,	Harry	1
	.mp4	Barbara	,,,,	Sophie
	.	Barbara	,,,,	Paul
	.	Barbara	,,,,	Pete		1
	.	Barbara	,	George
	.mp4	Barbara	,	Barbara	2	3
	.mp4	Cyril	,,	Cyril	1	2

I have this working code that Counts the number of times a user has a Filename that ends in ".mp4" (Filename is in Column B - "Recording") and places a running total in column F (MP4)

For Each U In UsIDRnge
            
If Right(U.Offset(0, -1), 4) = ".mp4" Then
  UsidPlace = "F" & Application.Match(U, LookupRange, 0)
   CntMP4 = CntMP4 + 1
       Range(UsidPlace).Value = Range(UsidPlace).Value + CntMP4
   CntMP4 = 0
End If
 Next U

As you can see in the spreadsheet - the users who have ".mp4" in Column B have numbers, against their names, in Columns F (MP4)
This is a running total, so if I use the code on another csv file, Bill, for example, who has 2 entries of .mp4 above, might have 4 more instances of .mp4 so he would then have a running total of 6
As mentioned the above code is working

When I try to modify the IF statement above to use the Substitute function I get an error
I am trying to count the number of Commas in Column D and keep the total of those in Column H

        For Each U In UsIDRnge
        
If Len(U.Offset(0, 1)) - Len(WorksheetFunction.Substitute(U.Offset(0, 1), "", "", """")) > 4 Then
  UsidPlace = "H" & Application.Match(U, LookupRange, 0)
     CntComma = CntComma + 1
        Range(UsidPlace).Value = Range(UsidPlace).Value + CntComma
     CntComma = 0
End If

 Next U

I tried to use hard coded cell address instead of Variable and Offset but get the same error

If Len("D2") - Len(Application.WorksheetFunction.Substitute("D2", "", "", """")) > 4 Then

The error is Runtime Error 1004

"Unable to get the Substitute property of the WorkSheetFunction class" at the line of code above

Wondering if it cannot be done this way in VBA and I need another approach to count the number of times the cells containing Commas have more than 4 of them ?

All help gratefully received,

below is the full code including DIM and SET statements

[The two working IF statements are currently "remarked out"]

Sub Test_Count_Number_of_Alerts()
Dim U As Variant
Dim ws As Worksheet
    Set ws = ActiveWorkbook.Sheets("Sheet1")
Dim UsID As Variant 'User

Dim lastRow As Long
    lastRow = Cells(Rows.Count, 4).End(xlUp).Row
Dim UsIDRnge As Range
    Set UsIDRnge = ws.Range("C2:C" & lastRow) 'range containing End User IDs
         
Dim LookupRange As Range
Set LookupRange = ws.Range("E1:E50") 'Range containing All users
    

Dim UsidPlace As Variant 'Variable - to store location of count
'Dim i As Long

Dim CntMP4 As Long, CntBlank As Long, CntCDT As Long, CntComma As Long
CntMP4 = 0
CntBlank = 0
CntCDT = 0
CntComma = 0

        For Each U In UsIDRnge
        
        
'
'
'               If Right(U.Offset(0, -1), 4) = ".mp4" Then
'                    UsidPlace = "F" & Application.Match(U, LookupRange, 0)
'                        CntMP4 = CntMP4 + 1
'                             Range(UsidPlace).Value = Range(UsidPlace).Value + CntMP4
'                        CntMP4 = 0
'                End If
'

'                If Right(U.Offset(0, -1), 4) <> ".mp4" And Right(U.Offset(0, -1), 4) <> "opus" Then
'                     UsidPlace = "G" & Application.Match(U, LookupRange, 0)
'                        CntBlank = CntBlank + 1
'                            Range(UsidPlace).Value = Range(UsidPlace).Value + CntBlank
'                        CntBlank = 0
'                End If

                If Len(U.Offset(0, 1)) - Len(WorksheetFunction.Substitute(U.Offset(0, 1), "", "", """")) > 4 Then
                    UsidPlace = "H" & Application.Match(U, LookupRange, 0)
                        CntComma = CntComma + 1
                            Range(UsidPlace).Value = Range(UsidPlace).Value + CntComma
                        CntComma = 0
                End If

        Next U

Set ws = Nothing
Set UsIDRnge = Nothing
Set LookupRange = Nothing
End Sub

 

[Akuini]

I went with the Substitute option for now as it will enable me to quickly move onto finish the code, plan to try out the Split method as I think that may be quicker when I get sheets with a couple of thousand rows to process.
Thanks again

You're welcome, glad to help & thanks for the feedback.