IF(OR) function with text

[[email protected]]

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(G2,[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.

[Toppers]

Use double quotes i.e. ""1"",""R""

HTH

"[email protected]" 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(G2,[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.
>
>

[broro183]

Hi,

To fix the VBA problem use double quotation marks in your statement, eg (""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 formula as I'm off to bed but at a glance would suggest:
*that you limit the size of your lookup ranges to those rows that contain data rather than complete columns to speed the vlookup up (eg by using a "dynamic named range").
*Changing the format of col G to "general" or a some sort of number format so that the "OR(LEFT(G2,1)=1,LEFT(G2,1)="1"),..." etc is not needed.

hth
Rob Brockett
NZ
Always learning & the best way to learn is to experience...

[[email protected]]

thanks!

[broro183]

Pleased we could help, thanks for the acknowledgement.

Rob Brockett
NZ
Always learning & the best way to learn is to experience...