formula #REF!

  • Thread starter Thread starter qiong
  • Start date Start date
Q

qiong

Hi all,

What should i do to prevent #REF! in my formula?
I would need to delete certain rows in my database according to som
criteria, but when i do that, this appears.

My formulas:
Range("C1").Select
ActiveCell.FormulaR1C1 = "=0.3*R[2]C"
Range("C2").Select
ActiveCell.FormulaR1C1 = "=0.705*R[1]C"

The error displays only when i delete Row 3. If the other rows ar
deleted, it works fine.

I read about this somewhere, but cant seem to find the thread.

Thanks in advance!
Ken
 
Hi Keng
When you delete row 3, your formula is referencing something that is n
longer there.

In effect, Excel is complaining that it cannot find the reference tha
you have told it to look for in the formula
 
Is it possible to circumvent this?
I would like the formula to apply to the subseqent row (ie orginall
row 4 but now after the delete, has become row 3
 
Hi Keng

Not that I know of?

You could try one of the following.
1. Use the search and replace tool to update the formula.

2. Drag the contents of row 3 into row 4 and then delete row 3. (This
will only work if you do not have content in row 4 to start with)

3. Copy the formula down one row (and if neccessary - then drag it back
to the original cell.) Then delete row 3
 
Hi

=INDIRECT("R[-1]C[0]",FALSE)

In A4 will equal A3. Delete row 3 and it will move to row 3 and equal A2.
 
Back
Top