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

[bakerman]

Sheets("Shipment Report.xls").Select
Is this really the sheetname or the workbookname where the other sheet is in ?

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 ???

[xander1981]

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.


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

[bakerman]

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%

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

[snb]

Another caveat: avoid 'select' and 'activate' in VBA

probably some more speed gain, using


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

[bakerman]

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

[snb]

Very disappointed... ;)

Alternative:


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

[bakerman]

Eureka.
0.03sec :thumbsup::notworthy:

[snb]

Thank you for testing ;)