Page 2 of 2 FirstFirst 12
Results 11 to 18 of 18

Thread: Search Two Ranges To See If The Same String Exists

  1. #11
    Moderator
    Join Date
    Jul 2012
    Posts
    156
    Rep Power
    12
    Sheets("Shipment Report.xls").Select
    Is this really the sheetname or the workbookname where the other sheet is in ?
    Code:
    Sub tst()
        Dim sq
        sn = Sheets("Weekly Shipped Details").Columns(9).SpecialCells(xlCellTypeConstants)
        sn2 = Sheets("Shipment Report").Columns(9).SpecialCells(xlCellTypeConstants)
        ReDim sq(1 To UBound(sn2))
        For i = 1 To UBound(sn2)
            For ii = 1 To UBound(sn)
                If sn(ii, 1) = sn2(i, 1) Then
                    sq(i) = sn2(i, 1): Exit For
                Else
                    sq(i) = "#N/A"
                End If
            Next
        Next
        Sheets("Shipment Report").Range("J2").Resize(UBound(sn2)) = WorksheetFunction.Transpose(sq)
    End Sub
    Also you have several questions posted where you didn't give a final response ???

  2. #12
    Bakerman, thanks for your help. I have now resolved this with a vlookup workbookfunction in a loop (below) I think the main problem was defining the range to check.
    Code:
    On Error Resume Next
       Dim result As Variant
    For Each oCell In Range(Range("A2"), Range("A1000").End(xlUp))
        result = Application.VLookup(oCell, Workbooks("Shipment Report " & Year(GetDateRangeTo) & ".xls").Sheets("Weekly Shipped Details") _
        .Range("I5:I1000").Cells, 1, 0)
            oCell.Offset(0, 2).Select
            Selection.Value = result
    Next

  3. #13
    Moderator
    Join Date
    Jul 2012
    Posts
    156
    Rep Power
    12
    It is a very good thing that you achieved a solution yourself, but some remarks.
    Avoid Sheet interaction (reading - writing) as much as possible certainly for large numbers of data.
    Avoid using Worksheetfunctions if there is a VBA substitute.
    I tested your code on 1000 numbers to lookup in a 1000 numbers which your code took 0.14sec. while the code below only took 0.04sec
    This might seem trivial but it's a gain of 75%
    Code:
    Sub tst3()
        t = Timer
        Dim sq, result
        sn = Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row)
        ReDim sq(1 To UBound(sn))
        For i = 1 To UBound(sn)
            result = Application.Match(sn(i, 1), Workbooks("Shipment Report " & Year(GetDateRangeTo) & ".xls") _
                        .Sheets("Weekly Shipped Details").Range("I5:I1000"), 0)
            sq(i) = IIf(Not IsError(result), sn(i, 1), "#N/A")
        Next
        Range("C2").Resize(UBound(sn)) = WorksheetFunction.Transpose(sq)
        MsgBox Timer - t
    End Sub

  4. #14
    Senior Member
    Join Date
    Jun 2012
    Posts
    337
    Rep Power
    12
    Another caveat: avoid 'select' and 'activate' in VBA

    probably some more speed gain, using

    Code:
    Sub tst4()
        t = Timer
        Dim sq
        sn = Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row)
        sp= Workbooks("Shipment Report " & Year(GetDateRangeTo) & ".xls").Sheets("Weekly Shipped Details").Range("I5:I1000")
        ReDim sq(1 To UBound(sn))
    
        For i = 1 To UBound(sn)
            sq(i) = IIf(IsError(Application.Match(sn(i, 1), sp,0)), "#N/A" ,sn(i, 1))
        Next
        Range("C2").Resize(UBound(sn)) = WorksheetFunction.Transpose(sq)
    
        MsgBox Timer - t
    End Sub

  5. #15
    Moderator
    Join Date
    Jul 2012
    Posts
    156
    Rep Power
    12
    @ snb
    I have to disappoint you but as far as speed goes your solution still can't beat Match and Range object.
    I tested a similar code involving 2 arrays but i compared each item of array1 to each item of array2.
    I've tested the four solutions on the same ranges of 1000 items to lookup in 1000 elements. These results came out.
    Vlookup 0.16sec, array vs array 0.12sec, array vs range using Match 0.04sec and array vs array using Match 0.12sec.
    It seems that Match looses its speed when used in comparing in an array vs comparing to Range object.

  6. #16
    Senior Member
    Join Date
    Jun 2012
    Posts
    337
    Rep Power
    12
    Very disappointed...

    Alternative:

    Code:
    Sub tst5()
        t = Timer
        sn = Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row)
        sq = sn
        c00= join(application.transpose(Workbooks("Shipment Report " & Year(GetDateRangeTo) & ".xls").Sheets("Weekly Shipped Details").Range("I5:I1000").value),"|")
        
        For j = 1 To UBound(sn)
            if instr("|" & c00 & "|" ,"|" & sn(j, 1)& "|")=0 then sq(j,1)="#N/A"
        Next
        Range("C2").Resize(UBound(sq)) = sq
    
        MsgBox Timer - t
    End Sub

  7. #17
    Moderator
    Join Date
    Jul 2012
    Posts
    156
    Rep Power
    12
    Eureka.
    0.03sec

  8. #18
    Senior Member
    Join Date
    Jun 2012
    Posts
    337
    Rep Power
    12
    Thank you for testing

Similar Threads

  1. Search in tables
    By mahmoud-lee in forum Excel Help
    Replies: 3
    Last Post: 07-21-2013, 10:30 PM
  2. Search form on work sheet
    By Ryan_Bernal in forum Excel Help
    Replies: 5
    Last Post: 01-15-2013, 11:46 AM
  3. Distribute ranges as per Plan
    By ayazgreat in forum Excel Help
    Replies: 35
    Last Post: 12-17-2012, 10:40 PM
  4. Replies: 3
    Last Post: 12-05-2012, 09:51 PM
  5. Search word in different workbooks and sheets
    By k0st4din in forum Excel Help
    Replies: 5
    Last Post: 11-29-2012, 10:10 PM

Tags for this Thread

Posting Permissions

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