VB count word occurence in variable size sheet

J

JBW

Me again folks, sorry.

System report will be variable number of rows deep, I have macro that's
running sweetly but counter in VB is set to big number to make sure all lines
calculated. I need to be able to count he occurences of "NPQ" in the sheet,
this will give me correct counter number for vb.

How do I do count?
How do I refernce answer in the macro?

cheers in advance for all your wonderful help
 
B

Bob Phillips

myVal = application.countif(activesheet.cells,"NPQ")

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
M

Mike H

If you are trying to use this count value as the terminating value in a loop
then try:-

Sub stance()
For x = 1 To Application.CountIf(ActiveSheet.Cells, "NPQ")

'Your macro

Next

End Sub

Mike
 
J

JBW

Bob,

If I use that in my macro it'll count the NPQ for me, but how do I use the
naswer in my for Counter = 1 to 100 line?
 
B

Bob Phillips

For cntr = 1 To myVal

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Top