vba 如果单元格是特定值，则使用 Excel 宏快速替换文本
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20227402/
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
Using Excel Macros to replace text quickly if the cell is a certain value
提问by ShadowLiberal
I have a macro that's met to find all the rows in the N column in an excel spreadsheet with a value of 'Accept', and adjust their value to 'Reject'.
我有一个宏，用于在 Excel 电子表格中查找 N 列中的所有行，其值为“接受”，并将其值调整为“拒绝”。
My macro is working, but it works VERYslow, it literally took me over 15 minutes for my macro to run through 20,000+ rows changing the cell value from Accept to Reject, which is way too long for me to expect any customer to wait (20,000 is the high end of how many rows of data I'd expect customers to have).
我的宏正在运行，但运行速度非常慢，我的宏运行 20,000 多行将单元格值从接受更改为拒绝花了我超过 15 分钟的时间，这对我来说太长了，无法期望任何客户等待（ 20,000 是我希望客户拥有的数据行数的高端）。
Below is the code in my macro, I'm wondering if anyone has any ideas how I can make it run faster.
下面是我的宏中的代码，我想知道是否有人知道如何让它运行得更快。
' Select cell N2, *first line of data*.
Range("N2").Select
' Set Do loop to stop when an empty cell is reached.
Do Until IsEmpty(ActiveCell)
If ActiveCell.Value = "Accept" Then
ActiveCell.Value = "Reject"
End If
' Step down 1 row from present location.
ActiveCell.Offset(1, 0).Select
Loop
采纳答案by ShadowLiberal
Thanks for all the help guys. I used some of the links and code you guys posted (especially the link Doug Glancy posted in a comment, wish I could pick comments as the accepted answer) to come up with some new code that works almost instantly. For anyone who's interested in how it's working, here's the new VBA code.
感谢所有帮助的家伙。我使用了你们发布的一些链接和代码（尤其是 Doug Glancy 在评论中发布的链接，希望我可以选择评论作为接受的答案）来提出一些几乎可以立即运行的新代码。对于任何对其工作方式感兴趣的人，这里是新的 VBA 代码。
Dim dat As Variant
Dim rng As Range
Dim i As Long
Set rng = Range("N2:N" & ActiveSheet.UsedRange.Rows.Count)
dat = rng ' dat is now array
For i = LBound(dat, 1) To UBound(dat, 1)
If dat(i, 1) = "Accept" Then
dat(i, 1) = "Reject"
End If
Next
rng = dat ' put new values back on sheet
回答by user7333687
Try this:
尝试这个：
Sub formatnumbers()
Do Until IsEmpty(ActiveCell)
ActiveCell.Select
ActiveCell.Replace What:=ActiveCell.Value, Replacement:=ActiveCell.Value, LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
ActiveCell.Offset(2000, 0).Select
Loop
End Sub
回答by bf2020
The following has worked very fast for me in the past:
过去，以下工作对我来说非常快：
Have macro select area/range that needs to have values replaced.
让宏选择需要替换值的区域/范围。
Selection.Replace What:="Accept",Replacement:="Reject", LookAt:=xlPart, SearchOrder:=xlByRows,MatchCase:=True,SearchFormat:=False,ReplaceFormat:=False
Selection.Replace What:="Accept",Replacement:="Reject", LookAt:=xlPart, SearchOrder:=xlByRows,MatchCase:=True,SearchFormat:=False,ReplaceFormat:=False
