VBA - Len function to sort out specific letters

[Daktari]

I've a large task, which I'm splitting up into smaller pieces:
I'm trying to get VBA to output the number of plus signs in a cell (a workaround to how many other cells it adds into this cell).

Sub TEST()
'
' Makro4 Makro
'
Dim MyString As String
Dim StringLength As Integer
MyString = ActiveCell
StringLength = Len(MyString) - Len(Substitute(MyString, "+", ""))
MsgBox StringLength
'
End Sub

I get an error on the substitute line on my small code. If all worked according to plan I'd have the number of plus signs regardless of how many letters on the cell.

 

[Firefly2012]

The Substitute equivalent in VBA is the Replace function so:

StringLength = Len(MyString) - Len(Replace(MyString, "+", ""))

 

[Daktari]

Thanks for the quick reply there, firefly!

 

[hiker95]

Daktari,


The following changes do work:

Sub TEST()
'
' Makro4 Makro
'
Dim MyString As String
Dim StringLength As Integer
MyString = ActiveCell.Formula
StringLength = Len(MyString) - Len(Application.Substitute(MyString, "+", ""))
MsgBox StringLength
'
End Sub