如果单元格是特定值，则使用Excel宏来快速替换文本 [英] Using Excel Macros to replace text quickly if the cell is a certain value

问题描述

我有一个宏，以查找Excel列表中的N列中的所有行，值为接受，并将其值调整为拒绝。

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'.

我的宏正在工作，但它的工作速度非常慢[/ b]很慢，我用了超过15分钟的时间让我的宏运行了20,000+行，将单元格值从接受改为拒绝太长了，我期望任何客户等待（20,000是我期望客户有多少行数据的高端）。

My macro is working, but it works VERY slow, 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).

以下是代码在我的宏中，我想知道有没有人有任何想法，我可以如何运行更快。

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

推荐答案

感谢所有的帮助。我使用了你们发布的一些链接和代码（特别是链接Doug Glancy发表在评论中，希望我可以选择评论作为接受的答案）来提出一些几乎可以立即工作的新代码。对于任何对它有效的人来说，这里是新的VBA代码。

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.

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

这篇关于如果单元格是特定值，则使用Excel宏来快速替换文本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！