vba 遍历单元格范围以检查它是否是数字（任何数字）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17283947/
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
Loop through range of cells to check if it is a number (any number)
提问by rwbarg15
I'm looking for a way to loop through a range of cells and check if the cell is a number or not. I'm aware that dates are actually numbers and I will be searching for number of dates. So let's say I want to check the range ("A2:A10") and if the cell is a number, add 1 to my datecount variable. The ISNUMBER() function seems to only be useful if you are looking for a specific number. But I'm looking for any number.
我正在寻找一种方法来遍历一系列单元格并检查单元格是否为数字。我知道日期实际上是数字，我将搜索日期数。因此，假设我想检查范围 ("A2:A10")，如果单元格是数字，则将 1 添加到我的 datecount 变量中。ISNUMBER() 函数似乎仅在您查找特定数字时才有用。但我正在寻找任何数字。
Sub Main()
Dim Datecount As Integer
Datecount = 0
Range("A2").Select
For Each c In Range("2:2")
If c.Value.IsNumber() Then
Datecount = Datecount + 1
Next
End Sub
The above is the most important thing I need help with. If you can help with that, great! If you can help with this next part, that would be wonderful as well. Basically, I'd like to check if the cell is a number just like above. But this time, if it isn't a number I want it to exit the loop.
以上是我需要帮助的最重要的事情。如果你能帮上忙，那就太好了！如果您能在下一部分中提供帮助，那也太棒了。基本上，我想检查单元格是否是一个数字，就像上面一样。但这一次，如果它不是数字，我希望它退出循环。
Dim Datecount As Integer
Datecount = 0
Range("A2").Select
For Each c In Range("2:2")
If c.Value.IsNumber() Then
Datecount = Datecount + 1
ElseIf
Exit For
End If
Next
End Sub
回答by sous2817
To solve your first problem, you don't need to loop at all. A simple CountIf formula will give you what you're looking for:
要解决您的第一个问题，您根本不需要循环。一个简单的 CountIf 公式将为您提供所需的内容：
Sub test()
Dim DateCount As Integer
DateCount = Application.WorksheetFunction.CountIf(Sheet1.Range("A2:A10"), ">=0")
End Sub
I thinkthis will do what you're after for your second question:
我认为这将满足您对第二个问题的要求：
Sub test2()
Dim LastNumberRow As Integer
If Application.Evaluate("=IFERROR(MATCH(TRUE,INDEX(ISTEXT($A2:$A10),0),0),0)") = 1 Then
LastNumberRow = 0
Else
LastNumberRow = Application.Evaluate("=IFERROR(MATCH(TRUE,INDEX(ISTEXT($A2:$A10),0),0),0)")
End If
End Sub
test2() will return 0 if there isn't any TEXT in the range you're checking (meaning they're all numeric cells). Let me know if that's a problem and I'll hack my way around it...
如果您检查的范围内没有任何 TEXT（意味着它们都是数字单元格），则 test2() 将返回 0。让我知道这是否有问题，我会解决它...
回答by David Zemens
Try:
尝试：
Sub Main()
Dim c as Range
Dim Datecount As Integer
Datecount = 0
For Each c In Range("2:2").Cells
If IsNumeric(c.Value) Then
Datecount = Datecount + 1
End If
Next
End Sub
Now, I am not sure what you mean with the IsNumber()function. I've never heard of that. And in any case, you don't apply a function to the end of an object:
现在，我不确定你对这个IsNumber()函数的意思。我从来没有听说过。在任何情况下，您都不会将函数应用于对象的末尾：
c.Value.IsNumeric()wil give you an error (object does not support this property or method, probably, I have not tested it)
c.Value.IsNumeric()会给你一个错误（对象不支持这个属性或方法，可能，我没有测试过）
Instead:
反而：
IsNumeric(c.Value), because c.Valueis the argument you're passing to the IsNumericfunction.
IsNumeric(c.Value)，因为c.Value是您传递给IsNumeric函数的参数。
回答by Nunzio Puntillo
try this:
尝试这个：
Sub flexRange()
'range can be estended Range("b2").Select i = 0
'range 可以扩展 Range("b2").Select i = 0
Do Until ActiveCell = ""
If IsNumeric(ActiveCell.Value) Then i = i + 1
ActiveCell.Offset(1, 0).Select
Loop
MsgBox "I found_" & i & "_Numeric element"
End Sub
