Hi Howard,

Am Mon, 12 May 2014 23:13:47 -0700 (PDT) schrieb L. Howard:

Range("A2:A10") is a list of numbers.

I want to search each sheet for each number in the list and return occurrence of each number to column B, next to the number.

As posted, code throws 'Subscript out of range' error on the line:
With nNumArr(i)

your array of numbers is a 2D-Array so you have to write
nNumArr(i,1)

If your number can occure more than once you have to do FindNext.
Try:

Sub WSnNumCount()

Dim nNumArr As Variant
Dim nNumCt As Long
Dim varSheets As Variant
Dim i As Long, ii As Long
Dim c As Range
Dim FirstAddress As String

nNumArr = Sheets("Sheet1").Range("A2:A10")

varSheets = Array("Sheet2", "Sheet3", "Sheet4", "Sheet5", "Sheet6")

For i = LBound(nNumArr) To UBound(nNumArr)

For ii = LBound(varSheets) To UBound(varSheets)

With Sheets(varSheets(ii))
Set c = .UsedRange.Find(What:=nNumArr(i, 1),
LookIn:=xlValues, _
lookat:=xlWhole)

If Not c Is Nothing Then
FirstAddress = c.Address
Do
Sheets("Sheet1").Cells(i + 1, 2) =
Sheets("Sheet1").Cells(i + 1, 2) + 1
Sheets("Sheet1").Cells(i + 1, 3) =
Sheets("Sheet1").Cells(i + 1, 3) & Chr(10) & _
Sheets(varSheets(ii)).Name & "!" & c.Address(0, 0)
Set c = .UsedRange.FindNext(c)
Loop While Not c Is Nothing And c.Address < FirstAddress
End If

End With

Next ' ii

Next 'i

End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional