查找两个下拉菜单的所有组合的最大值

• 如果你确信至少有一个数值，就不需要最后的if语句;如果你确信至少有一个正值，就不需要IsFirstFound或IsMax，但为什么要冒这个险呢？

Option Explicit

Sub MaxAcrossRegions()
    
    Dim Regions(): Regions = Array("Region 1", "Region 2", "Region 3")
    Dim ProductGroups(): ProductGroups = Array(1, 2, 3, 4, 5)
    
    Dim ws As Worksheet: Set ws = ActiveSheet ' improve!
    Dim RegionCell As Range: Set RegionCell = ws.Range("A1")
    Dim ProductGroupCell As Range: Set ProductGroupCell = ws.Range("A2")
    Dim ValueCell As Range: Set ValueCell = ws.Range("C3")
    
    Dim CurrValue As Variant, MaxValue As Double, r As Long, p As Long
    Dim MaxProductGroup As String, MaxRegion As String
    Dim IsFirstFound As Boolean, IsMax As Boolean
    
    For r = LBound(Regions) To UBound(Regions)
        RegionCell.Value = Regions(r)
        For p = LBound(ProductGroups) To UBound(ProductGroups)
            ProductGroupCell.Value = ProductGroups(p)
            CurrValue = ValueCell.Value
            If IsNumeric(CurrValue) Then
                If IsFirstFound Then
                    If CurrValue > MaxValue Then IsMax = True
                Else
                    IsFirstFound = True ' set once, on the first numeric value
                    IsMax = True
                End If
                If IsMax Then
                    MaxValue = CurrValue
                    MaxProductGroup = ProductGroups(p)
                    MaxRegion = Regions(r)
                    IsMax = False ' reset for next iteration
                End If
            End If
        Next p
    Next r
    
    If IsFirstFound Then
        MsgBox "The highest value for a product group across all regions is " _
            & MaxValue & ", found in product group " & MaxProductGroup _
            & " of region " & MaxRegion & ".", vbInformation
    Else
        MsgBox "No numeric values found.", vbCritical
    End If
    
End Sub

每个产品组

• 外循环遍历产品组，内循环遍历区域。每个内循环完成后，显示当前产品组的结果。

Sub MaxPerProductGroup()
    
    Dim Regions(): Regions = Array("Region 1", "Region 2", "Region 3")
    Dim ProductGroups(): ProductGroups = Array(1, 2, 3, 4, 5)
    
    Dim ws As Worksheet: Set ws = ActiveSheet ' improve!
    Dim RegionCell As Range: Set RegionCell = ws.Range("A1")
    Dim ProductGroupCell As Range: Set ProductGroupCell = ws.Range("A2")
    Dim ValueCell As Range: Set ValueCell = ws.Range("C3")
    
    Dim CurrValue As Variant, MaxValue As Double, r As Long, p As Long
    Dim MaxRegion As String, IsFirstFound As Boolean, IsMax As Boolean
    
    For p = LBound(ProductGroups) To UBound(ProductGroups)
        ProductGroupCell.Value = ProductGroups(p)
        For r = LBound(Regions) To UBound(Regions)
            RegionCell.Value = Regions(r)
            CurrValue = ValueCell.Value
            If IsNumeric(CurrValue) Then
                If IsFirstFound Then
                    If CurrValue > MaxValue Then IsMax = True
                Else
                    IsFirstFound = True
                    IsMax = True
                End If
                If IsMax Then
                    MaxValue = CurrValue
                    MaxRegion = Regions(r)
                    IsMax = False ' reset for next iteration
                End If
            End If
        Next r
        If IsFirstFound Then
            MsgBox "The highest value for product group " & ProductGroups(p) _
                & " across all regions is " & MaxValue _
                & ", found in region " & MaxRegion & ".", _
                vbInformation
            IsFirstFound = False ' reset for next iteration
        Else
            MsgBox "No numeric values found in product group " _
                & ProductGroups(p) & ".", vbCritical
        End If
    Next p
    
End Sub

每个区域

• 外部循环循环通过区域，内部循环通过产品组。每个内部循环完成后，将显示当前区域的结果。

Sub MaxPerRegion()
    
    Dim Regions(): Regions = Array("Region 1", "Region 2", "Region 3")
    Dim ProductGroups(): ProductGroups = Array(1, 2, 3, 4, 5)
    
    Dim ws As Worksheet: Set ws = ActiveSheet ' improve!
    Dim RegionCell As Range: Set RegionCell = ws.Range("A1")
    Dim ProductGroupCell As Range: Set ProductGroupCell = ws.Range("A2")
    Dim ValueCell As Range: Set ValueCell = ws.Range("C3")
    
    Dim CurrValue As Variant, MaxValue As Double, r As Long, p As Long
    Dim MaxProductGroup As String, IsFirstFound As Boolean, IsMax As Boolean
    
    For r = LBound(Regions) To UBound(Regions)
        RegionCell.Value = Regions(r)
        For p = LBound(ProductGroups) To UBound(ProductGroups)
            ProductGroupCell.Value = ProductGroups(p)
            CurrValue = ValueCell.Value
            If IsNumeric(CurrValue) Then
                If IsFirstFound Then
                    If CurrValue > MaxValue Then IsMax = True
                Else
                    IsFirstFound = True
                    IsMax = True
                End If
                If IsMax Then
                    MaxValue = CurrValue
                    MaxProductGroup = ProductGroups(p)
                    IsMax = False ' reset for next iteration
                End If
            End If
        Next p
        If IsFirstFound Then
            MsgBox "The highest value for region " & Regions(r) _
                & " across all product groups is " & MaxValue _
                & ", found in product group " & MaxProductGroup & ".", _
                vbInformation
            IsFirstFound = False ' reset for the next iteration
        Else
            MsgBox "No numeric values found in region " _
                & Regions(r) & ".", vbCritical
        End If
    Next r
    
End Sub