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