Another problem is using the FormulaR1C1 property and then giving it a
formula in A1 format

Rng.Range("B1").FormulaR1C1 = "=Lookup(R5C1,tab1)"

or for A1

Rng.Range("B1").Formula = "=Lookup(A5,tab1)"
However, I assume you want it relative to the Row where it is entered, so

Rng.Range("B1").FormulaR1C1 = "=Lookup(RC[-1],tab1)"

--
Regards,
Tom Ogilvy


"Rod Gill" <rod AT project-systems DOT co DOT nz wrote in message
...
Try:

Rng.Range("B1").FormulaR1C1 = "=Lookup(A5,tab1)"

+ is just an old backwards compatibility option for old Lotus 123
spreadsheets. It's better to use Excel's = instead of your +.
--

Rod Gill
Project MVP

NEW!! Project VBA Book, for details visit: http://www.projectvbabook.com


"BRC" wrote in message
ups.com...
Gentlemen,
Thank you for the help. I have got to a point where I can insert "My
Formula" in the correct cells. My only remaining problem how to refer
to the formula so it doesn't go in as text. What I am trying to
insert is "+Lookup(A5,tab1)" (in place of "MY Formula"). Tab1 is a
named range within the workbook. It seems that referring the cell a5
is what is causing the problem but I am not sure how to correct it. I
enclose the whole thing in double quotes I get #NAME? error and the
macro has put single quotes around the 'a5'. Is there a special way
to refer to cells within a formula in the code?
Many thanks
BRC

Tom Ogilvy wrote:
Sub Formulas()
Dim Rng As Range
Dim DateRange As Range
Set DateRange = Columns(1).SpecialCells(xlConstants,xlNumbers)
For Each Rng In DateRange
'If Not IsEmpty(Rng) Then
Rng.Range("B1").FormulaR1C1 = "My Formula"
'End If
Next Rng
End Sub


Depends on what is in Column A.

--
Regards,
Tom Ogilvy


"BRC" wrote in message
oups.com...
Rod thanks for the help. Your code works well but I think I wasn't
concise on my original post. The range of column A can change. It
might
be a5-a6 or might be a5 - a210. So I can't hard code the range.
thanks again
BRC

Rod Gill wrote:
Hi,

Try:

Sub Formulas()
Dim Rng As Range
Dim DateRange As Range
Set DateRange = Range("A5:A14")
For Each Rng In DateRange
If Not IsEmpty(Rng) Then
Rng.Range("B1").FormulaR1C1 = "My Formula"
End If
Next Rng
End Sub

--

Rod Gill

"BRC" wrote in message
oups.com...
Hi All
I am looking for the most efficient way (in code) to insert a
formula
in the cell next to one that is already populated. I. e. Cells
a5-a14
have dates. I want to insert the same formula in cells b5-b14.
the range in col A changes. I have looked at help for autofill,
filldown etc but I not sure how to test for cell a(x) being greater
than "". Any suggestions would be appreciated.
Thanks BRC