Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
IF(OR) function with text
hello,
I'm trying to get VBA to insert the following formula into Excel, however, I get a "Expected end of statement error" with all the text ("1", "4", " R", "Error/check") sections in the formula. Would you know why? And maybe there's a smarter way to do this? Range("H2").Formula = "=IF(OR(LEFT(G2,1)=1,LEFT(G2,1)="1"),VLOOKUP(G 2,[Voucher_03.xls]Sheet1!$A:$B,2,FALSE), IF(OR(LEFT(G2,1)=4,LEFT(G2,1)="4"),VLOOKUP(G2,[POnumbers_03.xls]Sheet1!$A:$B,2,FALSE),IF(LEFT(G2,2)=" R",VLOOKUP(G2,[POnumbers_03.xls]Sheet1!$A:$B,2,FALSE), "Error/check")))" BR, K. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
IF(OR) function with text
Use double quotes i.e. ""1"",""R""
HTH " wrote: hello, I'm trying to get VBA to insert the following formula into Excel, however, I get a "Expected end of statement error" with all the text ("1", "4", " R", "Error/check") sections in the formula. Would you know why? And maybe there's a smarter way to do this? Range("H2").Formula = "=IF(OR(LEFT(G2,1)=1,LEFT(G2,1)="1"),VLOOKUP(G 2,[Voucher_03.xls]Sheet1!$A:$B,2,FALSE), IF(OR(LEFT(G2,1)=4,LEFT(G2,1)="4"),VLOOKUP(G2,[POnumbers_03.xls]Sheet1!$A:$B,2,FALSE),IF(LEFT(G2,2)=" R",VLOOKUP(G2,[POnumbers_03.xls]Sheet1!$A:$B,2,FALSE), "Error/check")))" BR, K. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
IF(OR) function with text
Hi, To fix the VBA problem use double quotation marks in your statement, e (""1"", ""4"", "" R"", ""Error/check""). re "And maybe there's a smarter way to do this?": I haven't actually taken the time to completely decipher your formul as I'm off to bed but at a glance would suggest: *that you limit the size of your lookup ranges to those rows tha contain data rather than complete columns to speed the vlookup up (e by using a "dynamic named range"). *Changing the format of col G to "general" or a some sort of numbe format so that the "OR(LEFT(G2,1)=1,LEFT(G2,1)="1"),..." etc is no needed. hth Rob Brockett NZ Always learning & the best way to learn is to experience.. -- broro18 ----------------------------------------------------------------------- broro183's Profile: http://www.excelforum.com/member.php...fo&userid=3006 View this thread: http://www.excelforum.com/showthread.php?threadid=53369 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
IF(OR) function with text
thanks!
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
IF(OR) function with text
Pleased we could help, thanks for the acknowledgement. Rob Brockett NZ Always learning & the best way to learn is to experience... -- broro183 ------------------------------------------------------------------------ broro183's Profile: http://www.excelforum.com/member.php...o&userid=30068 View this thread: http://www.excelforum.com/showthread...hreadid=533692 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
using a conditional suffix in text function format syntax=text(value,format_text) | Excel Worksheet Functions | |||
Can Text Function change output text color? | Excel Discussion (Misc queries) | |||
Advanced text function (combining text) | Excel Worksheet Functions | |||
Using Concatenate function to generate text in Text Box | Charts and Charting in Excel | |||
Macro or Function to make text size to suite text Length? | Excel Discussion (Misc queries) |