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

Discussion in 'Excel Programming' started by L. Howard, May 13, 2014.

1. ### L. HowardGuest

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

L. Howard, May 13, 2014

2. ### Claus BuschGuest

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

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
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)
End If

End With

Next ' ii

Next 'i

End Sub

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

Claus Busch, May 13, 2014

3. ### GSGuest

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, May 13, 2014
4. ### GSGuest

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, May 13, 2014
5. ### GSGuest

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

GS, May 13, 2014
6. ### L. HowardGuest

Re: Search an array of sheets for an array of numbers & return countof numbers

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

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

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)

End If

End With

Next ' ii

Next 'i

End Sub

L. Howard, May 13, 2014
7. ### GSGuest

> *****
> 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, May 13, 2014
8. ### GSGuest

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

GS, May 13, 2014
9. ### L. HowardGuest

Re: Search an array of sheets for an array of numbers & return countof numbers

> 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

L. Howard, May 13, 2014
10. ### GSGuest

> 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 hope you found it easy to understand!<g>

> 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

GS, May 13, 2014
11. ### Claus BuschGuest

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.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Claus Busch, May 13, 2014
12. ### Claus BuschGuest

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.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Claus Busch, May 13, 2014