VB programming
 

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

VB programming
 

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)"

VB programming
 

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


"Nelson" wrote in message
...
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

VB programming
 

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)
--
HTH...

Jim Thomlinson


"Nelson" wrote:

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

VB programming
 

Thank you to all

"Rick Rothstein (MVP - VB)" wrote:

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


"Nelson" wrote in message
...
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