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.

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.

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

thanks!

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