Solved: VBA to count characters in cell - getting error

[bdsii]

Hello all, hopefully someone here can help.

I want to count the number of instances a semicolon appears within a cell and can do that through a formula inside Excel but I do not want to add data to the spreadsheet, I want to do this through VBA. I will base action in an IF THEN statement depending on the number of semicolons contained in each cell.

The formula for counting the number of semicolons in a cell would be:


SemicolonCount = Len(E2) - (Substitute(E2, ";", ""))


but I get an error stating the following:
Compile error:
Sub or Function not defined

After searching the web I think the problem is with the Substitute portion of the formula. I thought I may be able to go to correct this by going to the Tools menu and then to References and select the Excel add-in or do this through the Add-In menu but both of those options are grayed out for me. This may be something that is done by our IT group that we cannot change.

Is there another formula that I can use that will be recognized by VB ?

Or is there a way to define the Function for this that could then be referenced by my Macro ?

I tried writing test code to have a Message Box pop-up to verify the Count function was working correctly. My plans were once I verified it was correct I would then use the code within the Main macro. The test code is below:


Sub Countsemicolon()
'
' Countsemicolon Macro
'
'
Range("E1").Select
Dim totalrows As Long
totalrows = ActiveSheet.UsedRange.Rows.Count

Dim Counter As Integer
For Counter = 1 To (totalrows - 2)

ActiveCell.Offset(1, 0).Select
Dim SemicolonCount As Long
SemicolonCount = Len(E2) - (Substitute(E2, ";", ""))
MsgBox ("Semicolon Count is: ") & SemicolonCount
Next Counter

End Sub




Any help or advice would be greatly appreciated.

thanks!

[nst1107]

I don't think the problem is an add-in. Your syntax is all wrong. Try this instead:SemicolonCount = Len([E2]) - Len(WorksheetFunction.Substitute([E2], ";", ""))

[bdsii]

Thanks Nate, that helped!

I also realized I used the constant E3 instead of a variable to pickup a new row each time. I changed formula to the one you provided and substituted ActiveCell.Value for E3. I also realized I had a Dim statement inside the loop and corrected that.

Below is the code that worked:


Sub Countsemicolon()
'
' Countsemicolon Macro
'
'
Range("E1").Select
Dim totalrows As Long
Dim SemicolonCount As Long
totalrows = ActiveSheet.UsedRange.Rows.Count

Dim Counter As Integer
For Counter = 1 To (totalrows - 1)
SemicolonCount = 0
ActiveCell.Offset(1, 0).Select
SemicolonCount = Len(ActiveCell.Value) - Len(WorksheetFunction.Substitute(ActiveCell.Value, ";", ""))
MsgBox ("Semicolon Count is: ") & SemicolonCount
Next Counter

End Sub



thanks so much for the help ! :-)
Marking this one solved!