如何用VBA数组结合循环实现多列对的条件行独立计数？

Got it, let's break down how to adjust your VBA code to handle 200 columns and track independent counts for each column pair. The core issue with your current code is that intCounter is a single 1D array that accumulates results across all columns—we need a 2D array to store separate counts for each group of columns.

Key Changes Explained: #

• 2D Array for Grouped Counts: We'll use a 2D array where each row represents a column pair group, and each column tracks one of your three conditions.
• Dynamic Group Calculation: Calculate how many column groups exist (from column 3 to 200, stepping by 3) to size the array correctly.
• Per-Group Reset: Reset counts for each group before processing its rows, so results don't bleed between groups.
• Robust Error Handling: Add a check to ensure the "is_monitoring_relevant" header exists, avoiding runtime errors.

Modified Code #

Function RabigatorCount() As Variant
    Dim zelle As Range
    Dim i As Integer, j As Integer, k As Integer
    Dim posMonitoring As Integer
    Dim numGroups As Integer
    Dim intCounter() As Integer
    Dim intLastRow As Integer ' Optional: Use dynamic row count instead of fixed 59
    
    ' Locate the "is_monitoring_relevant" column
    Set zelle = Cells.Find("is_monitoring_relevant")
    If zelle Is Nothing Then
        MsgBox "Header 'is_monitoring_relevant' not found!", vbExclamation
        RabigatorCount = CVErr(xlErrValue)
        Exit Function
    End If
    posMonitoring = zelle.Column
    
    ' Calculate number of column groups (3 to 200, step 3)
    numGroups = ((200 - 3) \ 3) + 1
    ' Resize array: rows = groups, columns = 3 conditions
    ReDim intCounter(1 To numGroups, 1 To 3)
    
    ' Optional: Get last used row in the monitoring column instead of fixed 59
    intLastRow = Cells(Rows.Count, posMonitoring).End(xlUp).Row
    
    k = 1 ' Track current group index
    For j = 3 To 200 Step 3
        ' Reset counts for the current column group
        intCounter(k, 1) = 0
        intCounter(k, 2) = 0
        intCounter(k, 3) = 0
        
        ' Loop through rows (use intLastRow instead of 59 for dynamic range)
        For i = 2 To intLastRow
            If Cells(i, posMonitoring).Value = "c" Then
                Select Case True
                    ' Condition 1: Both columns < 0
                    Case Cells(i, j + 1).Value < 0 And Cells(i, j + 2).Value < 0
                        intCounter(k, 1) = intCounter(k, 1) + 1
                    ' Condition 2: First column = 0, second < 0
                    Case Cells(i, j + 1).Value = 0 And Cells(i, j + 2).Value < 0
                        intCounter(k, 2) = intCounter(k, 2) + 1
                    ' Condition 3: Both columns > 0
                    Case Cells(i, j + 1).Value > 0 And Cells(i, j + 2).Value > 0
                        intCounter(k, 3) = intCounter(k, 3) + 1
                End Select
            End If
        Next i
        
        k = k + 1 ' Move to next column group
    Next j
    
    ' Return the 2D array of counts
    RabigatorCount = intCounter
End Function

How to Use This: #

1. Press Alt + F11 to open the VBA editor, paste this code into a module.
2. Go back to your worksheet, select a range that has numGroups rows × 3 columns (e.g., if there are 66 groups, select 66x3 cells).
3. Type =RabigatorCount() and press Ctrl + Shift + Enter (this enters it as an array formula). Each row will show the three counts for one column pair group.

Notes: #

• The intLastRow variable replaces the fixed 59 to automatically adjust to your data's actual row count—remove it and use 59 if you need the fixed range.
• Each group corresponds to columns j, j+1, j+2 where j starts at 3 and increments by 3 (3→6→9... up to 198, since 198+2=200).

内容的提问来源于stack exchange，提问作者Elena