Excel Vba代码检查列中的数量(Excel Vba Code to Check Quantity in a Column)

Excel Vba代码检查列中的数量(Excel Vba Code to Check Quantity in a Column)

我有一个宏来格式化电子表格。 我需要一些excel vba代码添加到开头，检查列中的Quantity是否总是'1'

代码需要检查H2中单元格H2到数据底部的列（直到找到一个空白单元格）。

如果所有值均为“1”则不执行任何操作并继续运行宏。 如果发现任何其他数字（负数或正数），则显示MsgBox "Warning: Quantities Other Than '1' Found. Fix Errors and Re-Run!" 然后当选择“确定”时退出宏。

I have a macro to format a spreadsheet. I need some excel vba code to add to the start to check that the Quantity in a column is always '1'

The code needs to check the column from cell H2 to the bottom of the data in H2 (until it finds a blank cell).

If all the values are '1' do nothing and continue running the macro. If it finds any other number (either negative or positive) display a MsgBox "Warning: Quantities Other Than '1' Found. Fix Errors and Re-Run!" then when 'OK' is selected exit the macro.

最满意答案

像这样的东西：

Sub YourExistingCode() If QuantityErrorFound Then MsgBox "Warning: Quantities Other Than '1' Found. Fix Errors and Re-Run!" Exit Sub Else '~~> Run your code End If End Sub Function QuantityErrorFound() As Boolean Dim cl As Range, result As Boolean result = False For Each cl In Range("H2:H" & Range("H2").End(xlDown).Row) If cl.Value <> 1 Then result = True End If Next cl QuantityErrorFound = result End Function 我使用了一个函数（ QuantityErrorFound ），以便更容易集成到现有代码中 在现有代码中，只需添加if语句以检查是否找到错误

Something like this:

Sub YourExistingCode() If QuantityErrorFound Then MsgBox "Warning: Quantities Other Than '1' Found. Fix Errors and Re-Run!" Exit Sub Else '~~> Run your code End If End Sub Function QuantityErrorFound() As Boolean Dim cl As Range, result As Boolean result = False For Each cl In Range("H2:H" & Range("H2").End(xlDown).Row) If cl.Value <> 1 Then result = True End If Next cl QuantityErrorFound = result End Function I've used a function (QuantityErrorFound) to make it easier to integrate into your existing code In your existing code simply add the if statement to check whether an error is found