I have a standard COUNTIF query in a spreadsheet that I'm adding a macro to
in order to automate its generation.

At present the formula is =COUNTIF('Original Data'!Y:Y,B3) and it returns a
value of 595, which is correct.

I have some code in my macro which returns the column number (25) according
to set criteria and inserts this into the above formula. I have to determine
the column number each time as some enlightened people keep messing with the
source data format and columns move about in their exports (don't ask!).

So, the revised formula looks like this: =COUNTIF('Original Data'!25:25, B3)
and returns a value of 8. A very incorrect number!

Why does this happen and what's the best way to ensure that I get the
correct result returned? I have about 600 of these formulae to set up for
various parts of the spreadsheet. If I knew that the source data layout was
going to be static, I wouldn't be bothering with a macro at all and would
just leave all the formulae in situ.

Many thanks