CODEWITHSUNDEEP
excelvba
posfan12
posfan12

Reputation: 2651

Limit search & replace macro to one column on one page

I have the following Excel macro:

Rem Attribute VBA_ModuleType=VBAModule
Sub FixPlatforms()
    Dim fndList As Object
    Set fndList = CreateObject("Scripting.Dictionary")
    fndList.Add "3DO Interactive Multiplayer", "3DO"
    fndList.Add "Nintendo 3DS", "3DS"
    fndList.Add "Ajax", "AJAX"
    fndList.Add "Xerox Alto", "ALTO"
    fndList.Add "Amiga CD32", "AMI32"
    fndList.Add "Amiga", "AMI"
    fndList.Add "Apple I", "APPI"
    fndList.Add "Apple IIe", "APPIIE"
    fndList.Add "Apple IIGS", "APPGS"
    fndList.Add "Apple II Plus", "APPII+"
    fndList.Add "Apple II series", "APPII"
    fndList.Add "Apple II", "APPII"

    For Each sht In ActiveWorkbook.Worksheets
    For Each strKey In fndList.Keys()
        sht.Cells.Replace What:=strKey, Replacement:=fndList(strKey), _
          LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
          SearchFormat:=False, ReplaceFormat:=False
    Next strKey
    Next sht
End Sub

It works fine, but how do I make it so that it only operates on a single column in a single worksheet? (Limiting it to a selection would be okay too, I guess.)

Thanks!

Upvotes: 0

Views: 221

Answers (2)

E. Villiger
E. Villiger

Reputation: 916

Just combining the above comments into an answer (per this meta topic):

Sub FixPlatforms()
    Dim fndList As Object
    Set fndList = CreateObject("Scripting.Dictionary")
    fndList.Add "3DO Interactive Multiplayer", "3DO"
    fndList.Add "Nintendo 3DS", "3DS"
    fndList.Add "Ajax", "AJAX"
    fndList.Add "Xerox Alto", "ALTO"
    fndList.Add "Amiga CD32", "AMI32"
    fndList.Add "Amiga", "AMI"
    fndList.Add "Apple I", "APPI"
    fndList.Add "Apple IIe", "APPIIE"
    fndList.Add "Apple IIGS", "APPGS"
    fndList.Add "Apple II Plus", "APPII+"
    fndList.Add "Apple II series", "APPII"
    fndList.Add "Apple II", "APPII"

    For Each strKey In fndList.Keys()
        ActiveSheet.Columns(1).Replace What:=strKey, Replacement:=fndList(strKey), _
          LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
          SearchFormat:=False, ReplaceFormat:=False
    Next strKey
End Sub

Of course, just adjust the Columns(1) to whichever column you want the replace to work in. You can also select a column by using .EntireColumn on a Range object like:

ActiveSheet.Range("A1").EntireColumn.Replace ...

If you just want it to work by Selection, then use the Selection object (which is also an object of type Range):

Selection.Replace ...

Upvotes: 1

YowE3K
YowE3K

Reputation: 23994

how do I make it so that it only operates on a single column

If you only want to operate on a single Column in a worksheet and not on all Cells in the worksheet, use (for instance) Columns(4).Replace instead of Cells.Replace.

in a single worksheet

If you don't want to process all Worksheets in the ActiveWorkbook and, instead, only want to process (for instance) ActiveWorkbook.Worksheets("specific_worksheet") remove the loop you added to your code to process Each worksheet in ActiveWorkbook.Worksheets, and then use ActiveWorkbook.Worksheets("specific_worksheet") instead of your loop object (sht).

Limiting it to a selection would be okay too

If you only want to perform the Replace on the current Selection, use Selection.Replace

Upvotes: 1

Related Questions