bdsii
10-06-2009, 10:15 AM
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!
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!