Avoid !REF Errors

[Sandy Radjenovic]

I'm just not catching on with avoiding the #REF error. I realize why I am getting it but getting around it seems to be a problem.

I have formulas in columns that reference the columns above it. When I delete a row, the row below and thereafter contain #REF error because the referenced cell no longer exists.

My spreadsheet is as follows ...

Column P = date time ie P7=01/10/2004 7:00:00 am
Column N = hours needed ie N8=3.0
P8=01/10/2004 10:00:00 am

This is a schedule which calculates time periods. My formula (p8=P7+N8/24) is inserted via VBA

Range("P" & r).Formula = "=P" & r - 1 & " + N" & r & "/ 24"

for rows 7 thru last row.

How can I update this formula so that a deleted row does not result in references to no longer existent cells and #REF errors?

Totally struggling here and would appreciate any insight whatsoever!

 

[Andrew Poulsom]

Try this:

Range("P" & r).Formula = "=OFFSET(P" & r & ",-1,0)+N" & r & "/24"

 

[Sandy Radjenovic]

You are so wonderful! I have been playing with the offset command for some time and, obviously, never came up with the correct syntax!