Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare Sheets values by .find loop?
greetings
i am stuck, i have two lists, both with the same information, but in differnt places what i need is this If Cell A1. value = "Anything on sheet (2) Column A" then do nothing else if Cell A1. Value < "Anything on sheet (2) Column A" then highlight A1, I know not too tough, but here is where i get stuck.. After searching for Cell A1.value i need to search the rest of column A's cells against Sheet (2) Column A any help would be great. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare Sheets values by .find loop?
On Apr 6, 7:41*am, Office_Novice
wrote: greetings i am stuck, i have two lists, both with the same information, but in differnt places what i need is *this If Cell A1. value = "Anything on sheet (2) Column A" then do nothing else if Cell A1. Value < *"Anything on sheet (2) Column A" then highlight *A1, I know not too tough, but here is where i get stuck.. After searching for Cell A1.value i need to search the rest of column A's cells against Sheet (2) Column A any help would be great. Hi, Sub compareVals() Dim SrcList As Range, ChkList As Range Dim cell As Range, FoundRng As Range Set SrcList = Range("C1:C19") Set ChkList = Range("A1:A19") On Error Resume Next For Each cell In SrcList Set FoundRng = ChkList.Find(cell) If FoundRng Is Nothing Then MsgBox "No match for " & cell & " in " & ChkList.Address Else MsgBox "Found " & cell & " in " & ChkList.Address End If Next End Sub This will loop through one range and let you know if it finds the cell from range 1 in range 2 or not. Just change the definition of the ranges to match you needs. Cheers, Ivan. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare Sheets values by .find loop?
That didnt work at all. Thanks for trying though.
I have written this & it does what i want but only if i manualy change the active cell stop and restart the macro Could use some help modifying the code Option Explicit Sub compareRng() Dim Cell As Range With Worksheets(1).Range("C2:C6000") Set Cell = .Find(What:=ActiveCell.Value, LookIn:=xlValues) Do If Not Cell Is Nothing Then Cell.Interior.ColorIndex = 6 ElseIf Cell Is Nothing Then ActiveCell.Interior.ColorIndex = 3 End If ActiveCell.Offset(1, 0).Select Loop End With End Sub "Ivyleaf" wrote: On Apr 6, 7:41 am, Office_Novice wrote: greetings i am stuck, i have two lists, both with the same information, but in differnt places what i need is this If Cell A1. value = "Anything on sheet (2) Column A" then do nothing else if Cell A1. Value < "Anything on sheet (2) Column A" then highlight A1, I know not too tough, but here is where i get stuck.. After searching for Cell A1.value i need to search the rest of column A's cells against Sheet (2) Column A any help would be great. Hi, Sub compareVals() Dim SrcList As Range, ChkList As Range Dim cell As Range, FoundRng As Range Set SrcList = Range("C1:C19") Set ChkList = Range("A1:A19") On Error Resume Next For Each cell In SrcList Set FoundRng = ChkList.Find(cell) If FoundRng Is Nothing Then MsgBox "No match for " & cell & " in " & ChkList.Address Else MsgBox "Found " & cell & " in " & ChkList.Address End If Next End Sub This will loop through one range and let you know if it finds the cell from range 1 in range 2 or not. Just change the definition of the ranges to match you needs. Cheers, Ivan. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare Sheets values by .find loop?
On Apr 7, 8:25*am, Office_Novice
wrote: That didnt work at all. Thanks for trying though. I have written this & it does what i want but only if i manualy change the active cell stop and restart the macro Could use some help modifying the code Option Explicit Sub compareRng() * Dim Cell As Range * With Worksheets(1).Range("C2:C6000") * Set Cell = .Find(What:=ActiveCell.Value, LookIn:=xlValues) * Do * * If Not Cell Is Nothing Then * * * * Cell.Interior.ColorIndex = 6 * * ElseIf Cell Is Nothing Then * * * * ActiveCell.Interior.ColorIndex = 3 * * End If * * *ActiveCell.Offset(1, 0).Select * *Loop * End With End Sub "Ivyleaf" wrote: On Apr 6, 7:41 am, Office_Novice wrote: greetings i am stuck, i have two lists, both with the same information, but in differnt places what i need is *this If Cell A1. value = "Anything on sheet (2) Column A" then do nothing else if Cell A1. Value < *"Anything on sheet (2) Column A" then highlight *A1, I know not too tough, but here is where i get stuck.. After searching for Cell A1.value i need to search the rest of column A's cells against Sheet (2) Column A any help would be great. Hi, Sub compareVals() * Dim SrcList As Range, ChkList As Range * Dim cell As Range, FoundRng As Range * Set SrcList = Range("C1:C19") * Set ChkList = Range("A1:A19") * On Error Resume Next * For Each cell In SrcList * * Set FoundRng = ChkList.Find(cell) * * If FoundRng Is Nothing Then * * * MsgBox "No match for " & cell & " in " & ChkList.Address * * * Else * * * MsgBox "Found " & cell & " in " & ChkList.Address * * End If * *Next End Sub This will loop through one range and let you know if it finds the cell from range 1 in range 2 or not. Just change the definition of the ranges to match you needs. Cheers, Ivan.- Hide quoted text - - Show quoted text - Hi, I'm puzzled that the code I posted didn't work for you. When you say it didn't work, do you mean you were getting an error, or that it wasn't finding anything or something else? I would say if it didn't find what you expected, that you haven't redefined the ranges quickly. Using my original code, you would have to change the following lines: Set SrcList = Range("C1:C19") Set ChkList = Range("A1:A19") to: Set SrcList = Sheets(???).Range("???:???") Set ChkList = Sheets(1).Range("C2:C6000") Just replace the question marks with the correct range... you haven't said where the list of values you want to check for is located. Cheers, Ivan. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare Sheets values by .find loop?
you're earlier post only returnedthe Cell adress in the msgbox. I needed
somthing more like this Sub compareVals() Dim Cell As Range With Worksheets(1).Range("C2:C60000") Do On Error Resume Next Set Cell = .Find(What:=ActiveCell.Value, LookIn:=xlValues) If Not Cell Is Nothing Then Cell.Interior.ColorIndex = 6 ElseIf Cell Is Nothing Then ActiveCell.Interior.ColorIndex = 3 End If ActiveCell.Offset(1, 0).Select Loop Until ActiveCell = "" End With End Sub "Ivyleaf" wrote: On Apr 7, 8:25 am, Office_Novice wrote: That didnt work at all. Thanks for trying though. I have written this & it does what i want but only if i manualy change the active cell stop and restart the macro Could use some help modifying the code Option Explicit Sub compareRng() Dim Cell As Range With Worksheets(1).Range("C2:C6000") Set Cell = .Find(What:=ActiveCell.Value, LookIn:=xlValues) Do If Not Cell Is Nothing Then Cell.Interior.ColorIndex = 6 ElseIf Cell Is Nothing Then ActiveCell.Interior.ColorIndex = 3 End If ActiveCell.Offset(1, 0).Select Loop End With End Sub "Ivyleaf" wrote: On Apr 6, 7:41 am, Office_Novice wrote: greetings i am stuck, i have two lists, both with the same information, but in differnt places what i need is this If Cell A1. value = "Anything on sheet (2) Column A" then do nothing else if Cell A1. Value < "Anything on sheet (2) Column A" then highlight A1, I know not too tough, but here is where i get stuck.. After searching for Cell A1.value i need to search the rest of column A's cells against Sheet (2) Column A any help would be great. Hi, Sub compareVals() Dim SrcList As Range, ChkList As Range Dim cell As Range, FoundRng As Range Set SrcList = Range("C1:C19") Set ChkList = Range("A1:A19") On Error Resume Next For Each cell In SrcList Set FoundRng = ChkList.Find(cell) If FoundRng Is Nothing Then MsgBox "No match for " & cell & " in " & ChkList.Address Else MsgBox "Found " & cell & " in " & ChkList.Address End If Next End Sub This will loop through one range and let you know if it finds the cell from range 1 in range 2 or not. Just change the definition of the ranges to match you needs. Cheers, Ivan.- Hide quoted text - - Show quoted text - Hi, I'm puzzled that the code I posted didn't work for you. When you say it didn't work, do you mean you were getting an error, or that it wasn't finding anything or something else? I would say if it didn't find what you expected, that you haven't redefined the ranges quickly. Using my original code, you would have to change the following lines: Set SrcList = Range("C1:C19") Set ChkList = Range("A1:A19") to: Set SrcList = Sheets(???).Range("???:???") Set ChkList = Sheets(1).Range("C2:C6000") Just replace the question marks with the correct range... you haven't said where the list of values you want to check for is located. Cheers, Ivan. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare Sheets values by .find loop?
On Apr 7, 10:22*am, Office_Novice
wrote: you're earlier post only returnedthe Cell adress in the msgbox. *I needed somthing more like this Sub compareVals() * Dim Cell As Range * With Worksheets(1).Range("C2:C60000") * Do * On Error Resume Next * Set Cell = .Find(What:=ActiveCell.Value, LookIn:=xlValues) * * If Not Cell Is Nothing Then * * * * Cell.Interior.ColorIndex = 6 * *ElseIf Cell Is Nothing Then * * * ActiveCell.Interior.ColorIndex = 3 * * End If * * *ActiveCell.Offset(1, 0).Select * * Loop Until ActiveCell = "" * End With End Sub "Ivyleaf" wrote: On Apr 7, 8:25 am, Office_Novice wrote: That didnt work at all. Thanks for trying though. I have written this & it does what i want but only if i manualy change the active cell stop and restart the macro Could use some help modifying the code Option Explicit Sub compareRng() * Dim Cell As Range * With Worksheets(1).Range("C2:C6000") * Set Cell = .Find(What:=ActiveCell.Value, LookIn:=xlValues) * Do * * If Not Cell Is Nothing Then * * * * Cell.Interior.ColorIndex = 6 * * ElseIf Cell Is Nothing Then * * * * ActiveCell.Interior.ColorIndex = 3 * * End If * * *ActiveCell.Offset(1, 0).Select * *Loop * End With End Sub "Ivyleaf" wrote: On Apr 6, 7:41 am, Office_Novice wrote: greetings i am stuck, i have two lists, both with the same information, but in differnt places what i need is *this If Cell A1. value = "Anything on sheet (2) Column A" then do nothing else if Cell A1. Value < *"Anything on sheet (2) Column A" then highlight *A1, I know not too tough, but here is where i get stuck.. After searching for Cell A1.value i need to search the rest of column A's cells against Sheet (2) Column A any help would be great. Hi, Sub compareVals() * Dim SrcList As Range, ChkList As Range * Dim cell As Range, FoundRng As Range * Set SrcList = Range("C1:C19") * Set ChkList = Range("A1:A19") * On Error Resume Next * For Each cell In SrcList * * Set FoundRng = ChkList.Find(cell) * * If FoundRng Is Nothing Then * * * MsgBox "No match for " & cell & " in " & ChkList.Address * * * Else * * * MsgBox "Found " & cell & " in " & ChkList.Address * * End If * *Next End Sub This will loop through one range and let you know if it finds the cell from range 1 in range 2 or not. Just change the definition of the ranges to match you needs. Cheers, Ivan.- Hide quoted text - - Show quoted text - Hi, I'm puzzled that the code I posted didn't work for you. When you say it didn't work, do you mean you were getting an error, or that it wasn't finding anything or something else? I would say if it didn't find what you expected, that you haven't redefined the ranges quickly. Using my original code, you would have to change the following lines: * Set SrcList = Range("C1:C19") * Set ChkList = Range("A1:A19") to: * Set SrcList = Sheets(???).Range("???:???") * Set ChkList = Sheets(1).Range("C2:C6000") Just replace the question marks with the correct range... you haven't said where the list of values you want to check for is located. Cheers, Ivan.- Hide quoted text - - Show quoted text - Hi, Sorry, I obviously didn't clarify my initial code properly. It was only designed to be a proof of concept as I thought from the sound of your post the main trouble you were having was with looping through the list and finding the values. As such, I thought you could just replace the appropriate msgbox line with whatever you wanted to happen - i.e. change colour of the cell. The following code should (maybe) be more suitable: Sub compareVals() Dim SrcList As Range, ChkList As Range Dim cell As Range, FoundRng As Range Set SrcList = Intersect(Sheets(2).Columns(1),Sheets(2).UsedRange ) Set ChkList = Intersect(Sheets(1).Columns(3),Sheets(1).UsedRange ) On Error Resume Next For Each cell In SrcList Set FoundRng = ChkList.Find(cell, LookAt:=xlWhole) If FoundRng Is Nothing Then cell.Interior.ColorIndex = 6 End If Next For Each cell In ChkList Set FoundRng = SrcList.Find(cell, LookAt:=xlWhole) If FoundRng Is Nothing Then cell.Interior.ColorIndex = 6 End If Next End Sub |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare Sheets values by .find loop?
Another approach. try this one.
If your range is different from below, then change With Worksheets("Sheet1") Set src = Range(.Cells(1, "A"), .Cells(1, "A").End(xlDown)) End With and Set dst = Worksheets("Sheet2").Range("C2:C6000") to suitable for your case. Sub Comparetest() Dim Cell As Range, src As Range, dst As Range Dim k With Worksheets("Sheet1") Set src = Range(.Cells(1, "A"), .Cells(1, "A").End(xlDown)) End With Set dst = Worksheets("Sheet2").Range("C2:C6000") With Application For Each Cell In src k = .Match(Cell.Value, dst, 0) If Not IsError(k) Then .Index(dst, k, 1).Interior.ColorIndex = 6 Else Cell.Interior.ColorIndex = 3 End If Next End With End Sub keiji "Office_Novice" wrote in message ... That didnt work at all. Thanks for trying though. I have written this & it does what i want but only if i manualy change the active cell stop and restart the macro Could use some help modifying the code Option Explicit Sub compareRng() Dim Cell As Range With Worksheets(1).Range("C2:C6000") Set Cell = .Find(What:=ActiveCell.Value, LookIn:=xlValues) Do If Not Cell Is Nothing Then Cell.Interior.ColorIndex = 6 ElseIf Cell Is Nothing Then ActiveCell.Interior.ColorIndex = 3 End If ActiveCell.Offset(1, 0).Select Loop End With End Sub "Ivyleaf" wrote: On Apr 6, 7:41 am, Office_Novice wrote: greetings i am stuck, i have two lists, both with the same information, but in differnt places what i need is this If Cell A1. value = "Anything on sheet (2) Column A" then do nothing else if Cell A1. Value < "Anything on sheet (2) Column A" then highlight A1, I know not too tough, but here is where i get stuck.. After searching for Cell A1.value i need to search the rest of column A's cells against Sheet (2) Column A any help would be great. Hi, Sub compareVals() Dim SrcList As Range, ChkList As Range Dim cell As Range, FoundRng As Range Set SrcList = Range("C1:C19") Set ChkList = Range("A1:A19") On Error Resume Next For Each cell In SrcList Set FoundRng = ChkList.Find(cell) If FoundRng Is Nothing Then MsgBox "No match for " & cell & " in " & ChkList.Address Else MsgBox "Found " & cell & " in " & ChkList.Address End If Next End Sub This will loop through one range and let you know if it finds the cell from range 1 in range 2 or not. Just change the definition of the ranges to match you needs. Cheers, Ivan. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how can I compare two data sheets to find variances? | Excel Discussion (Misc queries) | |||
Compare Sheets values in two colums | Excel Worksheet Functions | |||
Loop Down Columns and Compare Values | Excel Programming | |||
Compare values in multiple sheets | Excel Programming | |||
compare two cell values on different sheets | Excel Programming |