Name error

[Gregfox]

Hi All,
The following code gives a name error in cell I2. Anyone know how I could fix it?

StartYear2018 is a named cell. Working with excel 2007 on a PC.​

 Dim StartYear As Double
     StartYear = Range("StartYear2018")
     
 Range("I2").Select
 ActiveCell.FormulaR1C1 = "=+SUM(R[1]C: StartYear)"

Thanks.

 

[mrshl9898]

What value is StartYear and what are you wanting the formula to be?

Was looking at this but doesn't seem to make much sense to sum a cell.

Sub InsertRows()

 Dim StartYear As Double
     StartYear = Range("StartYear2018")
     
 Range("I2").Select
 ActiveCell.FormulaR1C1 = "=SUM(R[1]C" & StartYear2018 & ")"
    
End Sub

 

[Gregfox]

I'm trying to sum all the numbers in cell from 'StartYear'(StartYear is a named cell) to R1 same column relative to I2 into I2.
Sorry I omitted a ':' in the last, the below is correct.

Sub InsertRows()

 Dim StartYear As Double
     StartYear = Range("StartYear2018")
     
 Range("I2").Select
 ActiveCell.FormulaR1C1 = "=SUM(R[1]C:" & StartYear2018 & ")"
    
End Sub
​

​

Thanks.

 

[Gregfox]

To be more clear I want to sum all the cells in Col 'I' from I3 to Named cell StartYear in the same column, into I2.
Sorry I was not clear.

 

[ZVI]

Hi,
Try this:

Sub MySum()
  Range("I2").Formula = "=SUM(I3:StartYear)"
End Sub

 

[Gregfox]

Much better, Thanks!

 

[ZVI]

Happy to help!

 

[Gregfox]

Well, it looked good on paper, but when I tried it I got the same NAME ERROR. I think it may have to do with the named cell??

Sub SumI3()
     Dim StartYear As Double
     StartYear = Range("StartYear2018")


     Range("I2").Formula = "=SUM(StartYear:I3)"
End Sub

When I run the following it works but I really have to use a variable

Sub SumI3()


     Range("I2").Formula = "=SUM(StartYear2018:I3)"
End Sub

[/code]

 

[ZVI]

Well,
What value is in the named cell StartYear2018?
What formula of the cell is expected being entered manually, not by the code?

 

[ZVI]

May be you mean this?

Sub SetFormula()
  Dim NamedCell As String
  NamedCell = "StartYear2018"
  Range("I2").Formula = "=SUM(" & NamedCell & ":I3)"
End Sub