return non matches

[ll]

Hi,
I am working with a script to try and return non matches. I have set
up two ranges and have set up nested loops to compare them. The
problem I'm currently having is that, even with the operator set to
"<>", even the matches get returned for some reason.
Does anyone have any ideas as to why this would do that? (Script
below)
Thanks very much,
Louis
======================
Sub Find_Matches()
Dim CompareRange1 As Variant, x1 As Variant, y1 As Variant
Dim CompareRange2 As Variant, x2 As Variant, y2 As Variant
' Set CompareRange equal to the range to which you will
' compare the selection.
Set CompareRange1 = Worksheets("Sheet1").Range("A1:A5")
Set CompareRange2 = Worksheets("Sheet2").Range("A1:A5")

' Loop through each cell in the selection and compare it to
' each cell in CompareRange.

For Each x1 In CompareRange1
For Each y2 In CompareRange2

If x1 <> y2 Then x1.Offset(0, 1) = x1

Next y2
Next x1
End Sub

 

[Guest]

well i would do it this way:

Sub NewMatch()
Dim i As Long
For i = 1 To 5
If Sheets(1).Cells(i, 1) <> Sheets(2).Cells(i, 1) Then
Sheets(1).Cells(i, 2) = Sheets(2).Cells(i, 1)
End If
Next
End Sub

 

[Guest]

but i think this line can give u a hint
put in just before Next y2

MsgBox ("") & x1 & " - " & y2

 

[Dave Peterson]

Maybe...

Sub Find_Matches()
Dim CompareRange1 As Range
dim x1 as Range
Dim CompareRange2 As Range
dim res as variant

Set CompareRange1 = Worksheets("Sheet1").Range("A1:A5")
Set CompareRange2 = Worksheets("Sheet2").Range("A1:A5")

' Loop through each cell in the selection and compare it to
' each cell in CompareRange.

For Each x1 In CompareRange1
res = application.match(x1,comparerange2,0)
if iserror(res) then
'missing
x1.offset(0,1).value = x1
else
'found
xl.offset(0,1).value = ""
end if
Next x1
End Sub

 

[ll]

Thanks for your help - both of these return some results, but allow me
to clarify what I am trying to achieve:
I need to compare the data in range A with that or range B. It will be
a library listing of books with no identical entries. Range A will
come from the list of books which have been checked out, while Range B
will be the complete listing of books. A comparison of the two will
(hopefully) produce a list of books not checked out, which can populate
a VB Excel Form.
What type of solution would produce the complete list of
not-checked-out books?

Thanks,
Louis

 

[ll]

The main problem was that the suggested scripts would compare "like
rows" but different columns, rather than comparing the first row/first
column (A1) to each row in the second column, and then comparing the
second row/first column (A2) to each row in the second column, and so
on.

 

[ll]

The main problem was that the suggested scripts would compare "like
rows" but different columns, rather than comparing the first row/first
column (A1) to each row in the second column, and then comparing the
second row/first column (A2) to each row in the second column, and so
on.

 

[ll]

The main problem was that the suggested scripts would compare "like
rows" but different columns, rather than comparing the first row/first
column (A1) to each row in the second column, and then comparing the
second row/first column (A2) to each row in the second column, and so
on.

 

[Dave Peterson]

For the code I suggested, if you point comparerange1 to your total list of books
and point comparerange2 to the list of books checked out, what happens?

 

[ll]

Dave,
Thanks - I had the range still on only 5 cells, so for some "strange"
reason it was only returning 5! lol
One more point - as I am wanting to get the results into an Excel VBA
form, will the results go easily into a collection?

Thanks

 

[Dave Peterson]

I'd just use an array. I put this behind a userform that had a listbox on it:

Option Explicit
Private Sub UserForm_Initialize()
Dim CompareRange1 As Range
Dim x1 As Range
Dim CompareRange2 As Range
Dim res As Variant
Dim myArr() As String
Dim iCtr As Long

With Worksheets("sheet1")
Set CompareRange1 = .Range("A1", .Cells(.Rows.Count, "A").End(xlUp))
End With

With Worksheets("Sheet2")
Set CompareRange2 = .Range("A1", .Cells(.Rows.Count, "A").End(xlUp))
End With

' Loop through each cell in the selection and compare it to
' each cell in CompareRange.

ReDim myArr(1 To CompareRange1.Cells.Count)
iCtr = 0
For Each x1 In CompareRange1
res = Application.Match(x1, CompareRange2, 0)
If IsError(res) Then
'missing
iCtr = iCtr + 1
myArr(iCtr) = x1.Value
End If
Next x1

If iCtr = 0 Then
With Me.ListBox1
.AddItem "No Mismatches"
.Enabled = False
End With
Else
ReDim Preserve myArr(1 To iCtr)
With Me.ListBox1
.List = myArr
.Enabled = True
.MultiSelect = fmMultiSelectMulti
End With
End If

End Sub

 

[ll]

Dave,
Thanks for your help - this is working ideally. I see that the multi
select is also activated in the listbox. Very handy!
I had come up with a slightly different script, based in a module with
a collection, which also worked.
What would be an advantage of an array vs collection (is it the
possibility of duplicate values within the array)?

Thanks

 

[Dave Peterson]

If I wanted to avoid duplicates, a Collection is one way to go.

But since they're books that are checked out, wouldn't you want to show multiple
copies being checked out?

John Walkenbach shows how to use a collection to get a unique list. He also
shows how that list can be sorted (you may want to do that):
http://j-walk.com/ss/excel/tips/tip47.htm

Dave,
Thanks for your help - this is working ideally. I see that the multi
select is also activated in the listbox. Very handy!
I had come up with a slightly different script, based in a module with
a collection, which also worked.
What would be an advantage of an array vs collection (is it the
possibility of duplicate values within the array)?

Thanks