P
Pat
I've got a worksheet which contains several lists with numbers
(different ranges), which I want to verify and mark for duplicates.
Now if I use the 'find all' function from excel, the popup shows a list
of cells which correspond to the looked up value. I want to use that
result in my code to decide, if there is more than 1 match. But I have
no clue, on how to access that information. If I do the following 'IF -
THEN' statement (just a littel testversion - and for all you hard core
programmers out there: I'm a bloody beginner, so please excuse if its
not state of the art), its checking for the looked up value rather than
the instances:
Sub MarkDuplicates()
Cycles = 600
CI = 3
OS = 0
Range("DataArea").Select
LV = ActiveCell()
For j = 1 To Cycles
If Selection.Find(What:=LV) > 1 Then
Application.ReplaceFormat.Interior.ColorIndex = CI
Selection.Replace What:=LV, Replacement:="",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=True
CI = CI + 1
End If
OS = OS + 1
LV = ActiveCell.Offset(OS, 0)
Next j
End Sub
Can You please help me out and let me know, how I can solve this task?
thanks in advance for any sugestions.
(different ranges), which I want to verify and mark for duplicates.
Now if I use the 'find all' function from excel, the popup shows a list
of cells which correspond to the looked up value. I want to use that
result in my code to decide, if there is more than 1 match. But I have
no clue, on how to access that information. If I do the following 'IF -
THEN' statement (just a littel testversion - and for all you hard core
programmers out there: I'm a bloody beginner, so please excuse if its
not state of the art), its checking for the looked up value rather than
the instances:
Sub MarkDuplicates()
Cycles = 600
CI = 3
OS = 0
Range("DataArea").Select
LV = ActiveCell()
For j = 1 To Cycles
If Selection.Find(What:=LV) > 1 Then
Application.ReplaceFormat.Interior.ColorIndex = CI
Selection.Replace What:=LV, Replacement:="",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=True
CI = CI + 1
End If
OS = OS + 1
LV = ActiveCell.Offset(OS, 0)
Next j
End Sub
Can You please help me out and let me know, how I can solve this task?
thanks in advance for any sugestions.