How do I stop a formula from changing??

justme

Well-known Member
Joined
Aug 26, 2002
Messages
729
=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]
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
:oops: I'm not very good with macros. The will is there, but the knowledge is weak. What would I write?
 
Upvote 0
Try putting this in your macro

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

or

Code:
ActiveSheet.Range("e1") = "=COUNTIF(R8C4:R120C4,1)"
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,267
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top