Excel:查找和替换宏-仅一列 [英] Excel: Find and Replace Macro - One Column Only

问题描述

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

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.

设置为运行的方式是为修饰符使用单独的宏，然后是只对所有修饰符进行调用的'Run All'宏.

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.

当前，我有:

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

但是当我运行它时，它会从整张纸上剥离Ms，并且一列要求Ms仍在单元格中(这是W列)

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 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!

推荐答案

您需要适当限定Replace()方法的范围

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

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

这篇关于Excel:查找和替换宏-仅一列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！