Search Two Ranges To See If The Same String Exists

[xander1981]

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.

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

[p45cal]

make sure the If statement is all on one line:

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

[xander1981]

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

[p45cal]

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?

[xander1981]

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.

[xander1981]

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.

[xander1981]

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

[p45cal]

try:

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.

[xander1981]

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

[p45cal]

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.