VBA: How can I control a search&replace to only be valid for a defined sheet?

[Rixn]

I import some text from a web page. The data is separated in rows and columns so it easily slip into cells in a nice row and column manner.

Now, I want to parse some names in one column (column A:A). The names do not reside in any other column. I want to change the content for those cells that is "Alpha Beta" into just "Beta", and I only want the replacing to be carried out on a specific sheet (named "Import").

I've recorded a VBA script and it look like this:

Cells.Replace What:="Alpha Beta", Replacement:="Beta", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

But, when I try different setting for the range I get the same script, I mean, for when I try the search & replace with settings for both the whole workbook and for the current sheet.

How can I control the search & replace with VBA to only be valid for a defined sheet?

 

[rjwebgraphix]

I believe this would do it.

With Sheets("Import")
    .Cells.Replace What:="Alpha Beta", _
            Replacement:="Beta", _
                 LookAt:=xlPart, _
            SearchOrder:=xlByRows, _
              MatchCase:=False, _
           SearchFormat:=False, _
          ReplaceFormat:=False
End With

 

[hiker95]

Rixn,

Sample raw data:

Excel 2007
	A	B
1	Alpha Beta
2		Alpha Beta
3	Alpha Beta
4		Alpha Beta
5
6
7
8	Alpha Beta
9		Alpha Beta
10
11
12
13	Alpha Beta
14		Alpha Beta
15
Import

After the macro:

Excel 2007
	A	B
1	Beta
2		Alpha Beta
3	Beta
4		Alpha Beta
5
6
7
8	Beta
9		Alpha Beta
10
11
12
13	Beta
14		Alpha Beta
15
Import

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Sub Test()

With Sheets("Import")

  .Columns("A:A").Replace What:="Alpha Beta", Replacement:="Beta", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

End With

End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the Test macro.

 

[Rixn]

So, if I understand things correcly I can replace several phrases in the same column by doing this:

With Sheets("Import")
.Columns("A:A").Replace What:="Alpha Beta", Replacement:="Beta", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
.Columns("A:A").Replace What:="Delta Echo", Replacement:="Echo", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
End With

..and for different columns I can do this:

With Sheets("Import")
.Columns("A:A").Replace What:="Alpha Beta", Replacement:="Beta", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
.Columns("B:B").Replace What:="Delta Echo", Replacement:="Echo", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
End With

..or do I have to do this:

With Sheets("Import")
.Columns("A:A").Replace What:="Alpha Beta", Replacement:="Beta", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
End With

With Sheets("Import")
.Columns("B:B").Replace What:="Delta Echo", Replacement:="Echo", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
End With


Last question: How do I get the code tagged as code (I can't see any formatting tools even in the advanced mode)?

 

[rjwebgraphix]

..and for different columns I can do this:

With Sheets("Import")
.Columns("A:A").Replace What:="Alpha Beta", Replacement:="Beta", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
.Columns("B:B").Replace What:="Delta Echo", Replacement:="Echo", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
End With

Last question: How do I get the code tagged as code (I can't see any formatting tools even in the advanced mode)?

That should work. No need for double With statements.

See my signature for how to post code with code tags.

 

[hiker95]

Rixn,

When posting VBA code, please use Code Tags - like this:

[code]

'Paste your code here.

[/code]


This will work correctly:

With Sheets("Import")
  .Columns("A:A").Replace What:="Alpha Beta", Replacement:="Beta", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
  .Columns("B:B").Replace What:="Delta Echo", Replacement:="Echo", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
End With