How do I stop a formula from changing??

[justme]

=COUNTIF($D$8:$D$120,1)

I have this formula in cell E1. I also have a macro which deletes rows that have blank cells. When I run the macro, the $D$120 keeps changing (reducing). How can I get the cell to ALWAYS remain the same? I want it to be absolute, not relative.

Thanks.[/b]

 

[andshep]

why don't you just get the macro to reset the formula?

 

[justme]

I'm not very good with macros. The will is there, but the knowledge is weak. What would I write?

 

[andshep]

Try putting this in your macro

    Range("E1").Select
    ActiveCell.FormulaR1C1 = "=COUNTIF(R8C4:R120C4,1)"

or

ActiveSheet.Range("e1") = "=COUNTIF(R8C4:R120C4,1)"

 

[justme]

Perfect!

Thanks.

 

[justme]

One more question please.

When I try to use this for another line that is looking for "OH" I get the following error.

Compile error:
Expected: end of statement

I don't get the error if I just use OH, but then it doesn't find the cells with OH in it either.

 

[sbeatton]

Would you paste your VBA as it sounds like you aren;t using the correct syntax.

 

[justme]

ActiveCell.FormulaR1C1 = "=COUNTIF(R8C5:R120C5,"OH")"

 

[sbeatton]

Try

Sub Test()
ActiveCell.FormulaR1C1 = "= COUNTIF(R8C5:R120C5,""OH"")"

End Sub

 

[justme]

Don't understand why, but the double quotes worked. Thank you!