Excel如果范围内的值大于单元格中的值，则清除区域中单元格的值

Question

我想在 B3 中键入一个数字。如果 B8 到 B200 范围内的数字大于 B3 中的值，则值大于 B3 值的单元格将清除其内容。(我尝试使用附加的代码执行此操作(

(或： 如果在 B3 中输入值，则会生成小于或等于 B3 中值的所有值的下拉列表(这样就无法超过 B3 中的值(。

Sub ProcessLineNumberValidation()
Dim QTY As Integer
Dim ProcessNum As Integer
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Ozone Generator")
QTY = ws.Sheets("Ozone Generator").Cells(3, 2).Value
For i = 8 To 200
ProcessNum = ws.Sheets("Ozone Generator").Cells(i, 2).Value
If ProcessNum > QTY Then
ws.Sheets("Ozone Generator").Cells(i, 2).ClearContents
End If
Next i
End Sub

Answer 1

首先，您使用Set ws = Thisworkbook.Sheets("Ozone Generator")然后
，在多行上使用ws.Sheets("Ozone Generator")，这可能是问题的根源。如果您将ws替换回上面的代码行，您将获得：

Thisworkbook.Sheets("Ozone Generator").Sheets("Ozone Generator")

这不是有效的单元格引用。只需使用ws.Cells(....，这将产生以下代码(针对问题进行了更正，并对代码应用了更多标准的间距，排序和缩进方法(

Option Explicit
Sub ProcessLineNumberValidation()
Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("Ozone Generator")
Dim QTY As Integer, ProcessNum as Integer, i
QTY = ws.Cells(3, 2).Value
For i = 8 To 200
ProcessNum = ws.Cells(i, 2).Value
If ProcessNum > QTY Then
ws.Cells(i, 2).ClearContents
End If
Next i
End Sub

---

您可以考虑这种具有相同输出但速度更快的替代方案。 在循环这样的范围时，For Each循环比For i =循环更快。此外，从用户的角度来看，关闭ScreenUpdating将使它看起来更干净。

Sub ProcessLineNumberValidation()
Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("Ozone Generator")
Dim MyRange As Range: Set MyRange = ws.Range("B8:B200")
Dim MyCell As Range
Application.ScreenUpdating = False
For Each MyCell In MyRange
If MyCell > ws.Cells(3, 2) Then MyCell.ClearContents
Next MyCell
Application.ScreenUpdating = True
End Sub

Answer 2

这可能是Autofilter()的工作：

Sub ProcessLineNumberValidation()
With ThisWorkbook.Sheets("Ozone Generator").Range("B7:B200") 'reference your sheet range B7:B200 (B7 is the header, values are from B8 downwards)
.AutoFilter field:=1, Criteria1:=">" & .Parent.Range("B3").Value2 ' filter referenced range with values greatre than referenced range sheet cell B3
If Application.WorksheetFunction.Subtotal(103, .Resize(, 1)) > 1 Then .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).ClearContents ' clear any filtered cell other than header
.Parent.AutoFilterMode = False ' remove autofilter
End With
End Sub

如果您希望在每个"臭氧发生器"表 B3 单元格更改上调用ProcessLineNumberValidation()请将此代码放在该工作表代码窗格中：

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$3" Then 'if cell B3 has changed
Application.EnableEvents = False ' disable events to prevent this event handler being called in a possibly infinite loop
ProcessLineNumberValidation ' process your range
Application.EnableEvents = True ' enable events back on
End If
End Sub