CODEWITHSUNDEEP
vbaexcel
user434885
user434885

Reputation: 2018

excel macro to search for specific string

i need to write and excel macro to search for a string and replace it with another... how do i do this ? HELP :/

Upvotes: 0

Views: 6461

Answers (2)

Robert Mearns
Robert Mearns

Reputation: 11996

Using user interface in Excel 2007

  1. Select cell A1
  2. Click on the Home tab in the Ribbon
  3. Click on the 'Find & Select' icon and select 'Replace'
  4. Click on the 'Options' button
  5. Enter the text to search for 'abc' in the 'Find What' box
  6. Enter the text to replace 'abc' with in the 'Replace with' box
  7. Make sure that the tick boxes are not ticked
  8. Make sure Within = Sheet, Search = By Rows and Look in = Formulas
  9. Click on the 'Replace all' button

Replace screenshot

Using VBA code

Sub Replace_abc()

    Sheets("Sheet1").Select
    Range("A1").Select
    Cells.Replace What:="abc", Replacement:="def", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

End Sub

Upvotes: 2

William Zhou
William Zhou

Reputation: 46

You can record that operations, and Alt+Shift+F11 to open the Script Editor, then reference the code generated by the Recorder. I think that can give you some hints.

And I think you don't need to use Macro. It is enough to use the "Find and Replace" Menu Item, and choose the option you want.

Upvotes: 2

Related Questions