I have this question based on a question asked in this link
For example, the formula in cell C1 Sheet1 is =VLOOKUP(A1,Sheet2!A:B,2,FALSE)
The code I've tried which work is only the "test-5"
Sub test()
'test-1
Range("C1").FormulaR1C1 = "=VLOOKUP(rc[-2],'" & Sheet2.Name & "'!c[1]:c[2],2,false)"
'=VLOOKUP(A1,Sheet2!D:E,2,FALSE) ---> wrong formula
'test-2
Range("C1").FormulaR1C1 = "=VLOOKUP(rc[-2],'" & Sheet2.Name & "'!A:B,2,false)"
'=VLOOKUP(A1,Sheet2!A:(B),2,FALSE) ---> wrong formula
'test-3
Range("C1").FormulaR1C1 = "=VLOOKUP(rc[-2],'" & Sheet2.Name & "'!$A:$B,2,false)"
'throw error 1004
'test-4
Range("C1").FormulaR1C1 = "=VLOOKUP(rc[-2],'" & Sheet2.Name & "'!r[1]c[1]:r[100]c[2],2,false)"
'=VLOOKUP(A1,Sheet2!D2:E101,2,FALSE) ---> wrong formula
'test-5
Range("C1").FormulaR1C1 = "=VLOOKUP(rc[-2],'" & Sheet2.Name & "'!r1c1:r100c2,2,false)"
'=VLOOKUP(A1,Sheet2!$A$1:$B$100,2,FALSE) ---> formula work but the range is with a defined row number
End Sub
So if the answer is :
it's not possible, then I'd be glad if there is an explanation about this.
it's possible, then how ?
I also curious in test4 vs test5.
If in test5, r1c1:r100c2 gives a correct range $A$1:$B$100 with $ sign
Why in test4, r[1]c[1]:r[100]c[2] don't give A1:B100 without $ sign,
but still refer the cell as if it offsetting based on cell C1 Sheet1 (D2:E101)?
Any kind of help would be greatly appreciated.
Thank you in advanced.
