Worksheet计算多个范围

问题描述

我有当前的代码计算单个单元格的值，然后调用一个模块，如果它的值超过另一个值。

如何使它检查多个单元格范围B5：E5，B8：M8，如果范围中的任何单元格超过该值，则调用该模块。

  Private Sub Worksheet_Calculate （）
如果Range（B5）> 4然后
 Application.EnableEvents = False 
 Application.RunMail_small_Text_Outlook
 Application.EnableEvents = True 
 End If 
 End Sub 
  

解决方案

这是你想要的吗？

范围
Dim aCell As Range

设置aRng =范围（$）

$ b设置aRng = B5：E5）
设置bRng =范围（B8：M8）

每个aCell在aRng
如果aCell.Value> 4然后
'
Application.RunMail_small_Text_Outlook
'
退出子
结束如果
下一个

对于每个aCell在bRng
如果aCell.Value> 4然后
'
Application.RunMail_small_Text_Outlook
'
退出子
结束如果
下一个
End Sub

或这样的东西

  Private Sub Worksheet_Calculate（）
 Dim aRng As Range，bRng As Range 
 Dim aCell As Range，uRng As Range 
 
设置aRng =范围（B5：E5 ）
设置bRng =范围（B8：M8）
设置uRng =联合（aRng，bRng）
 
每个aCell在uRng 
如果aCell.Value > 4然后
'
 Application.RunMail_small_Text_Outlook
'
退出子
结束如果
下一个
 End Sub 
  

I have the current code calculating a single cell's value then calling a module if it's value exceeds another value.

How can I make it check multiple ranges of cells eg B5:E5, B8:M8 and call the module if any of the cells in the range exceed the value.

Private Sub Worksheet_Calculate()
If Range("B5") > 4 Then
Application.EnableEvents = False
Application.Run "Mail_small_Text_Outlook"
Application.EnableEvents = True
End If
End Sub

解决方案

Is this what you are trying?

Private Sub Worksheet_Calculate()
    Dim aRng As Range, bRng As Range
    Dim aCell As Range

    Set aRng = Range("B5:E5")
    Set bRng = Range("B8:M8")

    For Each aCell In aRng
        If aCell.Value > 4 Then
            '
            Application.Run "Mail_small_Text_Outlook"
            '
            Exit Sub
        End If
    Next

    For Each aCell In bRng
        If aCell.Value > 4 Then
            '
            Application.Run "Mail_small_Text_Outlook"
            '
            Exit Sub
        End If
    Next
End Sub

or something like this?

Private Sub Worksheet_Calculate()
    Dim aRng As Range, bRng As Range
    Dim aCell As Range, uRng As Range

    Set aRng = Range("B5:E5")
    Set bRng = Range("B8:M8")
    Set uRng = Union(aRng, bRng)

    For Each aCell In uRng
        If aCell.Value > 4 Then
            '
            Application.Run "Mail_small_Text_Outlook"
            '
            Exit Sub
        End If
    Next
End Sub

这篇关于Worksheet计算多个范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！