ExcelBanter - ~Help~ converting Excel equation to vba function

Hello,

I dont know if this can be done...

Say this is my equation in a cell:

=PROPER(LEFT(A1,FIND(":",A1,4)-1))&IF(CountOccurrences(A1,",,")0,"
"&"("&CountOccurrences(A1,",,")+1&")",)

And say A1 is a text =gift:something free,, is good,, can be bad
because it may explode

The equation returns: 'Gift (3)'<in A1 and the number 3 refers to
items separated by ",,"

HOW DO I PUT THIS INTO VBA PROGRAMMING SO THAT I CAN JUST HIGHLIGHT ANY
CELLS AND APPLY THIS FORMULA THAT WILL GIVE THE RESULT? UM.... IS THIS
EVEN SIMPLE TO DO?

"Countoccurences" is a user-defined equation that returns the number of
times substring appears in str:

Function CountOccurrences(str, substring) As Long
Dim x As Variant
x = Split(str, substring)
CountOccurrences = UBound(x)

----

I thought this may be possible because I happened to look at this
highlight-'n-apply-trimcleaning-macro:

Sub trimclean()
Dim CTRg As Range
Dim oCell As Range
Dim Func As WorksheetFunction

Set Func = Application.WorksheetFunction

On Error Resume Next
Set CTRg = Selection.SpecialCells(xlCellTypeConstants, 2)
If Err Then MsgBox "No data to clean and Trim!": Exit Sub

For Each oCell In CTRg
oCell = Application.WorksheetFunction.Clean(Func.Trim(oCel l))
Next

End Sub

But I'm so confused with all the functions listed here; dont know how
to make my own function.

Please help!

--
Ambrosia
------------------------------------------------------------------------
Ambrosia's Profile: http://www.excelforum.com/member.php...o&userid=28146
View this thread: http://www.excelforum.com/showthread...hreadid=480664