EXCEL MACRO - EXCEL VBA HOW TO USE THE FIND AND REPLACE FUNCTION

[FERRiver]

Hello Everyone!!!
I need some help ... please ... I work for a call center - I have a request to review 30,000 contacts .. and send over to other businesss .. so I have to replace the customer address to something like "UNK" for each contact

So if i know the customer information is in C7 how Can use the function of find and replace the customer info text to "UNK" if the data is repeat multiple times in the file...

Please help... Thanks

 

[Gavona]

First without VBA

Edit,Replace.......

When you have got the hang of that switch the macro recorder on and repeat the operation.
Tools, Macro, Record New Macro.....

Now examine your code.
Consider changing it. One thing you will want is
What:=Range("C7").value
If you get stuck, post your code and some sample data and explain exactly what you are trying to achieve.


Gavin

 

[FERRiver]

Hello Gavin,
Thank you for you help...

Here is the macro .. as you can see is replacing the customer info to UNK (A6+A7+A8) in the entire file.. I created the macro by using the find and replace function .. it is able to replace the multiple customer info to "UNK" - How can I change this macro to be use in other file with other customer info files. ????

I tried to change the macro to What:=Range("A7").value but something is missing...

Thank you for your help..




Sub contactformattest()
'
' contactformattest Macro
' Macro recorded 9/16/2008 by riverfe
'

'
Range("A6:A8").Select
Selection.TextToColumns Destination:=Range("A6"), DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(27, 1)), TrailingMinusNumbers:=True
Range("A6").Select
ActiveWindow.SmallScroll Down:=-15
ActiveCell.FormulaR1C1 = "Ms. Terri Wilson"
Cells.Replace What:="Ms. Terri Wilson", Replacement:="UNK", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("A7").Select
ActiveCell.FormulaR1C1 = "14a Main Pl."
Range("A9").Select
Cells.Replace What:="14a Main Pl.", Replacement:="UNK", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("A8").Select
ActiveCell.FormulaR1C1 = "Garfield NJ 07026"
Range("A8").Select
Cells.Replace What:="Garfield NJ 07026", Replacement:="UNK", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveWindow.SmallScroll Down:=243
ActiveWindow.ScrollRow = 243
ActiveWindow.ScrollRow = 242
ActiveWindow.ScrollRow = 241
ActiveWindow.ScrollRow = 240
ActiveWindow.ScrollRow = 239
ActiveWindow.ScrollRow = 237
ActiveWindow.ScrollRow = 235
ActiveWindow.ScrollRow = 234
ActiveWindow.ScrollRow = 230
ActiveWindow.ScrollRow = 227
ActiveWindow.ScrollRow = 223
ActiveWindow.ScrollRow = 217
ActiveWindow.ScrollRow = 211
ActiveWindow.ScrollRow = 206
ActiveWindow.ScrollRow = 200
ActiveWindow.ScrollRow = 193
ActiveWindow.ScrollRow = 183
ActiveWindow.ScrollRow = 173
ActiveWindow.ScrollRow = 160
ActiveWindow.ScrollRow = 147
ActiveWindow.ScrollRow = 133
ActiveWindow.ScrollRow = 120
ActiveWindow.ScrollRow = 107
ActiveWindow.ScrollRow = 96
ActiveWindow.ScrollRow = 89
ActiveWindow.ScrollRow = 84
ActiveWindow.ScrollRow = 79
ActiveWindow.ScrollRow = 74
ActiveWindow.ScrollRow = 70
ActiveWindow.ScrollRow = 65
ActiveWindow.ScrollRow = 60
ActiveWindow.ScrollRow = 56
ActiveWindow.ScrollRow = 53
ActiveWindow.ScrollRow = 49
ActiveWindow.ScrollRow = 47
ActiveWindow.ScrollRow = 44
ActiveWindow.ScrollRow = 42
ActiveWindow.ScrollRow = 39
ActiveWindow.ScrollRow = 36
ActiveWindow.ScrollRow = 33
ActiveWindow.ScrollRow = 30
ActiveWindow.ScrollRow = 26
ActiveWindow.ScrollRow = 23
ActiveWindow.ScrollRow = 20
ActiveWindow.ScrollRow = 17
ActiveWindow.ScrollRow = 14
ActiveWindow.ScrollRow = 11
ActiveWindow.ScrollRow = 9
ActiveWindow.ScrollRow = 6
ActiveWindow.ScrollRow = 4
ActiveWindow.ScrollRow = 3
ActiveWindow.ScrollRow = 1
Rows("6:8").Select
Selection.ClearContents
Range("B13").Select
ActiveWindow.SmallScroll Down:=255
Range("A273:A299").Select
Columns("A:A").ColumnWidth = 90.14
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("A289").Select
ActiveWindow.SmallScroll Down:=-81
ActiveWindow.ScrollRow = 174
ActiveWindow.ScrollRow = 173
ActiveWindow.ScrollRow = 172
ActiveWindow.ScrollRow = 170
ActiveWindow.ScrollRow = 169
ActiveWindow.ScrollRow = 168
ActiveWindow.ScrollRow = 167
ActiveWindow.ScrollRow = 165
ActiveWindow.ScrollRow = 163
ActiveWindow.ScrollRow = 162
ActiveWindow.ScrollRow = 159
ActiveWindow.ScrollRow = 158
ActiveWindow.ScrollRow = 156
ActiveWindow.ScrollRow = 153
ActiveWindow.ScrollRow = 151
ActiveWindow.ScrollRow = 148
ActiveWindow.ScrollRow = 145
ActiveWindow.ScrollRow = 142
ActiveWindow.ScrollRow = 139
ActiveWindow.ScrollRow = 135
ActiveWindow.ScrollRow = 132
ActiveWindow.ScrollRow = 128
ActiveWindow.ScrollRow = 125
ActiveWindow.ScrollRow = 122
ActiveWindow.ScrollRow = 118
ActiveWindow.ScrollRow = 113
ActiveWindow.ScrollRow = 109
ActiveWindow.ScrollRow = 105
ActiveWindow.ScrollRow = 101
ActiveWindow.ScrollRow = 97
ActiveWindow.ScrollRow = 93
ActiveWindow.ScrollRow = 89
ActiveWindow.ScrollRow = 86
ActiveWindow.ScrollRow = 83
ActiveWindow.ScrollRow = 79
ActiveWindow.ScrollRow = 75
ActiveWindow.ScrollRow = 72
ActiveWindow.ScrollRow = 70
ActiveWindow.ScrollRow = 67
ActiveWindow.ScrollRow = 65
ActiveWindow.ScrollRow = 62
ActiveWindow.ScrollRow = 60
ActiveWindow.ScrollRow = 58
ActiveWindow.ScrollRow = 56
ActiveWindow.ScrollRow = 53
ActiveWindow.ScrollRow = 51
ActiveWindow.ScrollRow = 49
ActiveWindow.ScrollRow = 45
ActiveWindow.ScrollRow = 42
ActiveWindow.ScrollRow = 37
ActiveWindow.ScrollRow = 33
ActiveWindow.ScrollRow = 28
ActiveWindow.ScrollRow = 22
ActiveWindow.ScrollRow = 17
ActiveWindow.ScrollRow = 12
ActiveWindow.ScrollRow = 7
ActiveWindow.ScrollRow = 2
ActiveWindow.ScrollRow = 1
Range("A16").Select
End Sub

 

[Andrzejek]

You can delete all lines with:
ActiveWindow.ScrollRow
from your macro.

Have fun.

---- Andy

 

[SkipVought]

Sub contactformattest()
'
' contactformattest Macro
' Macro recorded 9/16/2008 by riverfe
'

'[b]
    'first generate a UNIQUE LIST of CUSTOMERS
    'assume that the UNIQUE LIST of CUSTOMERS is in Sheet2, in column A  beginning in row 1
    'assume that the activesheet is Sheet1[/b]
    Dim r As Range
    With Sheets("Sheet1")
        .Range("A6:A8").TextToColumns _
            Destination:=.Range("A6"), _
            DataType:=xlFixedWidth, _
            FieldInfo:=Array(Array(0, 1), Array(27, 1)), _
            TrailingMinusNumbers:=True
        For Each r In Sheets("Sheet2").Range(Sheets("Sheet2").[A1], Sheets("Sheet2").[A1].End(xlDown))
            .Cells.Replace _
                What:=r.Value, _
                Replacement:="UNK", _
                LookAt:=xlPart, _
                SearchOrder:=xlByRows, _
                MatchCase:=False, _
                SearchFormat:=False, _
                ReplaceFormat:=False
        Next
    End With
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[FERRiver]

OK
Skip thanks for the code .. but to get all the names and address for all the contact it will be a lot time ...

What I need to do is to seach for each text located in A6, A7,A8
Then Look for that text in the entire sheet and replaced to UNK.

I need to run these macro in 3,000 files so it will delete the customer info from the file ...

I believe I have to use the value function so it will find and replace the date located (A6:A8)

Please Help !!! Thanks


Sub contactformattest()
'
' contactformattest Macro
' Macro recorded 9/16/2008 by riverfe
'

'
Range("A6:A8").Select
Selection.TextToColumns Destination:=Range("A6"), DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(27, 1)), TrailingMinusNumbers:=True
Range("A6").Select
ActiveWindow.SmallScroll Down:=-15
ActiveCell.FormulaR1C1 = "Ms. Terri Wilson"
Cells.Replace What:="Ms. Terri Wilson", Replacement:="UNK", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("A7").Select
ActiveCell.FormulaR1C1 = "14a Main Pl."
Range("A9").Select
Cells.Replace What:="14a Main Pl.", Replacement:="UNK", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("A8").Select
ActiveCell.FormulaR1C1 = "Garfield NJ 07026"
Range("A8").Select
Cells.Replace What:="Garfield NJ 07026", Replacement:="UNK", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

 

[SkipVought]

"...but to get all the names and address for all the contact it will be a lot time ..."

Is that news? How else do you propose to get them? One at a time? (Six of one. Half a dozen of the other.)

What do you need help on, exactly?

    Sheets("Sheet1").Cells.Replace _
                What:=[i][b][/b]SomeValue[/i], _
                Replacement:="UNK", _
                LookAt:=xlPart, _
                SearchOrder:=xlByRows, _
                MatchCase:=False, _
                SearchFormat:=False, _
                ReplaceFormat:=False

You dont' need Range("A6:A8").Select et al.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}