Results 1 to 13 of 13

Search column items in multiple sheets

Threaded View

YasserKhalil Search column items in... 03-23-2018, 03:08 AM
YasserKhalil Re: Search column items in... 03-23-2018, 03:21 AM
jindon Re: Search column items in... 03-23-2018, 03:41 AM
YasserKhalil Re: Search column items in... 03-23-2018, 03:49 AM
karedog Re: Search column items in... 03-23-2018, 07:14 AM
YasserKhalil Re: Search column items in... 03-23-2018, 07:46 AM
karedog Re: Search column items in... 03-23-2018, 07:54 AM
YasserKhalil Re: Search column items in... 03-23-2018, 08:11 AM
karedog Re: Search column items in... 03-23-2018, 09:03 AM
YasserKhalil Re: Search column items in... 03-23-2018, 09:18 AM
karedog Re: Search column items in... 03-23-2018, 10:11 AM
YasserKhalil Re: Search column items in... 03-23-2018, 10:35 AM
karedog Re: Search column items in... 03-23-2018, 10:59 AM
  1. #1
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,892

    Search column items in multiple sheets

    Hello everyone
    I have some items in column A in sheet1 ..and I intend to search for each item in the other sheets
    The search will be through the used range of the sheet and return the value in the first column of the searched sheets
    I have the following code that works well for small amounts of data but in real file it is very very slow so I am searching for a faster way
    Sub Search_Column_Items_Across_Multiple_Sheets_Using_Arrays()
        Dim wsh As Worksheet
        Dim ws As Worksheet
        Dim arr As Variant
        Dim a As Variant
        Dim v As Variant
        Dim rng As Range
        Dim i As Long
        Dim x As Long
        
        Set wsh = Worksheets("Sheet1")
        Set rng = wsh.Range(wsh.Range("A2"), wsh.Range("A" & wsh.Rows.Count).End(xlUp))
        arr = rng.Value
        
        For i = 1 To UBound(arr, 1)
            x = -1: Set ws = Nothing
            For Each ws In ThisWorkbook.Worksheets(Array("1", "2"))
                a = ws.Range("A1", ws.Cells(1, 1).SpecialCells(xlLastCell)).Value
                x = SearchIn2DArray(a, CStr(arr(i, 1)))
                If x <> -1 Then Exit For
            Next ws
    
            If x = -1 Then
                arr(i, 1) = Empty
            Else
                arr(i, 1) = a(x, 1)
            End If
            Erase a
        Next i
    
        Application.ScreenUpdating = False
            rng.Offset(0, 1).Value = arr
        Application.ScreenUpdating = True
    End Sub
    
    Function SearchIn2DArray(vArr As Variant, strCrit As String)
        Dim i As Long
        Dim j As Long
    
        SearchIn2DArray = -1
    
        For i = LBound(vArr, 1) To UBound(vArr, 1)
            For j = LBound(vArr, 2) To UBound(vArr, 2)
                If vArr(i, j) = strCrit Then
                    SearchIn2DArray = i: GoTo Skipper
                End If
            Next j
        Next i
    
    Skipper:
    End Function
    * The issue is posted here too
    http://www.eileenslounge.com/viewtopic.php?f=30&t=29498
    Attached Files Attached Files
    Last edited by YasserKhalil; 03-23-2018 at 03:23 AM.
    < ----- Please click the little star * next to add reputation if my post helps you
    Visit Forum : From Here

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Search for a column across multiple sheets and copy them in 1st sheet.
    By gauti14 in forum Excel Programming / VBA / Macros
    Replies: 17
    Last Post: 03-20-2017, 04:41 PM
  2. [SOLVED] Basic User list items , search, Edit from three sheets
    By KK1234 in forum Excel Programming / VBA / Macros
    Replies: 16
    Last Post: 04-15-2014, 06:52 AM
  3. [SOLVED] Need to count items in column that match multiple data items
    By bclucas55 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-27-2014, 10:03 AM
  4. [SOLVED] Search within a column and repeat the search until all items are found
    By RANDY LIPOSKY in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 01-09-2014, 10:23 AM
  5. Replies: 0
    Last Post: 10-15-2012, 12:12 PM
  6. Search column for three items
    By erblaze in forum Excel - New Users/Basics
    Replies: 4
    Last Post: 06-08-2011, 02:48 PM
  7. Replies: 3
    Last Post: 01-21-2010, 06:58 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1