Troubleshooting Date+1 in VB

[nicolehalliday]

Hi,
I could really use some help with my code, I know it's simple but I've tried many variations with no success.

In cell EE1 there is a date which is just entered as "03/15/2011". In code, I'd like to enter the next date, "03/16/2011" in EF1. In excel I can simply write "=EE1+1" to return the date. My code looks like this:

Sheets("highs").Range("EF1").Select
    ActiveCell.FormulaR1C1 = "=EE1 + 1"

For some reason it enters "='EE1' + 1" which returns #NAME?. Why are there brackets around the EE1? All my other variations turned out even worse... any help would be greatly appreciated, thanks!!!

 

[Greg Truby]

Try

Sheets("highs").Range("EF1").Formula = "=EE1 + 1"

(Use the Formula property, not the FormulaR1C1 property.)

<SUP>edit </SUP>BTW - your code would actually be [slightly] more robust if you do use the FormulaR1C1 property. But, in that case, you'd need to edit the formula string to be:

Sheets("highs").Range("EF1").FormulaR1C1 = "=RC[-1] + 1"

<SUB>/edit</SUB>

 

[Jonmo1]

Drop the R1C1, should be just ActiveCell.Formula = ...

Or even better, without selecting..try

Sheets("highs").Range("EF1").Formula = "=EE1 + 1"

 

[nicolehalliday]

Both of the solutions worked, much thanks!!!