VBA Hlookup what is wrong?

[Myproblem]

I have code under which should give me Hlookup formula.
But something is wrong.
Can anyone help me to write Hlookup formula? (I need to write Hlookup formula in Cell D10, like D10=HLOOKUP(D10;F_pre_mj;10;False)
any suggestions?


Sub copy_prev_month()
Range("D10").FormulaR1C1 = "=HLOOKUP(R[-10]C1;F_pre_m;10;false)"

Range("D10:D20").Copy
Range("F10:F20").PasteSpecial Paste:=xlPasteFormulas
Range("H10:H20").PasteSpecial Paste:=xlPasteFormulas
End Sub

 

[jbeaucaire]

You can't put a formula in row 10 that references a row -10 from that row, that's row 0, which doesn't exist.

 

[Myproblem]

You can't put a formula in row 10 that references a row -10 from that row, that's row 0, which doesn't exist.

I changed it from Range("D10").FormulaR1C1 = "=HLOOKUP(R[-10]C1;F_pre_m;10;false)"
to Range("D10").FormulaR1C1 = "=HLOOKUP(R[-9]C1;F_pre_m;10;false)"
but it is still wrong

any tips?

 

[jbeaucaire]

Sure, put the formula in D10 that should actually be there, then paste that formula here so we can see the real answer.

 

[Myproblem]

thx you jbeaucaire, i solved it , the code:
Sub copy_prev_month()
Range("D10") = "=HLookup(D$1, F_prev_m, $K10, False)"
End Sub