如何设置VBA数组在找到值"L"时停止加载元素？

需求概述 #

原CopyData宏通过Lookups工作表C列加载列引用至colArray，需调整为：

• 根据用户输入的当前月份，从Lookups表中依次提取**前1个月、前2个月……**对应的C列列标识（如输入March则取February对应值"U"、January对应值"L"）
• 提取到值"L"时立即停止加载
• 数组最多保留5个元素

修改后的完整代码 #

Sub CopyData()
    Dim wsLookups As Worksheet
    Dim colArray() As String
    Dim currentMonth As String
    Dim currentRow As Long, lookupRow As Long
    Dim monthsBack As Integer
    Dim maxElements As Integer: maxElements = 5
    Dim stopValue As String: stopValue = "L"
    
    ' 初始化工作表对象
    Set wsLookups = ThisWorkbook.Worksheets("Lookups")
    
    ' 获取用户输入的当前月份
    currentMonth = InputBox("请输入当前月份（如March）:", "输入当前月份")
    If currentMonth = "" Then Exit Sub ' 用户取消输入则退出
    
    ' 查找当前月份在Lookups表中的行（假设月份存放在A列）
    On Error Resume Next
    currentRow = wsLookups.Range("A:A").Find(What:=currentMonth, LookIn:=xlValues, LookAt:=xlWhole).Row
    On Error GoTo 0
    If currentRow = 0 Then
        MsgBox "未找到指定月份，请检查输入！", vbExclamation
        Exit Sub
    End If
    
    ' 初始化数组，开始向前遍历月份
    ReDim colArray(1 To maxElements)
    monthsBack = 1
    lookupRow = currentRow - monthsBack
    Dim arrIndex As Integer: arrIndex = 1
    
    Do While lookupRow >= 1 And arrIndex <= maxElements
        ' 获取当前回溯月份对应的C列值
        Dim colValue As String
        colValue = Trim(wsLookups.Cells(lookupRow, "C").Value)
        
        ' 将值加入数组
        colArray(arrIndex) = colValue
        arrIndex = arrIndex + 1
        
        ' 检查是否触发停止条件
        If colValue = stopValue Then
            Exit Do
        End If
        
        ' 继续回溯下一个月
        monthsBack = monthsBack + 1
        lookupRow = currentRow - monthsBack
    Loop
    
    ' 调整数组大小至实际加载的元素数量
    If arrIndex > 1 Then
        ReDim Preserve colArray(1 To arrIndex - 1)
    Else
        ' 未加载到任何元素的处理
        MsgBox "未找到符合条件的列引用！", vbExclamation
        Exit Sub
    End If
    
    ' --------------------------
    ' 此处保留原CopyData宏中使用colArray的后续逻辑
    ' --------------------------
    ' 示例：打印数组内容用于测试
    Dim i As Integer
    For i = LBound(colArray) To UBound(colArray)
        Debug.Print "列引用" & i & ": " & colArray(i)
    Next i
End Sub

关键修改说明 #

• 月份定位逻辑：通过Find方法在Lookups表A列定位用户输入的当前月份行，确保后续回溯的基准正确
• 回溯遍历控制：从当前月份的上一行开始向前遍历，每次回溯一个月，同时限制数组最大长度为5
• 停止触发机制：每次提取C列值后判断是否等于"L"，是则立即终止遍历
• 数组动态调整：遍历结束后用ReDim Preserve调整数组大小，只保留实际加载的有效元素
• 异常处理：增加了用户取消输入、未找到指定月份的错误提示，避免宏崩溃

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