searching characters problem

I think the A:A and B:B references will need to change to R1C1 syntax and also a literal " in a formula needs to be "", i.e. where the formula has "*" this needs to be ""*"".

The easisest way to do this is to use the macro recorder. record a macro in which you put that formula in the required cell and then you'll get the right syntax

Another way of getting the correct formula is to:
1. Select cell with formula.
2. In the VBE Immediate window type '? activecell.FormulaR1C1' (without the quotes)

This should display something like =INDEX(Sheet2!C[1],MATCH("*"&LEFT(RC[-18],3)&"*",Sheet2!C[-5], 0))

I ended up with the following code:
ActiveCell.FormulaR1C1 = "=INDEX(Make!A:A,MATCH(""*""&LEFT(rc[-" & Var4 & "],3)&""*"",Make!B:B,0))"
where Var4 is calculated before.
When I run the macro, The formula is correct in the formula bar, but in the cell I get "#NAME?". However, if I place the cursor at the ed of the formula bar, without changing anything and I press "Enter", I get the correct value.
What is wrong?