Insert formula into Cell 1

[vba317]

I am trying to insert a formula into a cell. The code I am using inserts the code as a string so I get a $NAME error in the cell. Is there a way to fix this issue?

Sub InsertCells()
Dim LR As Long
Dim A1 As Long

' InsertCells Macro
' Macro recorded 4/22/2009 by
'

    'LastRow = LR
    
    LR = ActiveSheet.UsedRange.Rows.Count
    A1 = LR - 200
    
    
   Range("H" & LR).Value = "200 day Average"
   Range("I" & LR).Value = "=SUM(E&A1:E&LR/200)"
   
     

End Sub

 

[HarleyQuinn]

Have a look at the FormulaR1C1 property rather than Value.

Hope this helps

HarleyQuinn
---------------------------------
You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before posting.

http://lessthandot.com

 

[SkipVought]

Hi,

The formula has to be error free. Yours is not...

"=SUM(E&A1:E&LR)/200"

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

..oops

"=SUM(E" & A1 & ":E" & LR ")/200"

I think it easier to use a property...

"=SUM(" & Range(Cells(A1,"E"),Cells(LR,"E")).Address(false,false) & ")/200"

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[vba317]

Thanks Skip the last answer worked great!

Tom