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

[excelent]

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

[excelent]

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 wrote:
>
> 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

--

Dave Peterson

[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 wrote:
> 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]

ll wrote:
> 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 wrote:
> > 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]

ll wrote:
> 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 wrote:
> > 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

[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 wrote:
>
> 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

--

Dave Peterson

[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 wrote:
> 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 wrote:
> >
> > 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
>
> --
>
> Dave Peterson

[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 wrote:
>
> 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 wrote:
> > 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 wrote:
> > >
> > > 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
> >
> > --
> >
> > Dave Peterson

--

Dave Peterson

[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 wrote:
> 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 wrote:
> >
> > 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 wrote:
> > > 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 wrote:
> > > >
> > > > 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
> > >
> > > --
> > >
> > > Dave Peterson
>
> --
>
> Dave Peterson

[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

ll wrote:
>
> 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 wrote:
> > 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 wrote:
> > >
> > > 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 wrote:
> > > > 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 wrote:
> > > > >
> > > > > 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
> > > >
> > > > --
> > > >
> > > > Dave Peterson
> >
> > --
> >
> > Dave Peterson

--

Dave Peterson