vba Excel 2010 宏：替换包含数字大于和小于的所有单元格
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16124271/
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 2010 Macro: Replace all cells containing number greater than AND smaller than
提问by rf2012
I have a large excel sheet in which I want to replace several number ranges (all within the same range of cells). For example, I want to replace all numbers greater than -5 and smaller than 5 with the number 0. All numbers greater than 5 and smaller than 10 should be replaced with 1, all numbers greater than -10 and smaller than -5 should be replaced with -1, and so forth.
我有一个很大的 excel 工作表，我想在其中替换几个数字范围（都在同一单元格范围内）。例如，我想将所有大于-5且小于5的数字替换为数字0。所有大于5且小于10的数字都应替换为1，所有大于-10且小于-5的数字都应替换为替换为 -1，依此类推。
I tried using this formula
我尝试使用这个公式
Sub replace()
Dim rng As Range
For Each rng In Range("B2:AI40")
If IsNumeric(rng) < 5 And IsNumeric(rng) > -5 Then
rng = 0
End If
Next rng
End Sub
which doesn't quite do the trick as it replace everything with 0. Similarly, thissolution doesn't do the trick either. How can I modify either formula to suit my needs, or is such a replacement not possible?
这并不能完全解决问题，因为它用 0 替换了所有内容。同样，这个解决方案也不能解决问题。我如何修改任一公式以满足我的需要，或者这样的替换是不可能的？
Thank you!
谢谢！
回答by Doc Brown
Try
尝试
Sub replace()
Dim rng As Range
For Each rng In Range("B2:AI40")
If rng.Value < 5 And rng.Value > -5 Then
rng = 0
End If
Next rng
End Sub
IsNumericis a boolean function which either returns 0 or 1, that's why every cell was set to 0 before. If you expect non-numeric values in your cells, you might have to use this code:
IsNumeric是一个返回 0 或 1 的布尔函数，这就是之前每个单元格都设置为 0 的原因。如果您希望单元格中出现非数字值，则可能必须使用以下代码：
Sub replace()
Dim rng As Range
For Each rng In Range("B2:AI40")
If IsNumeric(rng) then
If rng.Value < 5 And rng.Value > -5 Then
rng = 0
End If
End if
Next rng
End Sub
The whole task gets simpler (and deals also with your the other cases you mentioned) when you use integer division (\) by 5:
当您使用整数除法 ( \) 除以 5时，整个任务变得更简单（并且还处理您提到的其他情况）：
Sub replace()
Dim rng As Range
For Each rng In Range("B2:AI40")
If IsNumeric(rng) then
rng = rng.Value \ 5
End if
Next rng
End Sub
回答by Siddharth Rout
Please do not select this as an answer.
请不要选择此作为答案。
This post is only to show you a much much faster method than what you are actually using. Technically you are looping through every cell in B2:AI40. i.e your code is looping 1326 times (=34 Cols * 39 Rows)
这篇文章只是向您展示一种比您实际使用的方法快得多的方法。从技术上讲，您正在遍历B2:AI40. 即您的代码循环了 1326 次 ( =34 Cols * 39 Rows)
Here is a much much faster method using only 17 loops
这是一种仅使用 17 个循环的更快的方法
Logic:
逻辑：
- < 10 And > 5 (In other words 6,7,8,9 to be replaced by 1)
- < 5 And > -5 (In other words -4,-3,-2,-1,0,1,2,3,4 to be replaced by 0)
- < -5 And > -10 (In other words -6,-7,-8,-9 to be replaced by -1)
- < 10 和 > 5（也就是说 6、7、8、9 被 1 代替）
- < 5 和 > -5 （也就是说 -4,-3,-2,-1,0,1,2,3,4 被 0 代替）
- < -5 和 > -10 （也就是说 -6,-7,-8,-9 被 -1 替换）
Now instead of looping ever cell, we will be using .Replaceto search the above numbers so technically we will only be looping 17 times as compared to 1326!
现在，我们将使用.Replace搜索上述数字而不是循环任何单元格，因此从技术上讲，与 1326 相比，我们只会循环 17 次！
See this sample code
请参阅此示例代码
Option Explicit
Sub replace()
Dim ws As Worksheet
Dim rng As Range
Dim i As Long
Set ws = ThisWorkbook.Sheets("Sheet1")
Set rng = ws.Range("B2:AI40")
With rng
For i = 6 To 9 '<~~ 4 loops
.replace What:=i, Replacement:="1", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Next i
For i = -4 To 4 '<~~ 9 loops ; 7 if when i = 1 or 0 it doesn't do anyting
If i <> 1 And i <> 0 Then
.replace What:=i, Replacement:="0", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End If
Next i
For i = -9 To -6 '<~~ 4 loops
.replace What:=i, Replacement:="-1", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Next i
End With
End Sub
