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. Howard

    L. Howard Guest

    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
    #1
    1. Advertisements

  2. L. Howard

    Claus Busch Guest

    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
     
    Claus Busch, May 13, 2014
    #2
    1. Advertisements

  3. L. Howard

    GS Guest

    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
    #3
  4. L. Howard

    GS Guest

    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
    #4
  5. L. Howard

    GS Guest

    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
    #5
  6. L. Howard

    L. Howard Guest

    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
    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
     
    L. Howard, May 13, 2014
    #6
  7. L. Howard

    GS Guest

    > *****
    > 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
    #7
  8. L. Howard

    GS Guest

    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
     
    GS, May 13, 2014
    #8
  9. L. Howard

    L. Howard Guest

    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
    #9
  10. L. Howard

    GS Guest

    > 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
    #10
  11. L. Howard

    Claus Busch Guest

    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
    #11
  12. L. Howard

    Claus Busch Guest

    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
    #12
    1. Advertisements

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.
Similar Threads
  1. Alan Beban

    VBA Syntax for VLOOKUP to return array of return values

    Alan Beban, Aug 5, 2003, in forum: Excel Programming
    Replies:
    7
    Views:
    275
    Charles Williams
    Aug 5, 2003
  2. ROSE THE RED
    Replies:
    1
    Views:
    126
    Patrick Molloy
    Dec 31, 2004
  3. Corey
    Replies:
    5
    Views:
    230
    Corey
    Jun 26, 2006
  4. Corey
    Replies:
    2
    Views:
    290
    Corey
    Dec 11, 2006
  5. Cheer-Phil-ly

    search an array for values contained in another array

    Cheer-Phil-ly, Apr 12, 2007, in forum: Excel Programming
    Replies:
    0
    Views:
    117
    Cheer-Phil-ly
    Apr 12, 2007
  6. Replies:
    3
    Views:
    100
  7. JenIT
    Replies:
    2
    Views:
    256
    JenIT
    Aug 24, 2010
  8. L. Howard

    Search other sheets for numbers & color them

    L. Howard, Mar 28, 2014, in forum: Excel Programming
    Replies:
    10
    Views:
    163
Loading...