#NAME error from VBA code

it's been a week since i'm trying to fix this, I'm giving up...

If I input the formula direct into the cell it works perfect. If I try to use it from a VBA macro I get #NAME error in the cell. However, if after the formula is in place from the VBA code I place the cursor at the end of the formula bar and press enter, without changing anything, the result is correct.
I tryed 2 codes, one trying to set a variable column in the formula, the other trying to use a set column ("O").
The first code looks like this:

Please Login or Register  to view this content.

where Var4 is an integer, calculated before as the difference between 2 columns. In the formula bar I get something looging like:

=INDEX(Make!A:A,MATCH("*"&LEFT(O2,3)&"*",Make!B:B,0))
which looks correct to me, but it gives me the #NAME error.

The second code looks like:

Please Login or Register  to view this content.

When I use this code, in the formula bar I get:

=INDEX(Make!A:A,MATCH("*"&LEFT('O2',3)&"*",Make!B:B,0))

(note the 'O2')

But if I place the cursor at the end of formula bar and press enter, it works right.

Please, can somebody help?

Does this help

Please Login or Register  to view this content.

VBA Noob

Glad it helped

VBA Noob