I want the following formula in a cell in colum L by using a macro.

=IF(J6="bill",SUM(INDIRECT(SUBSTITUTE(C6,"/","_"))),VLOOKUP(C6,stock,5,FALSE))

Here is my code used in VBA:
Activecell.Formula = "=IF(" & ACTIVECELL.OFFSET(0,-2).ADDRESS & "=" &
""bill"" & ",SUM(INDIRECT(SUBSTITUTE(C6," & ""/"" & "," & ""_"" &
"))),VLOOKUP(" & ACTIVECELL.OFFSET(0,-9).ADDRESS & ",stock,5,FALSE))"

My problem is that at the ""BILL"" vba gives an error "expected: End of
statement"

Where am i missing the point?

Thx

ActiveCell.FormulaR1C1 =
"=IF(RC[-2]=""bill"",SUM(INDIRECT(SUBSTITUTE(RC[-9],""/"",""_""))),VLOOKUP(R
C[-9],stock,5,FALSE))"

HTH
--
AP

"lasca" a écrit dans le message de
...
I want the following formula in a cell in colum L by using a macro.


=IF(J6="bill",SUM(INDIRECT(SUBSTITUTE(C6,"/","_"))),VLOOKUP(C6,stock,5,FALSE
))

Here is my code used in VBA:
Activecell.Formula = "=IF(" & ACTIVECELL.OFFSET(0,-2).ADDRESS & "=" &
""bill"" & ",SUM(INDIRECT(SUBSTITUTE(C6," & ""/"" & "," & ""_"" &
"))),VLOOKUP(" & ACTIVECELL.OFFSET(0,-9).ADDRESS & ",stock,5,FALSE))"

My problem is that at the ""BILL"" vba gives an error "expected: End of
statement"

Where am i missing the point?

Thx

Always so much easier ways to do things!!!

Thank you very much


"Ardus Petus" wrote:

ActiveCell.FormulaR1C1 =
"=IF(RC[-2]=""bill"",SUM(INDIRECT(SUBSTITUTE(RC[-9],""/"",""_""))),VLOOKUP(R
C[-9],stock,5,FALSE))"

HTH
--
AP

"lasca" a écrit dans le message de
...
I want the following formula in a cell in colum L by using a macro.


=IF(J6="bill",SUM(INDIRECT(SUBSTITUTE(C6,"/","_"))),VLOOKUP(C6,stock,5,FALSE
))

Here is my code used in VBA:
Activecell.Formula = "=IF(" & ACTIVECELL.OFFSET(0,-2).ADDRESS & "=" &
""bill"" & ",SUM(INDIRECT(SUBSTITUTE(C6," & ""/"" & "," & ""_"" &
"))),VLOOKUP(" & ACTIVECELL.OFFSET(0,-9).ADDRESS & ",stock,5,FALSE))"

My problem is that at the ""BILL"" vba gives an error "expected: End of
statement"

Where am i missing the point?

Thx