Line in Macro doesn't work.

[Guest]

The following line in my macro won't work:

ActiveCell.FormulaR1C1 = "=IF(ISERROR(SEARCH(" -
",(RC[-7]))),(RC[-7]),-SUBSTITUTE((RC[-7])," - ",""))"

I have used the ActiveCell.Formula before but on little formulas. I want it
to write a formula in a cell that looks at a cell 7 columns to the left and
determine if it is text or not (imported data from SAP) and if it is text, I
want it to remove the "-" and put as a negative number.

I'm not very knowledgeable about VBA or macros. What I know is from what
I've copied from other recorded macros and pieced together.

Thanks for the help!!

 

[Bob Phillips]

ActiveCell.FormulaR1C1 = "=IF(ISERROR(SEARCH("" - ""," & _
"(RC[-7]))),(RC[-7])," & _
"-SUBSTITUTE((RC[-7]),"" - "",""""))"

when you have embedded quotes, you need to double-up.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

Thanks so much!!
V

ActiveCell.FormulaR1C1 = "=IF(ISERROR(SEARCH("" - ""," & _
"(RC[-7]))),(RC[-7])," & _
"-SUBSTITUTE((RC[-7]),"" - "",""""))"

when you have embedded quotes, you need to double-up.

--

HTH

RP
(remove nothere from the email address if mailing direct)

The following line in my macro won't work:

ActiveCell.FormulaR1C1 = "=IF(ISERROR(SEARCH(" -
",(RC[-7]))),(RC[-7]),-SUBSTITUTE((RC[-7])," - ",""))"

I have used the ActiveCell.Formula before but on little formulas. I want it
to write a formula in a cell that looks at a cell 7 columns to the left and
determine if it is text or not (imported data from SAP) and if it is text, I
want it to remove the "-" and put as a negative number.

I'm not very knowledgeable about VBA or macros. What I know is from what
I've copied from other recorded macros and pieced together.

Thanks for the help!!