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

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

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

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

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

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

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