在 VBA 中查找并替换特定范围
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15057861/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
find and replace for specific range in VBA
提问by user1155299
I am using the following code for finding the curr_symbol and replacing it with new_symbol. Even though I have specified in the code that it should do so within the specific row numbers, it applies the find and replace to all the rows in the selected sheet, which is not what I want.
我正在使用以下代码查找 curr_symbol 并将其替换为 new_symbol。即使我在代码中指定它应该在特定行号内执行此操作，它也会将查找和替换应用于所选工作表中的所有行，这不是我想要的。
Sub find_replace()
Dim curr_symbol, new_symbol As String
Dim row_num, last_row As Integer
row_num = ActiveSheet.Cells(1, "A").Value 'row_num=9
last_row = ActiveSheet.Cells(2, "A").Value 'last_row=11
Do While row_num < last_row
curr_symbol = ".ABC130301"
new_symbol = ActiveSheet.Cells(row_num, "E").Value 'new_symbol=".BAC130306"
With ActiveSheet.UsedRange
.Replace curr_symbol, new_symbol, xlPart
End With
row_num = row_num + 1
Loop
End Sub
I even tried replacing the With ActiveSheet.UsedRange....End Withwith the following statement, but that also replaces the curr_symbol in all the rows.
我什至尝试用With ActiveSheet.UsedRange....End With以下语句替换 ，但这也替换了所有行中的 curr_symbol 。
ActiveSheet.Cells.Replace What:=curr_symbol, Replacement:=new_symbol, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False
How can I fix this so it only replaces the string in the specified rows.
我该如何解决这个问题，让它只替换指定行中的字符串。
回答by mkingston
I'm assuming the range you're interested in replacing is column A, from row_numto last_row. Try replacing this:
我假设您有兴趣替换的范围是 A 列，从row_num到last_row。尝试替换这个：
With ActiveSheet.UsedRange
.Replace curr_symbol, new_symbol, xlPart
End With
With this:
有了这个：
Cells(row_num, 1).Replace curr_symbol, new_symbol, xlPart
No matter where you place ActiveSheet.UsedRangeit always refers to the entire used area of the active sheet. It is a collection containing all used cells and other cells in the same rows and columns as the used cells. It's not contextual in the way you've used it (i.e. it doesn't refer to the range relevant to your loop). The Replacemethod will be applied to every individual cell in the UsedRange.
无论你把ActiveSheet.UsedRange它放在哪里，它总是指活动表的整个使用区域。它是一个包含所有使用过的单元格以及与使用过的单元格在同一行和列中的其他单元格的集合。它与您使用它的方式无关（即它不指与您的循环相关的范围）。该Replace方法将应用于UsedRange.
Similarly with ActiveSheet.Cells; this always refers to the collection of everycell in the active sheet. My suggestion is the same as ActiveSheet.Cells(row_num, 1), specifying an individual cell to apply the Replacemethod to.
与ActiveSheet.Cells;类似 这始终是指活动工作表中每个单元格的集合。我的建议与 相同ActiveSheet.Cells(row_num, 1)，指定要应用该Replace方法的单个单元格。
No longer answering your question:
不再回答你的问题：
As an aside, the following line:
顺便说一句，以下行：
Dim curr_symbol, new_symbol As String
declares curr_symbol as a Variant, and new_symbol as a String. The same applies for your next line of declarations (the Integers). No, I don't like this declaration behaviour either. You have to specify a type for every variable. Here's how I tend to declare:
将 curr_symbol 声明为 Variant，将 new_symbol 声明为 String。这同样适用于您的下一行声明（整数）。不，我也不喜欢这种声明行为。您必须为每个变量指定一个类型。这是我倾向于声明的方式：
Dim curr_symbol As String, _
new_symbol As String
Additionally, Excel internally stores all Integer types as Long types internally. Declaring as an Integer only restricts their values, not the amount of memory they consume. Unless you specifically want to limit the value of your integers, I recommend changing all Integer declarations to Long:
此外，Excel 在内部将所有 Integer 类型存储为 Long 类型。声明为整数只会限制它们的值，而不是它们消耗的内存量。除非您特别想限制整数的值，否则我建议将所有 Integer 声明更改为 Long：
Dim last_row As Integer
Is just as memory consuming as:
与内存消耗一样：
Dim row_num as Long
But probably a little slower, if there's any speed difference at all.
但可能会慢一点，如果有任何速度差异的话。
回答by Craig Neil Brown
The "UsedRange" range that you are using is the entire spreadsheet that you have used. Your replace is being applied to your entire spreadsheet.
您使用的“UsedRange”范围是您使用的整个电子表格。您的替换正在应用于您的整个电子表格。
I would create a range that you want to do the replace in like:
我会创建一个您想要替换的范围，例如：
Dim MyRange as Range
Set MyRange = Range("B7:C9")
Then I would do the replace on this range.
然后我会在这个范围内进行替换。
