Maintain Formulas / Cell reference help

[wjsok85]

Hi,

I have a marco that will insert a new row and then add formulas to some of the new cells.

My problem is formating the formula to reference the correct cell.

My formula is
=DATE(YEAR(D13])+1,MONTH(D13),DAY(D13))

The problem being is that I need not to always reference cell D13, but the Cell that is -3 colums from the cell that contains the formula, as the value of this cell is different every time.

So ideally I would like a way of writing:
=DATE(YEAR((OFFSET([this cell],0,-3)))+1 etc

where [this cell] references the cell that the formula is in without any changes to the formula..

Any ideas?

Have tried the Indirect and Cell Adress fuinctions alongside the offset function but I cant get anything to work!

Thanks

Wil

[DonkeyOte]

Use R1C1 notation in your code, ie:

Please Login or Register  to view this content.

Anything appearing within [ ] is a relative adjustment based on the current cell, if a number appears after R/C outside of [ ] it is an absolute reference, ie R2C3 is like $C$2.

That said the formula you're using is open to error if the date happens to be 29th Feb (ie a leap year)

[wjsok85]

Thats great, thanks

why do I get a compile error when trying to add this formula to the macro?

Please Login or Register  to view this content.

I receive 'Compile Error: Expected )'

[DonkeyOte]

Perhaps

Please Login or Register  to view this content.

if you need " literally then double them "" ... also FormulaR1C1 is an equal of .Value etc... so used as alternative to .Value rather than as a child of.

[wjsok85]

Yeah, I figured the .Formula acted inpalce of .value that was me having not enough caffine!

It all works perfectly, I can now apply that process to other issues that I was having!

Thanks AGAIN,

Wil