I had a look through the forum but could not find what i need, can anyone out there help?
I have a sheet that works fine and am just fine tuning the last bits and I need a little help please.
There is a function =LOOKUP(K1,'AR7 calcs'!F22:F24,'AR7 calcs'!N22:N24) that as part of the sheet could possibly be overwritten in some circumstances so i want to reinsert it as part of a clear sheet macro I have written.
The macro Range("I13:J13").Select
ActiveCell.FormulaR1C1 = "=LOOKUP(K1,'AR7 calcs'!F22:F24,'AR7 calcs'!N22:N24)" leaves the formula as =LOOKUP('K1','AR7 calcs'!'F22':'F24','AR7 calcs'!'N22':'N24') and retuns a #name? error

How do I get the macro to input exactly the formula I need?

chers
nobby

sussed
Range("i13:j13").Select
ActiveCell.FormulaR1C1 = _
"=LOOKUP(R[-9]C[4],'AR7 calcs'!R[12]C[-1]:R[16]C[-1],'AR7 calcs'!R[12]C[7]:R[16]C[7])"

If you're going to use .formulaR1C1, then your formula needs to use R1C1
reference style--not A1 reference style.

Maybe you can do what you want just using .formula????



nobbyknownowt wrote:

I had a look through the forum but could not find what i need, can
anyone out there help?
I have a sheet that works fine and am just fine tuning the last bits
and I need a little help please.
There is a function =LOOKUP(K1,'AR7 calcs'!F22:F24,'AR7 calcs'!N22:N24)
that as part of the sheet could possibly be overwritten in some
circumstances so i want to reinsert it as part of a clear sheet macro I
have written.
The macro Range("I13:J13").Select
ActiveCell.FormulaR1C1 = "=LOOKUP(K1,'AR7 calcs'!F22:F24,'AR7
calcs'!N22:N24)" leaves the formula as =LOOKUP('K1','AR7
calcs'!'F22':'F24','AR7 calcs'!'N22':'N24') and retuns a #name? error

How do I get the macro to input exactly the formula I need?

chers
nobby

--
nobbyknownowt

--

Dave Peterson

Try this

Sub insert()

Dim x

For x = 1 To 2
Cells(13, x + 8).Activate
ActiveCell.Formula = "=LOOKUP(K1,'AR7 calcs'!F22:F24,'AR7 calcs'!N22:N24)"
Next x

End Sub



"nobbyknownowt" wrote:


I had a look through the forum but could not find what i need, can
anyone out there help?
I have a sheet that works fine and am just fine tuning the last bits
and I need a little help please.
There is a function =LOOKUP(K1,'AR7 calcs'!F22:F24,'AR7 calcs'!N22:N24)
that as part of the sheet could possibly be overwritten in some
circumstances so i want to reinsert it as part of a clear sheet macro I
have written.
The macro Range("I13:J13").Select
ActiveCell.FormulaR1C1 = "=LOOKUP(K1,'AR7 calcs'!F22:F24,'AR7
calcs'!N22:N24)" leaves the formula as =LOOKUP('K1','AR7
calcs'!'F22':'F24','AR7 calcs'!'N22':'N24') and retuns a #name? error

How do I get the macro to input exactly the formula I need?

chers
nobby


--
nobbyknownowt