VB programming

[Guest]

Hello,

I want to run a macro such that when I press on a toggle button, if the
state is true then I would like to insert the following formula into a cell

=IF(ISERROR(OR(SEARCH("a",$B$10,1)=1,SEARCH("a",$B$10,1)=4)),"Random",(VLOOKUP($B$10,'MC DATA'!$B$6:$C$54,2,FALSE)/(($B$12+18)/12)*Production_Rate)*60).

This is what I have tried:
worksheets("Simple Cost Analysis").Range("G6").Formula =
"=IF(ISERROR(OR(SEARCH("a",$B$10,1)=1,SEARCH("a",$B$10,1)=4)),"Random",(VLOOKUP($B$10,'MC DATA'!$B$6:$C$54,2,FALSE)/(($B$12+18)/12)*Production_Rate)*60)"

But I get a syntax error due to the " within the formula.

Any idea how to solve this?

Thank you in advance

 

[crferguson]

Within the formula itself (not counting the quotes around the formula
used by the .Formula function) double up the quotes. For example,
notice the quotes around "text" versus the single quotes around the
entire formula:

range("A1").Formula = "=search(""text"",$B$1)"

 

[Rick Rothstein \(MVP - VB\)]

You have to double up the quote marks **within** the String value in order
to place a single quote mark there (otherwise VBA tries to use the internal
single quote marks as the end delimiter of the String constant and gets
confused. Try it this way...

Worksheets("Simple Cost Analysis").Range("G6").Formula =
"=IF(ISERROR(OR(SEARCH(""a"",$B$10,1)=1,SEARCH(""a"",$B$10,1)=4)),""Random"",(VLOOKUP($B$10,'MC
DATA'!$B$6:$C$54,2,FALSE)/(($B$12+18)/12)*Production_Rate)*60)"

Rick

 

[Guest]

Double up your quotes (that is a directive to the compiler that you want to
use quotes in your string)

=IF(ISERROR(OR(SEARCH(""a"",$B$10,1)=1,SEARCH(""a"",$B$10,1)=4)),""Random"",(VLOOKUP($B$10,'MC
DATA'!$B$6:$C$54,2,FALSE)/(($B$12+18)/12)*Production_Rate)*60)

 

[Guest]

Thank you to all

You have to double up the quote marks **within** the String value in order
to place a single quote mark there (otherwise VBA tries to use the internal
single quote marks as the end delimiter of the String constant and gets
confused. Try it this way...

Worksheets("Simple Cost Analysis").Range("G6").Formula =
"=IF(ISERROR(OR(SEARCH(""a"",$B$10,1)=1,SEARCH(""a"",$B$10,1)=4)),""Random"",(VLOOKUP($B$10,'MC
DATA'!$B$6:$C$54,2,FALSE)/(($B$12+18)/12)*Production_Rate)*60)"

Rick