Why does the macro not work?

[Guest]

Duke Carey wrote:

You ought to receive input from some of the sharper advisors here, but for
the meantime I'd suggest you use a custom function like the one below

Function Count_X(rng As Range, strTest As String) As Integer
Dim cc As Range
Count_X = 0
For Each cc In rng
If cc.Value = strTest Then Count_X = Count_X + 1
Next
End Function

After pasting it into a module, you'd use it by entering this formula in a
cell

=count_x((K6,R6,Y6,AF6,AM6,AT6,BA6,BH6,BO6,BV6,CC6,CJ6,CQ6,CX6,DE6,DL6,DS6,DZ6,EG6,EN6),"X")

I did this while the rest of my spreadsheet was still imcomplete. It worked
on 1 page, but not the other 16 that I needed it used on. I decided to delete
it temproarily until the rest of the sheets are completed.

They are now done, and after putting the macro back in, it's not doing
anything. What am I doing wrong? I've never used a macro before, and like
most help sections... it's written in Greek.

 

[Ken Wright]

How about a formula instead as opposed to a UDF, that will take a range as
opposed to multiple arguments:-

=SUMPRODUCT(--(MOD(COLUMN(K6:EN6),7)=4),--(K6:EN6="X"))

This assumes that you don't vary the columns in anyway though, ie not adding
or deleting columns, including any before the range starts in Cols A:J. If
that may happen, then you could always use:-

=SUMPRODUCT(--(MOD(COLUMN(K6:EN6),7)=MOD(COLUMN(K6),7)),--(K6:EN6="X"))

but this still assumes no variation of columns inside your range, ie there
are currently 6 columns between each possible X.

 

[Guest]

My bad...

Forgot to include:

I can't use the range (K6:EN6) because of a similar =COUNTIF function that
runs on the same row.

 

[Dave Peterson]

Maybe it's just a text comparison difference.

Unless you do something special, X <> x in VBA.

You could add:
Option Compare Text
at the top of the module

or you could do something like:
If lcase(cc.Value) = lcase(strTest) Then Count_X = Count_X + 1

or even:
If StrComp(cc.Value, strTest, vbTextCompare) = 0 Then Count_X = Count_X + 1

 

[Ken Wright]

I may be missing something here but can't see why that matters. The macro
runs through each cell in the selection of cells you put in to the function,
which from the look of it is every 7th column. It then checks each cell to
see if it has an X in it, and if so it counts it. The formula does the
same, and doesn't matter what else is in between or also querying the same
range? You can put the formula in any column outside your range so that
shouldn't be a limitation. I was assuming you would simply put the formula
wherever you had your function.