如果列没有任何大于 1 的值，则退出-解网

问：

我正在尝试创建一个执行以下操作的 IF 语句：

突出显示（红色）值大于 1 且小于 26 的任何内容，然后继续执行宏的其余部分并执行其他操作（我已经成功完成了）。
如果值超过 25，则用红色突出显示，生成一个 messageBox，然后退出子（我已经成功完成了）。
如果所有行都是 = 1，则什么都不做并退出 sub（我正在努力）。

For Each C In Range("B2:B25000").Cells
  If C.Value > 1 And C.Value < 26 Then
    firstValue = C.Value
    firstAddress = C.Address
    Exit For
    
    If Not (C.Value > 1 And C.Value < 26) Then Exit Sub 'No
  
    ElseIf C.Value > 25 Then
    C.Interior.Color = VBA.ColorConstants.vbRed
    MsgBox "Too big!"
    Exit Sub
  End If
Next

C.Interior.Color = VBA.ColorConstants.vbRed 'if greater than 1 & less than 26 then Color = red

'remaining of the macro goes here

End Sub

Excel VBA

Option Explicit

Sub test()

    Dim ws As Worksheet, c As Range, lastrow As Long
    Dim bAllOnes As Boolean, bTooBig As Boolean
    
    Set ws = Sheet1
    bAllOnes = True
    bTooBig = False
    
    lastrow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row
    For Each c In ws.Range("B2:B" & lastrow).Cells
        If Val(c.Value) > 1 Then
            bAllOnes = False
            c.Interior.Color = VBA.ColorConstants.vbRed
            If c.Value > 25 Then
                 bTooBig = True
            End If
        ElseIf Val(c.Value) < 1 Then
            bAllOnes = False
        End If
    Next
    
    If bTooBig Then
        MsgBox "Too big!", vbCritical
        Exit Sub
    ElseIf bAllOnes Then
        MsgBox "All 1's!", vbCritical
        Exit Sub
    Else
        MsgBox "Continueing"
    End If
    
End Sub

0赞 M Muaz 11/9/2021 #2

我替换了这个If Not (C.Value > 1 And C.Value < 26) Then Exit Sub

有了这个ElseIf Application.WorksheetFunction.max(Range("b:b")) = 1 Then Exit Sub

而且效果很好

如果列没有任何大于 1 的值，则退出

If column doesn't have any value greater than 1 then exit

评论

评论