Thread: I need a way to reset some formulas as part of a maintenence function

=IF(Active!A3=0, "", Active!A3)
=IF(Active!A4=0, "", Active!A4)

So here is the problem, I have a sheet that is for printing data from page active. As it stands the formula above works fine (those two formulae are for A3, and A4 of my Printing worksheet. All the other cells are vlookups off the value in the cell in the A column. Annoyingly, when I delete a row from my spreadsheet, rather than simply referring to the NEW A3, it breaks the reference. (if its possible to just prevent that from happened so much the better).

The only way I can think of to fix this is to fold into another maintenence macro, a loop that goes through and makes the formula equal what it is supposed to for A3 to A50, but I'm not actually sure how to do that because the cell references are to another table. I don't want to try and do any kind of function IN the macro (although if I have to I have to). I just want the cells to have that formula in them with the correct references rather than #REF.

Any ideas?

To clarify, this MIGHT have a function fix (and I'd take that), but what I'm specifically looking for is the VBA to insert =IF(Active!A3=0, "", Active!A3) into A3, =IF(Active!A4=0, "", Active!A4) into A4, etc down to A50.

Why do you delete rows? How about just clearing contents instead of deleting the rows?

A few options:

=IF(INDEX(Active!A:A,row())=0, "", INDEX(Active!A:A,row()))

or if Active column A contains text:

=INDEX(Active!A:A,row())&""

or VBA

Range("A3:A50").Formula = "=IF(Active!A3=0, """", Active!A3)"