~Help~ converting Excel equation to vba function

[Ambrosia]

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(oCell))
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]

Actually peeps, nevermind! :

What I'm probably asking for is a lot.
Anyways... I'll be posting a new probably-easier-to-answer-question on
the next post instead.