Macro kills formula

[Guest]

I have a macro which searches a sheet for rows within a certain date range,
sorts these rows to the top and then deletes those rows. After running the
macro, a cell which contains a formula not related to the macro, has one of
the cell references changed to "#REF!".

This is the formula:
=MATCH(UserCheck!B3,C6:OFFSET(C6,G1-5,0),0)
The cell reference "C6" is the one that changes.

This is the macro line which deletes the range -
Range("A6:OFFSET(A6,G4-1,5).Delete

I can see that C6 is in the range being deleted but the cell is still there,
only the contents are deleted, and these are replaced by the remaining rows
moving up following deletion. (I'm not deleting actual rows, only the range)

Why is this happening?

 

[Niek Otten]

The cell may still be there, but it's an other one, according to excel.
replace "C6" with INDIRECT("C6").

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel