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
|