I'm having problems using the | character in VBA that I can use easily
in an Excel formula.

I use the following formula in Excel:

=REPLACE(a8,8,(SEARCH("|",a8)-SEARCH("=",a8)),"")

and when I convert this over to VBA, it gives me a warning that | is
not a valid char.

Then I tried using

=REPLACE(a8,8,(SEARCH(CHAR(124),a8)-SEARCH("=",a8)),"")

instead and the formula ends up coming across as FALSE as the final
result.

Anyone know what to do with this?

thanks,
skyler

Skyler,

I was able to use | in VBA.

One way to make sure that you are getting the correct character is to start
recording a macro and then F2 the cell with the formula and press enter.
Stop recording and you should get:

ActiveCell.FormulaR1C1 = _
"=REPLACE(RC[-1],8,(SEARCH(""|"",RC[-1])-SEARCH(""="",RC[-1])),"""")"

Then make another macro (as a test)

Sub Test()
MsgBox InStr(1, Range("A8").Value, "|")
End Sub

But insert the "|" by copying those three characters from the recorded
macro. Worked fine for me....

HTH,
Bernie
MS Excel MVP

"Skyler" wrote in message
om...
I'm having problems using the | character in VBA that I can use easily
in an Excel formula.

I use the following formula in Excel:

=REPLACE(a8,8,(SEARCH("|",a8)-SEARCH("=",a8)),"")

and when I convert this over to VBA, it gives me a warning that | is
not a valid char.

Then I tried using

=REPLACE(a8,8,(SEARCH(CHAR(124),a8)-SEARCH("=",a8)),"")

instead and the formula ends up coming across as FALSE as the final
result.

Anyone know what to do with this?

thanks,
skyler

Hi
try something like
activecell.formula="=REPLACE(a8,8,(SEARCH(""|"",a8 )-SEARCH
(""="",a8)),"""")"


-----Original Message-----
I'm having problems using the | character in VBA that I
can use easily
in an Excel formula.

I use the following formula in Excel:

=REPLACE(a8,8,(SEARCH("|",a8)-SEARCH("=",a8)),"")

and when I convert this over to VBA, it gives me a
warning that | is
not a valid char.

Then I tried using

=REPLACE(a8,8,(SEARCH(CHAR(124),a8)-SEARCH("=",a8)),"")

instead and the formula ends up coming across as FALSE as
the final
result.

Anyone know what to do with this?

thanks,
skyler
.