Friday, 23 August 2013

Determining the number of matches to a "Find" on a given range

Determining the number of matches to a "Find" on a given range

I have a macro which selects a particular range and then will find the
number "0" on it. I would like to know how many times "0" appears in the
range I've selected so that I can create a variable equal to that number.
How can I set a variable equal to the number of times find returns a match
for the query?
ActiveCell.Select
Selection.Offset(0, 1).Select
item = ActiveCell.Value
Sheets("Lights").Select
Rows(3).Select
Selection.Find(What:=item, After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False _
, SearchFormat:=False).Select
q = ActiveCell.row()
z = ActiveCell.Column()
Range(Cells(q, z), Cells(72, z)).Select
Selection.Find(What:="0", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False _
, SearchFormat:=False).Activate
So if there are 7 instance of 0 on the range then I want to set variable m= 7

No comments:

Post a Comment