[RESOLVED] VB Find and Replace Loop

[DarkBox121]

Hi. I just started a job that seems to focus on editing code in VB. I have no idea why I am doing it. I seem to run into a lot of issues. Anyway, here is my current problem that I was hoping one of you would not mind answering.

I have a Workbook with 2 Tabs - Everything and Test Page. On the Everything Tab I have a list of phone numbers in Column L and on Test Page I have the results of Filtering out which phone numbers are duplicates.

What I want to do is find them on the Everything Page and Replace them with "Duplicate." Now I have done the Macro and it looks like below:

Sub DupeA()
'
' DupeA Macro
' Remove Dupes
'

'

Range("A3").Select
ActiveCell.FormulaR1C1 = "9546259428"
Selection.Replace What:="9546259428", Replacement:="05-MM-Dupe", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("A4").Select
ActiveCell.FormulaR1C1 = "5615422286"
Selection.Replace What:="5615422286", Replacement:="05-MM-Dupe", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("A5").Select
ActiveCell.FormulaR1C1 = "7863954078"
Selection.Replace What:="7863954078", Replacement:="05-MM-Dupe", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

End Sub

As you can see it will only replace the 3 Ranges that I have selected. I was hoping to create a loop that would do the same thing as above but not be so specific.

My end goal is to be able to press one button and have it replace all the duplicates on the Everything page. However, say the next set of Duplicates are from A3:A25 on the Test Page. I need the loop to be able to adjust.

I have no idea how to do this though. I was looking around on GOOGLE and I came up with just about nothing that I could understand.

[si_the_geek]

Thread moved from "ASP, VB Script" forum to "Office Development/VBA" forum

[westconn1]

as i see it, the simplest method is, assuming all the phonenumbers are in a single column, is to loop from the bottom do a count of duplicates in the column above the cell, if the count is greater than zero then mark duplicate

for i = range("a65536").end(xlup).row to 2 step -1     ' don't check top row, will cause error
     if application.worksheetfunction.countif(Range("A1:a" & i - 1), Range("A" & i).value) > 0 Then range("a" & i).value ="Duplicate"
next

change from column A to your column

[DarkBox121]

This works. The only problem is though that say I have 5 phone numbers and all 5 of them are the same, this will only write "Duplicate" on 4 of them. I need all 5 to be marked "Duplicate."

Also, is there any way to exclude what I have marked as "Flag" and "Blank" I need them to stay that way but they will get changed. Thanks again.

Edit:
I have found a way to exclude the "Flag" and "Blank" by running another version of this same macro before and the opposite after. Basically skips the Value of both.

However, I am struggling to create the way to mark (like in the example above) all 5 phone numbers as "Duplicate". This is what I am running into an error with:

Sub Test3()
'Marks Dupes

    Sheets("Test Sheet").Select


For i = Range("A65536").End(xlUp).Row To 2 Step -1
If Application.WorksheetFunction.CountIf(Range("A1:A" & i - 1), Range("A" & i).Value) > 0 Then Range("A1:A" & i - 1).Value
& Range("A" & i).Value = "Dupe"
Next

It doesn't like the Red & Sign. How else do you list 2 things to change?

[DarkBox121]

I am utterly dumb.

All I had to do was put that step ahead of what I wanted to be marked Blank and Flag. It works perfectly now. Thank you. Onto other issues. Only 1 not resolved but it has nothing to do with this.

Thank you again.