vba Excel：查找和替换宏 - 仅一列

Excel: Find and Replace Macro - One Column Only

提问by Mathew Mitchell

I've written some macros to format a load of data into the same accepted format, the program we pull from refuses to pull the data how we want it but in theory it wouldn't be hard to change in Excel.

我已经编写了一些宏来将加载的数据格式化为相同的可接受格式，我们从中提取的程序拒绝以我们想要的方式提取数据，但理论上在 Excel 中更改并不难。

The way it is set to run is to have separate macros for the modifiers and then a 'Run All' macro that just does a Call to them all.

它设置为运行的方式是为修饰符设置单独的宏，然后使用一个“全部运行”宏来调用它们。

Currently I have:

目前我有：

Sub ReplaceTitleMs()
'
' Strips Mrs from Headteacher Name
'
'
'
 Columns("V").Select
 Cells.Replace What:="Ms ", Replacement:="", LookAt:=xlPart, SearchOrder _
    :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
End Sub

But when I run this, it strips Ms from the whole sheet and one column requires Ms to still be in the Cells (this is column W)

但是当我运行它时，它会从整个工作表中删除 Ms 并且一列要求 Ms 仍然在单元格中（这是 W 列）

An example of the data is effectively:

数据的一个例子是有效的：

Ms Helen Smith
Ms Brenda Roberts
Ms Kirsty Jones

But there are many other titles being used so I would like to just run a Find and Replace on the column that has to be selected by the macro.

但是还有许多其他标题正在使用，所以我只想在必须由宏选择的列上运行查找和替换。

The macro works find on the column I want it to...I just need to restrict it to that column!

宏在我想要的列上找到...我只需要将它限制在该列上！

回答by

You need to properly qualify the range for the Replace()method

您需要正确限定该Replace()方法的范围

Sub ReplaceTitleMs()
    Columns("V").Replace    What:="Ms ", _
                            Replacement:="", _
                            LookAt:=xlPart, _
                            SearchOrder:=xlByRows, _
                            MatchCase:=False, _
                            SearchFormat:=False, _
                            ReplaceFormat:=False
End Sub

回答by brettdj

Based on your data (which is properly capitilised), to avoid false matches you should set MatchCaseto True to avoid culling the likes of Mrs Nancy Adamsto Mrs Nancy Ada

根据您的数据（正确地大写），为避免错误匹配，您应该将其设置MatchCase为 True 以避免剔除诸如 Mrs Nancy AdamstoMrs Nancy Ada

Columns("V").Replace "Ms ", vbNullString, xlPart, xlByRows, True

To be completely bullet-proof, you would either

为了完全防弹，你要么

1. Use AutoFilterfor the detection and removal
2. Build a range of matches with Findand FindNext, then remove only the first three characters where the match is correct

1. 使用AutoFilter的检测和清除
2. 使用Findand构建一系列匹配FindNext，然后只删除匹配正确的前三个字符

回答by Top-Master

Search and Replace in selected-cells

在选定单元格中搜索和替换

1. press the "ALT + F11" keys shortcut (i.e. hold ALTkey down and press F11key at last release both) to open the Microsoft Visual Basic for Applicationswindow.

2. Click "Insert > Module" (i.e. from "Insert" menu click "Module"), and paste the following code in the Module Window.

1. 按“ ALT + F11”键快捷方式（即按住ALT键并F11在最后释放时按下键）打开Microsoft Visual Basic for Applications窗口。

2. 单击“ Insert > Module”（即从“ Insert”菜单中单击“ Module”），然后将以下代码粘贴到模块窗口中。

Sub ReplaceInSelection()
    Dim rng As Range
    Dim workRng As Range
    Dim find$
    Dim replace$
On Error Resume Next
    xTitleId = "KutoolsforExcel"
    Set workRng = Application.Selection
    Set workRng = Application.InputBox("Range", "Select Range", workRng.Address, Type:=8)
    find = Application.InputBox("Find:", "Select what to find")
    replace = Application.InputBox("Replace with:", "Select replacement")
    For Each rng In workRng
        If rng.Value = find Then
            rng.Value = replace
        End If
    Next
End Sub

1. for now close Microsoft Visual Basic for Applicationswindow
2. and Select the range of url text that you want to convert to clickable hyperlinks.
3. Then press "ALT + F8" key (or just F5in older excel), to run the code click run while "ReplaceInSelection" is selected
4. then a pop-up dialog will open for you to change previously selected range, then click OK
5. then another pop-up dialog will open for to specify what you want to find
6. then, at last, a pop-up dialog will open for you to specify with what you want to replaceany occurrence of what was found

3. 现在关闭Microsoft Visual Basic for Applications窗口
4. 并选择要转换为可点击超链接的 url 文本范围。
5. 然后按“ ALT + F8”键（或只是F5在旧版excel中），要运行代码，请在ReplaceInSelection选择“ ”时单击运行
6. 然后将打开一个弹出对话框让您更改先前选择的范围，然后单击 OK
7. 然后将打开另一个弹出对话框以指定您想要的内容 find
8. 然后，最后，将打开一个弹出对话框供您指定您想要的replace任何发现的内容

all done! the selected cells have been searched for what you specified to findand was changed with again what you specified to replace with.

全部完成！所选单元格已搜索您指定的内容，find并再次更改为您指定的内容replace with。