VLOOKUP problem

[Euler271]

I'm using VBA to paste a VLOOKUP formula in one worksheet based on a table array found in another workbook's worksheet. I created a macro in Excel to try to get the correct syntax:

=VLOOKUP(G2,[Book2]Sheet1!$B$2:$AH$100000,33,TRUE)

but when I try to paste it from Access VBA:

xlWs.Range("AC2").FormulaR1C1 = "=VLOOKUP(G2,[Book2]Sheet1!$B$2:$AH$100000,33,TRUE)"

I get an error 1004 message, "Application-defined or object-defined error."

Can anyone tell me what the correct syntax for VBA should be?

 

[alansidman]

Try removing the R1C1 reference. This refers to Row Column Relative Notation. Your Vlookup Formula is in what is referred to as A1 Notation. You must choose one notation or the other, but not both.

Read this to understand the difference---> https://bettersolutions.com/excel/formulas/cell-references-a1-r1c1-notation.htm

 

[Euler271]

Try removing the R1C1 reference. This refers to Row Column Relative Notation. Your Vlookup Formula is in what is referred to as A1 Notation. You must choose one notation or the other, but not both.

Read this to understand the difference---> https://bettersolutions.com/excel/formulas/cell-references-a1-r1c1-notation.htm

Excellent. That was the problem. Thank you very much for pointing that out.