Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search an array of sheets for an array of numbers & return count of numbers
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search an array of sheets for an array of numbers & return count of numbers
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search an array of sheets for an array of numbers & return count of numbers
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search an array of sheets for an array of numbers & return count of numbers
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search an array of sheets for an array of numbers & return count of numbers
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search an array of sheets for an array of numbers & return count of numbers
*****
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search an array of sheets for an array of numbers & return count of numbers
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(vNumsTo Count), 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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(vNumsTo Count), 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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search an array of sheets for an array of numbers & return count of numbers
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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search an array of sheets for an array of numbers & return count of numbers
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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search an array of sheets for an array of numbers & return count of numbers
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
count array for top three entries and return their number | Excel Discussion (Misc queries) | |||
Which Function to Use? Search an Array, Return a Row Value | Excel Discussion (Misc queries) | |||
Search array and return element No | Excel Worksheet Functions | |||
average of kth largest numbers in an array of n numbers | Excel Worksheet Functions | |||
SEARCH EXCEL ARRAY FOR NON-VOIDS AND ENTER INTO ANOTHER ARRAY, | Excel Programming |