Find and Replace Formula Needed in Excel

Question

I have a big problem with replacing data in an Excel-sheet. There are more that 6000 keyword and more that 300000 rows in the main sheet. I have to replace those keywords in the next row of the original text row. I am trying this formula:

Sub ReplaceExample()

Dim OriginalText As String
Dim CorrectedText As String

OriginalText = Range("A2").Value
CorrectedText = Replace(OriginalText, "left", "Left")
CorrectedText = Replace(OriginalText, "limited", "Limited")


Range("A2").Offset(, 1).Value = CorrectedText 
End Sub

But I am having a problem with Range(). I have a huge data range in the Excel-sheet.

Can I use a single formula for all those replacements???

For Example: this is a sample data for original text cell Number (A2:A12)

00-00 Buick Lesabre (limited) headlight Set;
00-00 Buick Lesabre Tail Light left;
00-00 Buick Lesabre Tail Light Pair;
00-00 Buick lesabre tail Light Right;
00-00 Buick Lesabre Tail Light Set;
00-00 Dodge /Plymouth Neon Fog Light Pair;
00-00 Dodge caravan/Plymouth voyager/ Chrysler towncar(W/ Quad) Headlight Left;
00-00 Dodge Caravan/voyager /Towncar(W/O Quad&Dtrl)Headlight Set;
00-00 Dodge/Plymouth Neon Fog Light Left;
00-00 Dodge/Plymouth Neon Fog Light Right;
2000 Nissian 350z;

Keywords cell number (A2:A8)

Limited;
Left;
Lesabre;
Caravan;
Voyager;
350Z;
Towncar

I need to solve it quickly.

Does it have to be a VBA solution? Why not just use Control F to perform find/replace? — mason, Mar 21 '14 at 13:26
You are going to have to incorporate .FindNext and .Replace to accomplish this. Start with the code found on this site: http://msdn.microsoft.com/en-us/library/office/ff196143%28v=office.15%29.aspx — , Mar 21 '14 at 14:20

user1759942 · Answer 1 · 2014-03-21T14:22:48.743

1

do this:

insert 2 columns at the beginning, in A column put all keywords. in b columns put all replacements. start at the top in the first row. then use this macro:

Sub macro1()


    Dim rng As Range
    Set rng = thisWorkbook.Activesheet.Range("A1")'uses thisWorkbook and Activesheet such that this macro will work regardless of the name of the sheet or workbook.
    While rng.FormulaR1C1 <> "" 'processes untill a blank cell is encountered
        Cells.Replace What:=rng.FormulaR1C1, Replacement:=rng.Offset(0, 1), LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
        Set rng = rng.Offset(1, 0)
    Wend

End Sub

you'll want to paste this macro in the same module as the one you already have underneath your current macro. Alternatively you can simply delete everything between Sub ReplaceExample() and end sub in your macro, and copy everything in my example between Sub macro1() and end sub and paste it where your code was.

edited Mar 21 '14 at 14:22

answered Mar 21 '14 at 13:29

user1759942

1,322
4
14
33

change the `Range("A1").Activate` to A2 if you need. – user1759942 Mar 21 '14 at 13:31
1

You should avoid the use of `.Activate` or `ActiveCell` since it could lead to instabilities and will slow the process. **[This post](http://stackoverflow.com/a/10717999/2687063)** describes some solutions about this matter. – simpLE MAn Mar 21 '14 at 13:43
you're right and normally I'd totally avoid that. But I don't think OP is ever going to look at the again after today, and Im pretty sure it's not some in depth function we're building here, so if they stick `application.screenupdating = false` at the beginning it should run fine for what they need. – user1759942 Mar 21 '14 at 13:46
You should always post code that is at the best of your knowledge, you cannot imply OP's use of your code. Moreover, this is a learning site therefore, you should always show best practices in your answers. I think that the code you posted would be a code that someone who know programming would write for a quick tool because he or she knows the potential consequences of using this structure, so it should not be aimed at someone who's learning. – simpLE MAn Mar 21 '14 at 14:00
1

+1 if you explicitly define `Range("A1")` (i.e.: `ThisWorkbook.Worksheet("Sheet1").Range...` or `Me.Range...` or else) and that you explain where to paste this macro in-line with your definition of `Range("A1")` – simpLE MAn Mar 21 '14 at 14:16
1

determined to make a teacher out of me yet eh? editz complete :P – user1759942 Mar 21 '14 at 14:26

Find and Replace Formula Needed in Excel

1 Answers1