Converting Excel Formulas 2 VBA Code?

[indi visual]

I'm using these 3 formulas to split up a cell into three parts (in 3 diff cells).

1. everything before "{"

2. the contents in the middle of both braces

3. and everything after the last "}"

These formulas beneath work perfect in standard worksheet mode.


'=LEFT(A1,SEARCH("{",A1,1)-1)

'=IF(ISERROR(FIND("}",A1)),"",IF(ISERROR(FIND("{",A1)),"",LEFT(RIGHT(A1,LEN(A1)-FIND("{",A1)),FIND("}",RIGHT(A1,LEN(A1)-FIND("{",A1)))-19)))

'=AFTLAST(A1,"}")


The last one uses a UDF beneath:

Function AFTLAST(Cell As Range, findchar As String) As String

Application.Calculation = xlCalculationManual

For i = Len(Cell) To 1 Step -1

If Mid(Cell, i, 1) = findchar Then

AFTLAST = Mid(Cell, i + 1, 99)

Exit Function

End If

Next i

AFTLAST = Cell ' or aftlast="" depending on what you want

Application.Calculation = xlCalculationAutomatic

End Function


It makes the following string:

Teenage Mutant Nina {Turtles.comURLExtentionMarker} Turtle Power


Result in this in 3 pieces (excluding "URLExtentionMarker" purposely):

1. Teenage Mutant Nina

2. Turtles.com

3. Turtle Power


I can use these in excel all day long with no problem. But trying to use them in code is killing me.


Is there a general rule of thumb for converting these basic formulas for vba excel code use?


The first thing I think of to do is replace all the letter ranges to C's (to no avail)...


'=LEFT(C,SEARCH("{",C,1)-1)

'=IF(ISERROR(FIND("}",C)),"",IF(ISERROR(FIND("{",C)),"",LEFT(RIGHT(C,LEN(C)-FIND("{",C)),FIND("}",RIGHT(C,LEN(C)-FIND("{",C)))-19)))

'=AFTLAST(C,"}")


I have to play with the basic formula first to better understand the concept of what it does, and after I have the formula down... I'm dead in the water again because I have no idea how to use it in a "vba-like" form.


Any advice help will greatly be appreciated.


Is there any third party small plug in that just converts a basic formula to vba code form?, Or are the rules just simple enough to convert them yourself once one has the rules down?

 

[indi visual]

Nevermind on this one.


Resolved.. *(kinda)

''''''''''''''''''''''''''''''''

Dim C As Range

Dim OpenBrace As Integer, EndBrace As Integer

Dim Mystring As String

Dim MidString As String


For Each C In Range("A2")


Mystring = C.Value

OpenBrace = InStr(1, Mystring, "{")

EndBrace = InStr(1, Mystring, "}")

MidString = Mid(Mystring, OpenBrace, EndBrace - OpenBrace)


Range("B2").Value = Left(Mystring, OpenBrace - 1)


Range("C2").Value = Mid(Mystring, OpenBrace, EndBrace - OpenBrace + 1)


Range("D2").Value = Mid(Mystring, EndBrace + 1)


Next C

''''''''''''''''''''''''


You see I figured it out using common logic based on previous code provided to me.


It works tip top.... but yet still I still have no idea how I did it or why it worked : (