Excel Changing Formula

[greg303]

I have a marco that monitors changes in a text box, when a change is made it selects rows a1-a99, cuts them and pastes it to cell a2-a100 then inserts the new value in a1. This works fine, I also have 9 countif formula's that watch watch data is in cells a1 - a100 and counts the instances of certain numbers. The annoying problem is that when the marco runs the formual in C1 which is '=countif($a$1:$a$100, 1)' changes to '=countif($a$2:$a$100, 1)'. I know its trying to kept referance to the cells it was asked to monitor, but I dont want this, I want it to always monitor a1-a100 always. There must be an option to turn off that I am missing. Can anyone help?

Thanks in advance.

 

[SkipVought]

greg303,

Since you are using VBA...

c = Application.CountIf(Range(Cells(1, 1), Cells(100, 1)), 1)

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[SkipVought]

I guess I should have made it a Function

Function MyCountIf()
    Application.Volatile
    MyCountIf = Application.CountIf(Range(Cells(1, 1), Cells(10, 1)), 1)
End Function

So use =MyCountIf() instead of =countif($a$1:$a$100, 1)



Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884