Using double quotes in a macro

L

lasca

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
 
A

Ardus Petus

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

HTH
 
L

lasca

Always so much easier ways to do things!!!

Thank you very much


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

HTH
--
AP

lasca said:
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
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

End of Statement error 3
Using While Wend with Cells.Find 1
END IF in a macro 5
Macro to retrieve stock quotes in Excel 2007 1
Need macro help 3
Formula in macro causes macro to fail 2
Macro to fill to next field with data 4
A workbook of macros which runs on external workbooks. 6

Top