Page 1 of 2 12 LastLast
Results 1 to 10 of 18

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

  1. #1

    Search Two Ranges To See If The Same String Exists

    Hello,

    I need to perform a vlookup but programmatically by a loop instead of inserting the workbookfunction. For each string in my first range I need to check to see if it exists in a second range (like a Vlookup formula). I have tried with the following code but its not working. I would really like some help with this please.

    Code:
    Sheets("Weekly Shipped Details").Select
    Dim oLooks As Range
    Set oLooks = Range("I5:I1000").SpecialCells(xlCellTypeConstants)
    '--------------------------------------------------------------------------------------------------------------
    ' VLOOKUP AGAINST CONTAINER NUMBER TO SEE IF CONTAINER WAS ON LAST WEEKS REPORT. ---------------------------
    
    Sheets("Shipment Report.xls").Select
    
        Dim oCheckCell As Range, oCheckCell2 As Range
    
        Range("I2:I1000").SpecialCells(xlCellTypeConstants).Select
    
        For Each oCheckCell In Selection
        For Each oCheckCell2 In oLooks
        If oCheckCell2 = oCheckCell.Value Then oCheckCell.Offset(0, 1).Value = oCheckCell.Value Else
        oCheckCell.Offset(0, 1).Value = "#N/A"
        Next oCheckCell2
        Next oCheckCell

  2. #2
    Member p45cal's Avatar
    Join Date
    Oct 2013
    Posts
    94
    Rep Power
    12
    make sure the If statement is all on one line:
    Code:
    For Each oCheckCell In Selection
      For Each oCheckCell2 In oLooks
        If oCheckCell2 = oCheckCell.Value Then oCheckCell.Offset(0, 1).Value = oCheckCell.Value Else oCheckCell.Offset(0, 1).Value = "#N/A"
      Next oCheckCell2
    Next oCheckCell

  3. #3
    Thanks p45cal, done that but still populating each cell with "#N/A" even though the string is in both ranges.

  4. #4
    Member p45cal's Avatar
    Join Date
    Oct 2013
    Posts
    94
    Rep Power
    12
    Then it's probably correct. There are probably similar entries but not exactly the same; there could be a small difference such as a leading or trailing space or some such. Can you provide a file where this is happening?

  5. #5
    I have checked both Ranges and some strings are identical and have no extra charaters. The ranges are on two different workbooks so I set the first range when the workbook was active (oLooks) then activated the second workbook to select the second range(selection). I have checked both ranges to make sure they are being used and they are. I'm sorry i cannot post the workbooks are they relate to a specific customer.

  6. #6
    I don't know if this helps in maybe finding an answer but after much testing i can see that the code is searching each cell in the range oLooks but instead of searching against the current selection that I need it to, its searching against the same row in oLooks. So the problem with the code is getting the loops in oLook 'for each' to look back to the selection.

  7. #7
    i have created a mock workbook for all to see but can't find how to upload here?

  8. #8
    Member p45cal's Avatar
    Join Date
    Oct 2013
    Posts
    94
    Rep Power
    12
    try:
    Code:
    Dim oLooks As Range, oLooks2 As Range
    Dim oCheckCell As Range, oCheckCell2 As Range
    Set oLooks = Sheets("Weekly Shipped Details").Range("I5:I1000").SpecialCells(xlCellTypeConstants)
    '--------------------------------------------------------------------------------------------------------------
    ' VLOOKUP AGAINST CONTAINER NUMBER TO SEE IF CONTAINER WAS ON LAST WEEKS REPORT. ---------------------------
    With Sheets("Shipment Report.xls")
      .Select
      Set oLooks2 = .Range("I2:I1000").SpecialCells(xlCellTypeConstants)
    End With
    For Each oCheckCell In oLooks2.Cells
      For Each oCheckCell2 In oLooks.Cells
        If oCheckCell2.Value = oCheckCell.Value.Value Then oCheckCell.Offset(0, 1).Value = oCheckCell.Value Else oCheckCell.Offset(0, 1).Value = "#N/A"
      Next oCheckCell2
    Next oCheckCell
    To upload a file, click on Go Advanced and in the Attachments section below the editing area, click on Manage attachments and follow instructions.

  9. #9

    Test workbook attached.

    Thanksp45cal, the changes you gave still don't work i'm afraid. i tried attaching the workbook and the upload won't work so I tried to zip but still not uploading. Maybe God doesn't want me to resolve this issue

  10. #10
    Member p45cal's Avatar
    Join Date
    Oct 2013
    Posts
    94
    Rep Power
    12
    Is you zip file bigger than 293kb? If so instead of uploading it here, upload it to a file sharing site and provide a link to it in your next message.

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
  •