Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
return non matches
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
return non matches
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
return non matches
but i think this line can give u a hint
put in just before Next y2 MsgBox ("") & x1 & " - " & y2 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
return non matches
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
return non matches
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
return non matches
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
return non matches
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
return non matches
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
return non matches
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
return non matches
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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
return non matches
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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
return non matches
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 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
return non matches
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
If value matches criteria, return formula | Excel Worksheet Functions | |||
Return value of non matches in columns | Excel Discussion (Misc queries) | |||
Count values only if matches and return on another worksheet | Excel Worksheet Functions | |||
How do I use lookup to return multiple matches and sum them? | Excel Discussion (Misc queries) | |||
Vlookup to return the sum of multiple matches | Excel Discussion (Misc queries) |