Passing multiple strings to variable in Replace Function
 

I am trying to remove the operators and funtions from a formula string. I am
using the Replace Method. However I cannot seem to pass multiple items to
two variables and then have those variables work properly within the two
Replace functions. The goal being to start with a string that looks like
this:

"=$A7+$B$11+SUM($I$2:$I$6)+I6+CHOOSE(1,3,3,$I$6,K$ 3)"

and end up looking like this:

"=$A7 $B$11 $I$2:$I$6 I6 1,3,3,$I$6,K$3 "

Can anyone provide me with some guidance.

Thanks

EM

Sub FormulaZapper()
Dim CurrentFormula As String
Dim CurrentFormulaNoOperators As String
Dim CurrentFormulaNoFunction As String
Dim Operators As String
Dim AllFunctions

AllFunctions = Array("SUM", "CHOOSE")

Operators = "=" & "/" & "+" & "-" & "*" & "^" & & "(" & ")"

CurrentFormula = "=$A7+$B$11+SUM($I$2:$I$6)+I6+CHOOSE(1,3,3,$I$6,K$ 3)"

CurrentFormulaNoOperators = Replace(CurrentFormula, Operators, "")
CurrentFormulaNoFunction = Replace(CurrentFormula, AllFunctions, "")

End Sub

Passing multiple strings to variable in Replace Function
 

Sub FormulaZapper()
Dim CurrentFormula As String
Dim CurrentFormulaNoOperators As String
Dim CurrentFormulaNoFunction As String
Dim Operators As Variant
Dim AllFunctions As Variant
Dim s As String

AllFunctions = Array("SUM", "CHOOSE")

Operators = Array("=", "/", "+", "-", "*", "^", "(", ")")

CurrentFormula = "=$A7+$B$11+SUM($I$2:$I$6)+I6+CHOOSE(1,3,3,$I$6,K$ 3)"
s = CurrentFormula
For i = LBound(Operators) To UBound(Operators)
s = Replace(s, Operators(i), " ", 1, -1, vbTextCompare)
Next
CurrentFormulaNoOperators = s
For i = LBound(AllFunctions) To UBound(AllFunctions)
s = Replace(s, AllFunctions(i), " ", 1, -1, vbTextCompare)
Next
CurrentFormulaNoFunction = s
Debug.Print s
End Sub

Produces:
$A7 $B$11 $I$2:$I$6 I6 1,3,3,$I$6,K$3

--
Regards,
Tom Ogilvy


"ExcelMonkey" wrote in message
...
I am trying to remove the operators and funtions from a formula string. I
am
using the Replace Method. However I cannot seem to pass multiple items to
two variables and then have those variables work properly within the two
Replace functions. The goal being to start with a string that looks like
this:

"=$A7+$B$11+SUM($I$2:$I$6)+I6+CHOOSE(1,3,3,$I$6,K$ 3)"

and end up looking like this:

"=$A7 $B$11 $I$2:$I$6 I6 1,3,3,$I$6,K$3 "

Can anyone provide me with some guidance.

Thanks

EM

Sub FormulaZapper()
Dim CurrentFormula As String
Dim CurrentFormulaNoOperators As String
Dim CurrentFormulaNoFunction As String
Dim Operators As String
Dim AllFunctions

AllFunctions = Array("SUM", "CHOOSE")

Operators = "=" & "/" & "+" & "-" & "*" & "^" & & "(" & ")"

CurrentFormula = "=$A7+$B$11+SUM($I$2:$I$6)+I6+CHOOSE(1,3,3,$I$6,K$ 3)"

CurrentFormulaNoOperators = Replace(CurrentFormula, Operators, "")
CurrentFormulaNoFunction = Replace(CurrentFormula, AllFunctions, "")

End Sub