Howard,
nNumArr is a 2D array, not an object. It has 9 rows and 1 col so here's
a couple of ways to go...
Sub WSnNumCount()
Dim nNumArr(), varSheets, c As Range
Dim nNumCt&, i&, j&, k&
nNumArr = Range("A2:A10") '//results nNumArr(1 To 9, 1 To 1)
varSheets = Array("Sheet2", "Sheet3", "Sheet4", "Sheet5", "Sheet6")
'Results varSheets(0 To 4)
For i = LBound(nNumArr) To UBound(nNumArr)
For j = LBound(varSheets) To UBound(varSheets)
Set c = Sheets(varSheets(j)).UsedRange.Find(What:=nNumArr( i, 1),
_
LookIn:=xlValues)
If Not c Is Nothing Then k = k + 1
Next ' j
Range("B" & Rows.Count).End(xlUp)(2) = k: k = 0
Next 'i
End Sub
Sub WSnNumCount2()
Dim vNumsToCount, vName, rng As Range, n&, j&
vNumsToCount = Range("A2:B10")
Const sShtsToSearch$ = "Sheet2,Sheet3,Sheet4,Sheet5,Sheet6"
For n = LBound(vNumsToCount) To UBound(vNumsToCount)
For Each vName In Split(sShtsToSearch, ",")
Set rng = Sheets(vName).UsedRange
j = j + Application.WorksheetFunction.CountIf(rng, _
vNumsToCount(n, 1))
Next 'vName
vNumsToCount(n, 2) = j: j = 0
Next 'n
Range("A2:B10") = vNumsToCount
End Sub
--
Garry
Free usenet access at
http://www.eternal-september.org
Classic
VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.
vb.general.discussion