Excel VBA 使用不同工作表中的查找/替换更新单元格
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10490147/
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
Excel VBA updating cells with Find/Replace from a different sheet
提问by ajhstn
Hey i have a sheet1 containing a list of search patterns in column A, and a corresponding list of category names in column B. I have a sheet2 which has a list of various descriptions of my bank transactions.
嘿，我有一个 sheet1，其中包含 A 列中的搜索模式列表和 B 列中的相应类别名称列表。我有一个 sheet2，其中包含我的银行交易的各种描述列表。
Eg in sheet1 i have groceries, fuel, entertainment, savings, and in sheet2 i have "Shell service station, abc road", "Coles supermarket" etc..
例如，在 sheet1 中我有杂货、燃料、娱乐、储蓄，在 sheet2 中我有“壳牌服务站、abc 路”、“科尔斯超市”等。
I want to find words in the transactions columns, and then replace the found line with a category..
我想在交易列中查找单词，然后用类别替换找到的行。
Eg. If i find the word "Shell" in Sheet2 i want to replace that line with the word "Fuel"
例如。如果我在 Sheet2 中找到“Shell”这个词，我想用“Fuel”这个词替换那一行
So far i have got this working, but i dont believe that it is the most efficient or effective way of doing it. Below is my code.
到目前为止，我已经完成了这项工作，但我不相信这是最有效或最有效的方式。下面是我的代码。
Sub UpdateCats()
Dim x As Integer
Dim FindString As String
Dim ReplaceString As String
Dim NumRows As Integer
'Replace and update Categories
With Sheets("Categories")
.Activate
' Set numrows = number of rows of data.
NumRows = Range("A2", Range("A2").End(xlDown)).Rows.Count
' Select cell a1.
Range("A2").Select
' Establish "For" loop to loop "numrows" number of times.
For x = 1 To NumRows
FindString = ActiveCell.Value
ActiveCell.Offset(0, 1).Select
ReplaceString = ActiveCell.Value
ActiveCell.Offset(1, -1).Select
With Sheets("Data")
.Activate
'With Columns(2)
Cells.Replace What:=FindString, Replacement:=ReplaceString, LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False
'End With
End With
Sheets("Categories").Activate
Next
End With
End Sub
The reason i dont like my code so far is because it has to continually switching (activating) between sheets as it runs through the loop. Is there a way to do this better?
到目前为止我不喜欢我的代码的原因是因为它在循环中运行时必须在工作表之间不断切换（激活）。有没有办法做得更好？
回答by Kim Gysen
In general, I don't believe that this a very good way to go. Using "ActiveCell" and ".Activate" is a rather dangerous habit because whatever changes will screw your entire piece of code. Try using a named hierarchy (name application - name workbook - name sheet - dynamic ranges such as named ranges as much as possible). Personally I am also not too fond of the offset function, I don't know why everyone is so crazy about it, to revise this type of code is rather untransparent and you seldomly really need it. You could just load the entire thing in an array of strings and loop through it. It's brief and easy to read.
总的来说，我不认为这是一个很好的方法。使用“ActiveCell”和“.Activate”是一个相当危险的习惯，因为任何改变都会破坏你的整段代码。尝试使用命名层次结构（名称应用程序 - 名称工作簿 - 名称表 - 动态范围，例如尽可能多的命名范围）。我个人也不太喜欢offset函数，不知道为什么大家都这么着迷，修改这种代码是比较不透明的，很少真正需要的。您可以将整个内容加载到字符串数组中并循环遍历它。它简短易读。
回答by Kim Gysen
Use this piece of code:
使用这段代码：
Application.ScreenUpdating=false
回答by Kim Gysen
This should substantially improve processing time.
这应该会大大缩短处理时间。
Sub UpdateCats()
Dim v As Long, vFSRSs As Variant
On Error GoTo bm_Safe_Exit
Application.EnableEvents = False
Application.ScreenUpdating = False
'Replace and update Categories
With Sheets("Categories")
vFSRSs = .Range(.Cells(2, 1), .Cells(Rows.Count, 1).End(xlUp).Offset(0, 1)).Value2
End With
With Sheets("Data")
With .Columns(2)
For v = LBound(vFSRSs, 1) To UBound(vFSRSs, 1)
'Debug.Print vFSRSs(v, 1) & " to " & vFSRSs(v, 2)
.Replace What:=vFSRSs(v, 1), Replacement:=vFSRSs(v, 2), _
LookAt:=xlWhole, MatchCase:=False
Next v
End With
End With
bm_Safe_Exit:
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
By storing the search-for and replacement terms in a two dimensional array, the loop through the worksheet cells is discarded. Additionally, any form of Worksheet.Activate methodor range .Selecthas been avoided.
通过在二维数组中存储搜索和替换项，工作表单元格的循环被丢弃。此外，避免了任何形式的Worksheet.Activate 方法或范围.Select。
See How to avoid using Select in Excel VBA macrosfor more methods on getting away from relying on select and activate to accomplish your goals.
有关摆脱依赖选择和激活来实现目标的更多方法，请参阅如何避免在 Excel VBA 宏中使用选择。
回答by Luke Kot-Zaniewski
I would use built in app functions to your advantage. In my code I have implemented the "TypeName" and "Search" methods to essentially do the same thing as Ctrl+F & Ctrl+replace, it just became a little more readable. I think it is important to be mindful of how powerful VBA methods are and to use those first before resorting to more unorthodox methods.
我会使用内置的应用程序功能来为您服务。在我的代码中，我实现了“TypeName”和“Search”方法，基本上与 Ctrl+F 和 Ctrl+replace 做同样的事情，它只是变得更具可读性。我认为重要的是要注意 VBA 方法的强大程度，并在使用更非正统的方法之前先使用它们。
Sub Cats()
With ThisWorkbook
For i = 1 To Sheets(1).Cells(Rows.Count, 1).End(xlUp).Row
Dim lookupvalue As String
lookupvalue = Sheets(1).Cells(i, 1).Value
For n = 1 To Sheets(2).Cells(Rows.Count, 1).End(xlUp).Row
If TypeName(Application.Search(lookupvalue, Sheets(2).Cells(n, 1))) = "Double" Then
Sheets(2).Cells(n, 1).Value = Sheets(1).Cells(i, 2).Value
GoTo exitloop
End If
Next n
exitloop:
Next i
End With
End Sub
