Search an array of sheets for an array of numbers & return count of numbers

[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)

I had intended to also list the sheet names in column C and write that sheet list to the varSheets array, but have not got that far. Tried some similar things like the numbers list but that failed also, so I just wrote them in the array in the code as you see them.

The numbers list and the sheet list will be much larger in a working code.

Thanks,
Howard

Sub WSnNumCount()

Dim nNumArr() As Variant
Dim nNumCt As Long
Dim varSheets As Variant
Dim i As Long, ii As Long, j As Long
Dim c As Range

nNumArr = Range("A2:A10")

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

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

With nNumArr(i)

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

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

If Not c Is Nothing Then
j = j + 1
End If

End With

Next ' ii

End With

Range("B" & Rows.Count).End(xlUp)(2) = j

Next 'i
j = 0
End Sub

 

[Claus Busch]

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.

 

[GS]

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

 

[GS]

Oops! Forgot to add 'FindNext' so modify with Claus' example.

Since I hate direct read/writes to ranges, my 2nd example is how I'd do
this (since only counting occurances). Far less code and wks get/put in
single step!<g> (Don't have to remember to use FindNext code either!)

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[GS]

Optionally, if you only want to put values in B2:B10...

replace this last line in 2nd sample

Range("A2:B10") = vNumsToCount

with this

Range("B2:B10") = Application.Index(vNumsToCount, 0, 2)

OR

Range("B2").Resize(UBound(vNumsToCount),1) _
= Application.Index(vNumsToCount, 0, 2)

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[L. Howard]

Hi Claus and Garry,

Here is what I am using which seems to work well.

I think I was unclear about the sheet names in column C. I meant that I wanted to list the sheets that were in the workbook that I wanted to search in for the numbers.

I used the example you both pointed out about being 2D and the code now takes a list of sheest in column C and they are the "search in" sheets.

I commented out the line that listed the sheets where the numbers were found.

And just for the record I had just found this as an example and failed to recognize it was what I was dealing with. Sorry.

*****
For a zero-based two dimensional array...
Code:
First row, second column:
arr(0,1)
*****

Thanks to both of you.
Howard


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 = Sheets("Sheet1").Range("C2:C6")

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

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

With Sheets(varSheets(ii, 1))

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

 

[GS]

*****

For a zero-based two dimensional array...
Code:
First row, second column:
arr(0,1)
*****

Not a good idea and is gonna trip you up!

ALL ranges loaded directly into a variant result in 1-based 2D arrays.
There is no Rows(0) on a worksheet, and Columns(1) is labeled "A"!
Deliberately using zero base necessitates having to '+1' your loop
counters. Why would you want that?

So 1st row, 2nd col is arr(1,2)!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[GS]

Revised as per your changes...

Sub WSnNumCount3()
Dim vNumsToCount, vShtsToSearch
Dim rng As Range, n&, j&, k&

With Sheets("Sheet1")
vNumsToCount = .Range("A2:B10")
vShtsToSearch = .Range("C2:C6")
End With
For n = LBound(vNumsToCount) To UBound(vNumsToCount)
For k = LBound(vShtsToSearch) To UBound(vShtsToSearch)
Set rng = Sheets(vShtsToSearch(k, 1)).UsedRange
j = j + WorksheetFunction.CountIf(rng, vNumsToCount(n, 1))
Next 'k
vNumsToCount(n, 2) = j: j = 0
Next 'n
' Range("A2:B10") = vNumsToCount
Sheets("Sheet1").Range("B2").Resize(UBound(vNumsToCount), 1) _
= Application.Index(vNumsToCount, 0, 2)
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

 

[L. Howard]

Sub WSnNumCount3()

Dim vNumsToCount, vShtsToSearch

Dim rng As Range, n&, j&, k&

With Sheets("Sheet1")

vNumsToCount = .Range("A2:B10")

vShtsToSearch = .Range("C2:C6")

End With

For n = LBound(vNumsToCount) To UBound(vNumsToCount)

For k = LBound(vShtsToSearch) To UBound(vShtsToSearch)

Set rng = Sheets(vShtsToSearch(k, 1)).UsedRange

j = j + WorksheetFunction.CountIf(rng, vNumsToCount(n, 1))

Next 'k

vNumsToCount(n, 2) = j: j = 0

Next 'n

' Range("A2:B10") = vNumsToCount

Sheets("Sheet1").Range("B2").Resize(UBound(vNumsToCount), 1) _

= Application.Index(vNumsToCount, 0, 2)

End Sub



--

Garry


So 1st row, 2nd col is arr(1,2)!

Okay, I think I see what you are saying.

Seemed like that example was from a pretty good source, but I may well have misconstrued it.

The last code is very nice, I like the 0's (Zeros) if no number if found.

Thanks.
Howard

 

[GS]

Okay, I think I see what you are saying.

Seemed like that example was from a pretty good source, but I may
well have misconstrued it.

For clarity...
arr(1,2) (indexes row1, col2)
...and makes sense.

To do same with zero-based...
arr(o + 1, 1 + 1) (indexes row0+1, col1+1)
...just doesn't make sense!

The last code is very nice,

I like the 0's (Zeros) if no number if found.

Zero is what CountIf() returns if not found. You must reset the counter
for each sheet, though, or you'll get progressive cumulative count of
current sheet + previous sheets.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[Claus Busch]

Hi Howard,

Am Tue, 13 May 2014 01:46:55 -0700 (PDT) schrieb L. Howard:

The last code is very nice, I like the 0's (Zeros) if no number if found.

or write in B2 of Sheet1:
=SUM(COUNTIF(INDIRECT("Sheet"&ROW($2:$6)&"!1:10000"),A2))
and enter the formula with CTRL+Shift+Enter and copy down


Regards
Claus B.

 

[Claus Busch]

Hi again,

Am Tue, 13 May 2014 12:41:57 +0200 schrieb Claus Busch:

or write in B2 of Sheet1:
=SUM(COUNTIF(INDIRECT("Sheet"&ROW($2:$6)&"!1:10000"),A2))
and enter the formula with CTRL+Shift+Enter and copy down

and in C2:
=IFERROR("Sheet"&SMALL(IF(COUNTIF(INDIRECT("Sheet"&ROW($2:$6)&"!1:10000"),$A2),ROW($2:$6)),COLUMN(A1)),"")
and enter the formula with CTRL+Shift+Enter and copy down and to the
right till cells remain empty


Regards
Claus B.