Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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)" |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA Programming. | Excel Programming | |||
new to VBA programming | Excel Programming | |||
VBA programming | Excel Programming | |||
Programming lag.. | Excel Programming | |||
Programming Help | Excel Programming |