VBA代码优化需求：按收益率曲线批量填充YC ABOVE TOLERANCE列

原代码核心问题 #

• 未针对单条收益率曲线的行范围做检查，错误遍历整个J列
• 未处理无触发阈值的场景，无法生成YC within tolerance标记
• 填充操作覆盖全表，而非当前曲线对应的行区间

修正后的代码 #

Sub PopulateYCToleranceFlagField()
    Application.DisplayStatusBar = True
    Application.ScreenUpdating = False ' 关闭屏幕刷新提升大文件处理效率
    
    Dim ws As Worksheet
    Dim lastRow As Long, currentRow As Long, curveStartRow As Long
    Dim currentCurve As String
    Dim hasGTTolerance As Boolean
    
    ' 指定目标工作表
    Set ws = ThisWorkbook.Sheets("QRM_YC_QoQ_Checks")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    ' 初始化起始行（假设表头在第1行，数据从第2行开始）
    curveStartRow = 2
    currentCurve = ws.Cells(curveStartRow, "A").Value
    
    For currentRow = 2 To lastRow
        ' 检测到新曲线或到达最后一行，处理上一条曲线
        If ws.Cells(currentRow, "A").Value <> currentCurve Or currentRow = lastRow Then
            Dim curveEndRow As Long
            curveEndRow = IIf(currentRow = lastRow, currentRow, currentRow - 1)
            
            ' 更新状态栏显示进度
            Application.StatusBar = "Processing row " & curveEndRow & " of " & lastRow & " - Curve: " & currentCurve
            DoEvents
            
            ' 检查当前曲线范围内是否存在触发阈值的记录
            hasGTTolerance = (WorksheetFunction.CountIf(ws.Range("J" & curveStartRow & ":J" & curveEndRow), "VAR GT TOLERANCE") > 0)
            
            ' 批量填充YC ABOVE TOLERANCE列
            If hasGTTolerance Then
                ws.Range("K" & curveStartRow & ":K" & curveEndRow).Value = "YC above tolerance"
            Else
                ws.Range("K" & curveStartRow & ":K" & curveEndRow).Value = "YC within tolerance"
            End If
            
            ' 更新当前曲线的起始信息
            currentCurve = ws.Cells(currentRow, "A").Value
            curveStartRow = currentRow
        End If
    Next currentRow
    
    ' 恢复系统设置
    Application.StatusBar = ""
    Application.ScreenUpdating = True
End Sub

关键逻辑说明 #

1. 按曲线分组处理：遍历A列识别每条收益率曲线的起止行，确保仅处理当前曲线的行范围
2. 精准容差判断：针对单条曲线的J列区间做CountIf检查，避免跨曲线误判
3. 覆盖全场景：同时处理触发阈值和未触发阈值的两种标记需求
4. 性能优化：关闭屏幕刷新，减少大数据集处理时的卡顿

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