Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Match or Find Function Help Please
The following code works to find and highlight exact matches from a set
list within a range of cells. I want it to also find matches within cells that aren't exact matches. This would be same as using Find with wildcards. Example: I would type *test* and click Find All which would return all cell that contain "test" and also words like testing", restest, etc. Any thoughts on how I can do this? Sub HighlightInAandInB(ByVal Column1 As Range, _ ByVal Column2 As Range, Color As Long) Dim Cll As Range 'Limit to the used range, to speed it up Set Column1 = Intersect(Column1, Column1.Worksheet.UsedRange) Set Column2 = Intersect(Column2, Column2.Worksheet.UsedRange) 'Remove the header Set Column1 = Column1.Offset(1).Resize(Column1.Rows.Count - 1) Set Column2 = Column2.Offset(1).Resize(Column2.Rows.Count - 1) 'Loop through the cells For Each Cll In Column1.Cells 'Use the MATCH() function to see if the value is in there If IsNumeric(Application.Match(Cll.Value, Column2, 1)) Then 'It is, so highlight it Cll.Interior.Color = Color 'To delete the cell, use 'Cll.Delete Shift:=xlShiftUp End If Next Cll End Sub Sub HighlightInANotInB(ByVal Column1 As Range, _ ByVal Column2 As Range, Color As Long) Dim Cll As Range 'Limit to the used range, to speed it up Set Column1 = Intersect(Column1, Column1.Worksheet.UsedRange) Set Column2 = Intersect(Column2, Column2.Worksheet.UsedRange) 'Remove the header Set Column1 = Column1.Offset(1).Resize(Column1.Rows.Count - 1) Set Column2 = Column2.Offset(1).Resize(Column2.Rows.Count - 1) 'Loop through the cells For Each Cll In Column1.Cells 'Use the MATCH() function to see if the value is in there If IsError(Application.Match(Cll.Value, Column2, 0)) Then 'Is not, so highlight it Cll.Interior.Color = Color 'To delete the cell, use 'Cll.Delete Shift:=xlShiftUp End If Next Cll End Sub Sub UniqueList(ByVal Column1 As Range, ByVal Column2 As Range, _ RngDest As Range) Dim WS As Worksheet 'We'll use a temporary worksheet to use Advanced Filter on it Set WS = Workbooks.Add(xlWorksheet).Worksheets(1) 'Put the first column WS.Range("A1").Resize(Column1.Rows.Count).Value = Column1.Value 'Put the second column, we have to skip one row, which is 'the heading WS.Range("A1").Offset(Column1.Rows.Count).Resize( _ Column2.Rows.Count - 1).Value = Column2.Offset(1).Resize( _ Column2.Rows.Count - 1).Value 'Now, use advanced filter and put the results directly in 'the destination range WS.Range("A:A").AdvancedFilter Action:=xlFilterCopy, _ CopyToRange:=RngDest, Unique:=True 'Close the temp workbook without saving WS.Parent.Close SaveChanges:=False End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Match or Find Function Help Please
If using Edit=find with wildcards works, then turn on the macro recorder
while you do it manually (with the wildcards). This will give you the initial find command. then use the sample code in the FindNext command to see how to fine all occurances. -- Regards, Tom Ogilvy " wrote: The following code works to find and highlight exact matches from a set list within a range of cells. I want it to also find matches within cells that aren't exact matches. This would be same as using Find with wildcards. Example: I would type *test* and click Find All which would return all cell that contain "test" and also words like testing", restest, etc. Any thoughts on how I can do this? Sub HighlightInAandInB(ByVal Column1 As Range, _ ByVal Column2 As Range, Color As Long) Dim Cll As Range 'Limit to the used range, to speed it up Set Column1 = Intersect(Column1, Column1.Worksheet.UsedRange) Set Column2 = Intersect(Column2, Column2.Worksheet.UsedRange) 'Remove the header Set Column1 = Column1.Offset(1).Resize(Column1.Rows.Count - 1) Set Column2 = Column2.Offset(1).Resize(Column2.Rows.Count - 1) 'Loop through the cells For Each Cll In Column1.Cells 'Use the MATCH() function to see if the value is in there If IsNumeric(Application.Match(Cll.Value, Column2, 1)) Then 'It is, so highlight it Cll.Interior.Color = Color 'To delete the cell, use 'Cll.Delete Shift:=xlShiftUp End If Next Cll End Sub Sub HighlightInANotInB(ByVal Column1 As Range, _ ByVal Column2 As Range, Color As Long) Dim Cll As Range 'Limit to the used range, to speed it up Set Column1 = Intersect(Column1, Column1.Worksheet.UsedRange) Set Column2 = Intersect(Column2, Column2.Worksheet.UsedRange) 'Remove the header Set Column1 = Column1.Offset(1).Resize(Column1.Rows.Count - 1) Set Column2 = Column2.Offset(1).Resize(Column2.Rows.Count - 1) 'Loop through the cells For Each Cll In Column1.Cells 'Use the MATCH() function to see if the value is in there If IsError(Application.Match(Cll.Value, Column2, 0)) Then 'Is not, so highlight it Cll.Interior.Color = Color 'To delete the cell, use 'Cll.Delete Shift:=xlShiftUp End If Next Cll End Sub Sub UniqueList(ByVal Column1 As Range, ByVal Column2 As Range, _ RngDest As Range) Dim WS As Worksheet 'We'll use a temporary worksheet to use Advanced Filter on it Set WS = Workbooks.Add(xlWorksheet).Worksheets(1) 'Put the first column WS.Range("A1").Resize(Column1.Rows.Count).Value = Column1.Value 'Put the second column, we have to skip one row, which is 'the heading WS.Range("A1").Offset(Column1.Rows.Count).Resize( _ Column2.Rows.Count - 1).Value = Column2.Offset(1).Resize( _ Column2.Rows.Count - 1).Value 'Now, use advanced filter and put the results directly in 'the destination range WS.Range("A:A").AdvancedFilter Action:=xlFilterCopy, _ CopyToRange:=RngDest, Unique:=True 'Close the temp workbook without saving WS.Parent.Close SaveChanges:=False End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Match or Find Function Help Please
Thanks Tom, I use that trick often however for some reason it doesn't
show the code when I record the macro. Any other thoughts? Matt |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Match or Find Function Help Please
do you have merged cells. Do you have hidden rows or columns.
Those can cause problems - I can't think of any others. -- Regards, Tom Ogilvy " wrote: Thanks Tom, I use that trick often however for some reason it doesn't show the code when I record the macro. Any other thoughts? Matt |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Match or Find Function Help Please
Nope, just to regular sheets nothing hidden. I've have the same thing
happen before in trying to record the visio find and replace code. It just doesn't show for some reason. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
find the second match using the match function | Excel Worksheet Functions | |||
Find an exact match and go to that match | Excel Discussion (Misc queries) | |||
Find Exact Match using INDEX, MATCH | Excel Worksheet Functions | |||
Match Function Problem - Won't Find Certain Numbers | Excel Discussion (Misc queries) | |||
MATCH function - how to find multiple matches ?? | Excel Programming |