英文:
If "anything other than a positive number" in a specific range of cells, stop macro
问题
我是VBA的新手，我想看看是否有人可以帮助我解决这个问题。
我想要这个宏的功能是检查我的工作表中A1到J12之间的任何单元格是否包含负数或字母。如果是的话，我希望它弹出一个消息框："只允许正数，请修复"，然后停止宏的运行。
我能够实现这个目标的唯一方法是使用以下代码：
If Application.WorksheetFunction.IsText(Range("A1")) Or sht2.Range("A1") < 0 Then MsgBox "只允许正数，请修复"
Exit Sub
然而，如果我使用这种方法，我需要为每个要应用此规则的单元格添加一行代码。我已经尝试将"A1"替换为"A1:J12"，但出现了VBA不接受的问题，显示"运行时错误'13'，类型不匹配"。
所以基本上，我的问题是，是否有一种更有效的方法来实现这个，而不需要为每个单元格都添加一行代码？
提前感谢您。
英文:
I am a newbie in VBA and I would like to see if anyone can help me out with this one.
What I want this macro to do is to see if any cells between A1 and J12 in my worksheet has a negative number or a letter. If it does, I want it to prompt a MsgBox "Only positive numbers allowed, please fix" and then stop the macro.
The only way I was able to reach this objective was by using the following:
If Application.WorksheetFunction.IsText(Range("A1")) Or sht2.Range("A1") < 0 Then MsgBox ("Only positive numbers allowed, please fix")
Exit Sub
However, if I do it this method, I need to basically add one line of code for every cell I want this rule to apply. I have tried replacing "A1" to "A1:J12" but for some reason VBA won't accept this and gives me "Run-Time Error '13', Type Mismatch."
So basically, my question is, is there a more efficient way to achieve this which does not involve a line of code for every single cell?
Thank you in advance.
答案1
得分: 3
使用WorksheetFunction.CountIfs：
Dim rng As Range
Set rng = sh2.Range("A1:J12")
If WorksheetFunction.CountIfs(rng, ">=0") <> rng.Count Then
MsgBox "Uh-oh, something other than a nonnegative number"
Exit Sub
End If
这是它的工作原理：
CountIfs返回在rng中大于等于0的值的计数。.Count返回rng中的单元格数。
如果这两者不相等，那么就有问题（可能是空白、文本、负数等）。
注意事项：
-
如果
rng有多个区域，例如rng对应于sh2.Range("A1:A3,C4:C5")，则无法正常工作。您将会收到一个
RTE 1004: 无法获取WorksheetFunction类的CountIfs属性错误。这将需要使用循环解决。
英文:
Using WorksheetFunction.CountIfs:
Dim rng As Range
Set rng = sh2.Range("A1:J12")
If WorksheetFunction.CountIfs(rng, ">=0") <> rng.Count Then
MsgBox "Uh-oh, something other than a nonnegative number"
Exit Sub
End If
Here's how this works:
CountIfsreturns the count of values >= 0 inrng..Countreturns the number of cells inrng.
If the two aren't equal, then something's off (blanks, text, negative values, etc.)
Caveat(s):
-
This won't work if
rnghas multiple areas, for example ifrngcorresponds tosh2.Range("A1:A3,C4:C5"). You'll get a
>RTE 1004: Unable to get the CountIfs property of the WorksheetFunction class.That will require a loop.
答案2
得分: 1
我的建议：循环遍历范围的单元格。
Range对象的Cells属性是该范围内所有单元格的集合。您可以轻松地循环遍历项目的集合，并针对每个项目执行某些操作/语句。
例如：
Dim cell As Range
For Each cell In ThisWorkbook.Worksheets("Sheet1").Range("A1:J12").Cells
'在此处的代码将对集合中的每个单元格执行
Debug.Print IsNumeric(cell.Value)
Next cell
您可以在此处阅读有关循环遍历范围的更多信息：https://learn.microsoft.com/en-us/office/vba/excel/concepts/cells-and-ranges/looping-through-a-range-of-cells
英文:
My suggestion: loop over the Cells of a Range.
The Cells property of a Range object is a collection of all the Cells in said Range. You can easily loop over a collection of items and perform certain actions/statements for each item.
For example:
Dim cell As Range
For Each cell In ThisWorkbook.Worksheets("Sheet1").Range("A1:J12").Cells
'code here will be executed for every cell in the collection
Debug.Print IsNumeric(cell.value)
Next cell
You can read more on looping over ranges here: https://learn.microsoft.com/en-us/office/vba/excel/concepts/cells-and-ranges/looping-through-a-range-of-cells
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论