Macro to convert formulas from 1 syntax to another

[Foodo]

Hello Excel Gurus,

I am trying to write a macro which will search through an entire worksheet for a specific formula (excel add-in) and replace it with another special formula (excel add-in). The added twist is that the syntax for each formula is different. Details as follows:

Formula A syntax "=FORMA(A,B,C,D)"
Formula B syntax "=FORMB(Param1#B;Param2#C;Param3#A;Param4#D,Param5#E)" whereby A,B,C,D parameters are same as from Formula A

Note in Formula B, the parameters are separated by a ";" as opposed to a "," and each parameter is defined (e.g., Param1#, etc.).

My original thought was to use InStr to find relative positions of the "," in Formula A and essentially define Dim's for each of the parameters in Formula A and then create a script that will insert Formula B using the Dim's.

Suggestions much appreciated.

 

[Foodo]

If I break the issue to several components, one obstacle appears to be replacing "," with ";". For some reason, the replace function does not like the ";". I tried to get around this by declaring a DIM using Chr(59) but still did not work.

I suppose I could use good old Ctrl+H but would prefer not to if possible since I am trying to create a self contained macro for users to run. Suggestions / tips on this component?