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

 

[duggie33]

I got a little lost so I will try to add something that may help get you going in the right direction. You can count the commas and determine if it is greater than four using the SUBSTITUTE function similar to what you tried in your code. Maybe this will help you sort out your code...

Book1
	A	B	C
1	17f4ccadbb664c8988f2d275ad545335:8DAF21CD89D89A5, 319fe73aa0aa44fd8aea47c060b841b2:8DAF21D0454EC02, 3aeec5b125dd4ea5acaa3b0caac7762f:8DAF219692151C4, 489928d43e2f4f129027ae58988416f4:8DAF218F7C57D72, 551edc48b19b45f1ab52aea502b0a58f:8DAF21A6386C63C, 8d39898ed8124d9aa299168b5987e1ee:8DAF218AF85095B, b5acbf712a064538a354c84ff7ee74e4:8DAF21D2FD38695	6	TRUE
Sheet6

Cell Formulas
Range	Formula
B1	B1	=LEN(A1)-LEN(SUBSTITUTE(A1,",",""))
C1	C1	=LEN(A1)-LEN(SUBSTITUTE(A1,",",""))>4

Hope that helps,

Doug

 

[Akuini]

I need another approach to count the number of times the cells containing Commas have more than 4 of them

Try using split function:

Sub try()

tx = "asd,fr,g,h,y,sdfs,fd"
ary = Split(tx, ",")
Debug.Print UBound(ary)  'returns 6

End Sub

 

[netrixuser]

I got a little lost so I will try to add something that may help get you going in the right direction. You can count the commas and determine if it is greater than four using the SUBSTITUTE function similar to what you tried in your code. Maybe this will help you sort out your code...

Book1
	A	B	C
1	17f4ccadbb664c8988f2d275ad545335:8DAF21CD89D89A5, 319fe73aa0aa44fd8aea47c060b841b2:8DAF21D0454EC02, 3aeec5b125dd4ea5acaa3b0caac7762f:8DAF219692151C4, 489928d43e2f4f129027ae58988416f4:8DAF218F7C57D72, 551edc48b19b45f1ab52aea502b0a58f:8DAF21A6386C63C, 8d39898ed8124d9aa299168b5987e1ee:8DAF218AF85095B, b5acbf712a064538a354c84ff7ee74e4:8DAF21D2FD38695	6	TRUE
Sheet6

Cell Formulas
Range	Formula
B1	B1	=LEN(A1)-LEN(SUBSTITUTE(A1,",",""))
C1	C1	=LEN(A1)-LEN(SUBSTITUTE(A1,",",""))>4

Hope that helps,

Doug

Hi Doug, thanks for your reply - I was trying to portray that I was using already using "Len( ) - Len(Substitute( , ,))" in Excel to get the number of Commas but now wanted to use the same method via VBA, and was having trouble with the code running, even though the code "looked" right and compiled without errors.

 

[netrixuser]

Try using split function:

Sub try()

tx = "asd,fr,g,h,y,sdfs,fd"
ary = Split(tx, ",")
Debug.Print UBound(ary)  'returns 6

End Sub

Thank you Akuini - I'm off to try that now, although not too much time to "play" with code today.

Many thanks

 

[netrixuser]

Thank you Akuini - I'm off to try that now, although not too much time to "play" with code today.

Many thanks

I fear I was a little hasty !! I'm having issues integrating this into my code to get the results.

To try and explain a little better:

Usernames who have had an alert raised are in Column C - This Column is part of the received csv file
The Alert details, containing phrases separated by commas is in Column D - This column is part of the received csv file
A managed List of usernames are in Column E - These details will actually be on another Sheet in the Workbook into which the csv is imported.

For every user that appears in Column C in the csv file, I want to look into the corresponding Cell in Column D and count the number of Commas in that Cell - I don't need to know how many Commas there are, just if there are 4 or more Commas present. If this is true, I want to place a "1" next to that user in Column H. This needs to be in the same Row that the user appears in Column E and not the same Row where the Commas were counted.

If the Same user has another alert further down, where there are 4 or more commas, then that figure, in Column H, will increment to "2"

When the next csv file is imported into the Workbook, and the same user has another alert where there are 4 or more Commas, the corresponding Cell in Column H will increment to "3"

Below is the code using Substitute (which throws the error) with the explanations above in the comments - if the code using the Split function could be written to fit in I would be very grateful.

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 User IDs - this is part of the received csv file
         
Dim LookupRange As Range
Set LookupRange = ws.Range("E1:E50") 'Range containing All users  - a managed list of ALL users, will eventually be in another Sheet in same Workbook, Currently Column E

Dim UsidPlace As Variant 'Variable - use the Match function to find the User ID from Column C in Column E
'Dim i As Long

Dim CntComma As Long

CntComma = 0


For Each U In UsIDRnge 'Look at users in Column C
                If Len(U.Offset(0, 1)) - Len(WorksheetFunction.Substitute(U.Offset(0, 1), "", "", """")) > 4 Then 'Offset U (Column C) 1 to the right - the Column with Commas in it and use Len()/Len(Substitute()) to replace Commas with nothing and calculate the difference - this bit fails
                    UsidPlace = "H" & Application.Match(U, LookupRange, 0) 'Match name in Column C (Variable U) in LookupRange (Column E) and Prefix result with H
                        CntComma = CntComma + 1 'Increment CntComma Variable to 1
                            Range(UsidPlace).Value = Range(UsidPlace).Value + CntComma 'Set value in "H" + UsidPlace (ie Now a Cell address) to 1 plus whatever value was in that Cell
                        CntComma = 0 'Set CntComma Variable back to zero before next Loop

                End If
Next U

Set ws = Nothing
Set UsIDRnge = Nothing
Set LookupRange = Nothing

End Sub

If I run the Code above but replace this Substitute line

If Len(U.Offset(0, 1)) - Len(WorksheetFunction.Substitute(U.Offset(0, 1), "", "", """")) > 4 Then

With this .MP4 Line, to count the number of times there is a file ending in .mp4 in Column B, it works as I need it to

If Right(U.Offset(0, -1), 4) = ".mp4" Then

So if you could show me how to use the Split function within my existing IF/Then I would be made up !!

Mant thanks in advance,

Netrix

 

[Akuini]

Your code doesn't replace comma with nothing:

                If Len(U.Offset(0, 1)) - Len(WorksheetFunction.Substitute(U.Offset(0, 1), "", "", """")) > 4 Then 'Offset U (Column C) 1 to the right - the Column with Commas in it and use Len()/Len(Substitute()) to replace Commas with nothing and calculate the difference - this bit fails

it should be:

                If Len(U.Offset(0, 1)) - Len(WorksheetFunction.Substitute(U.Offset(0, 1), ",", "")) > 4 Then

or you can use split function like this:

ary = Split(U.Offset(0, 1), ",")
If UBound(ary) > 4 Then '

 

[netrixuser]

                    If ary(Split(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

Your code doesn't replace comma with nothing:

                If Len(U.Offset(0, 1)) - Len(WorksheetFunction.Substitute(U.Offset(0, 1), "", "", """")) > 4 Then 'Offset U (Column C) 1 to the right - the Column with Commas in it and use Len()/Len(Substitute()) to replace Commas with nothing and calculate the difference - this bit fails

it should be:

                If Len(U.Offset(0, 1)) - Len(WorksheetFunction.Substitute(U.Offset(0, 1), ",", "")) > 4 Then

or you can use split function like this:

ary = Split(U.Offset(0, 1), ",")
If UBound(ary) > 4 Then '

You are a star, thank you !

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

Regards
Netrix

 

[Alex Blakenburg]

Using a dictionary should be much quicker than what you are currently using but let us know how go with the current method once you test it on a larger number of rows.

 

[netrixuser]

Using a dictionary should be much quicker than what you are currently using but let us know how go with the current method once you test it on a larger number of rows.

Thanks Alex,
I have not used an Array before and currently know nothing about the UBound( ary) bit, other than it works !! So much reading still to be done, and now I have a Dictionary "function/feature" to research too !

I am enjoying this roller-coaster of a VBA learning curve, but it seems to be getting steeper