VBA宏中动态数据范围的月度数据验证公式失效问题求助

Hey Katie, I get how frustrating it is when a formula works fine manually but breaks in VBA because of fixed row ranges. Let's break down two solid solutions to fix this—one tweaking your formula to be dynamic, and another using VBA directly for more reliability.

Option 1: Update the Formula to Use Dynamic Ranges #

Your original formula uses fixed rows ($A$5:$A$132), which fails when data length changes. Instead, we can use INDEX + COUNTA to automatically find the last row of data in the previous month's sheet.

Here's the revised formula:

=IF(ISNUMBER(MATCH(A5&D5,'PREVIOUS MONTH'!$A$5:INDEX('PREVIOUS MONTH'!$A:$A,COUNTA('PREVIOUS MONTH'!$A:$A))&'PREVIOUS MONTH'!$D$5:INDEX('PREVIOUS MONTH'!$D:$D,COUNTA('PREVIOUS MONTH'!$D:$D)),0)),"Previously Verified", "")

Key Details: #

• COUNTA('PREVIOUS MONTH'!$A:$A) counts all non-empty cells in column A of the previous month's sheet, giving us the last row number.
• INDEX('PREVIOUS MONTH'!$A:$A, [last row]) dynamically sets the end of the range, so it adapts as your data grows/shrinks.
• For Excel 365/2021, you can enter this as a regular formula (just press Enter). For older versions, you'll need to enter it as an array formula with Ctrl+Shift+Enter.

Option 2: Implement the Logic in VBA (More Robust for Macros) #

If you're integrating this into a macro, using VBA directly avoids formula array issues and gives you more control. Here's a reusable macro that dynamically finds row ranges and marks matches:

Sub MarkPreviouslyVerified()
    Dim wsCurrent As Worksheet, wsPrevious As Worksheet
    Dim lastRowCurrent As Long, lastRowPrevious As Long
    Dim i As Long
    Dim currentKey As String
    Dim previousKeys As Collection
    
    ' Update these worksheet names to match your actual sheet names
    Set wsCurrent = ThisWorkbook.Worksheets("CURRENT MONTH")
    Set wsPrevious = ThisWorkbook.Worksheets("PREVIOUS MONTH")
    
    ' Find last row with data in column A (adjust column if needed)
    lastRowCurrent = wsCurrent.Cells(wsCurrent.Rows.Count, "A").End(xlUp).Row
    lastRowPrevious = wsPrevious.Cells(wsPrevious.Rows.Count, "A").End(xlUp).Row
    
    ' Store all unique A+D combinations from previous month for fast lookup
    Set previousKeys = New Collection
    On Error Resume Next ' Ignore duplicate key errors
    For i = 5 To lastRowPrevious
        currentKey = wsPrevious.Cells(i, "A").Value & wsPrevious.Cells(i, "D").Value
        previousKeys.Add currentKey, Key:=currentKey
    Next i
    On Error GoTo 0 ' Reset error handling
    
    ' Check each row in current month and mark matches
    For i = 5 To lastRowCurrent
        currentKey = wsCurrent.Cells(i, "A").Value & wsCurrent.Cells(i, "D").Value
        On Error Resume Next
        ' Try to retrieve the key from the collection
        previousKeys.Item currentKey
        If Err.Number = 0 Then
            ' Match found: mark the cell (change column "E" to your target column)
            wsCurrent.Cells(i, "E").Value = "Previously Verified"
        Else
            ' No match: clear the cell
            wsCurrent.Cells(i, "E").Value = ""
        End If
        On Error GoTo 0
    Next i
End Sub

Why This Works: #

• Cells(Rows.Count, "A").End(xlUp).Row reliably finds the last non-empty row in column A (better than COUNTA if you have blank cells in the column).
• Using a Collection to store previous month's keys makes lookups much faster than looping through every row for each match.
• No fixed row ranges—this adapts automatically to any number of rows.

Quick Setup Tips: #

1. Replace "CURRENT MONTH" and "PREVIOUS MONTH" with your actual worksheet names.
2. Change column "E" to the column where you want the "Previously Verified" label to appear.
3. If your data starts at a different row than 5, adjust the For i = 5 To ... lines to match.

内容的提问来源于stack exchange，提问作者Katie H.