获取指定Forecast列至D列范围内的最新有效值问题排查

需要读取指定行中，从Forecast列（BM列）到D列范围内的最新有效值（即最靠近Forecast列的非空/有效数值），示例中预期结果为AV21单元格的0.5，但现有VBA代码未得到正确结果。

现有VBA代码 #

Option Explicit

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Target.Column <> 9 Then Exit Sub
    Dim foundCell As Range:  Set foundCell = Cells(Target.Row, "I").End(xlToLeft)
    Target.Offset(, 1).Value = foundCell.Value
    Target.Offset(, 2).Value = Cells(1, foundCell.Column).Value
End Sub
Public Sub LoopCells()

Dim c As Range
Dim rngSearch As Range
Dim rngFind As Range
 Set rngFind = ActiveSheet.Range("A1:K15")

For Each c In rngFind
    If c.Value = "Forecast" Then
      MsgBox "FindMe found at " & c.Address
      
      Set rngSearch = ActiveSheet.Range("A1:" & c.Address)
      
      'Set LastCell = ActiveSheet.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
      
      ' MsgBox LastCell
     Dim d As Range

For Each d In rngSearch 'Range("strFirstAddress:A1").Cells
  If d.Value > 0 Then
    
    MsgBox foundCell
   
    
    Exit For
  End If
Next

MsgBox foundCell
MsgBox "The first value greater than zero is in cell " & firstAddress & _
       "; - it has value " & firstValue
    End If
Next c

End Sub

示例截图 #

问题分析与修正代码 #

原代码存在以下问题：

• 搜索范围错误：rngSearch设置为A1到Forecast单元格，范围过大且未限定为当前行从BM列往左到D列的方向
• 未聚焦目标行：代码逻辑未针对需要取值的行（如示例中的21行）处理
• 变量未正确定义赋值：foundCell、firstAddress等变量未在LoopCells过程中正确初始化和赋值

以下是修正后的代码，实现从指定行的BM列（Forecast列）往左到D列，查找第一个有效值（这里定义为大于0的数值，可按需调整）：

Option Explicit

' 双击I列单元格时，自动获取当前行从BM列到D列的最新有效值
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    ' 仅处理第9列（I列）的双击事件
    If Target.Column <> 9 Then Exit Sub
    
    Dim targetRow As Long
    targetRow = Target.Row
    
    Dim startCol As Long, endCol As Long
    startCol = Columns("BM").Column ' Forecast列（BM列）
    endCol = Columns("D").Column    ' 起始列D列
    
    Dim foundCell As Range
    ' 从BM列向左遍历到D列，查找当前行第一个大于0的有效值
    For Col = startCol To endCol Step -1
        If Cells(targetRow, Col).Value > 0 Then
            Set foundCell = Cells(targetRow, Col)
            Exit For
        End If
    Next Col
    
    ' 将结果写入目标单元格右侧
    If Not foundCell Is Nothing Then
        Target.Offset(, 1).Value = foundCell.Value
        Target.Offset(, 2).Value = Cells(1, foundCell.Column).Value ' 写入对应列标题
        Cancel = True ' 取消双击进入编辑模式
    Else
        Target.Offset(, 1).Value = "无有效值"
    End If
End Sub

' 独立调用过程：指定行号，获取该行从BM列到D列的最新有效值
Public Sub GetLatestValidValue(targetRow As Long)
    Dim startCol As Long, endCol As Long
    startCol = Columns("BM").Column
    endCol = Columns("D").Column
    
    Dim foundCell As Range
    For Col = startCol To endCol Step -1
        If Cells(targetRow, Col).Value > 0 Then
            Set foundCell = Cells(targetRow, Col)
            Exit For
        End If
    Next Col
    
    If Not foundCell Is Nothing Then
        MsgBox "最新有效值位于" & foundCell.Address & "，值为：" & foundCell.Value & vbCrLf & _
               "列标题：" & Cells(1, foundCell.Column).Value
    Else
        MsgBox "第" & targetRow & "行从D列到BM列范围内无有效值"
    End If
End Sub

' 测试：获取示例中第21行的最新有效值
Public Sub TestGetValue()
    GetLatestValidValue 21
End Sub

代码说明 #

1. Worksheet_BeforeDoubleClick事件：双击I列任意单元格时，自动获取当前行的目标范围有效值，将结果和对应列标题写入右侧单元格
2. GetLatestValidValue过程：可指定行号单独调用，返回该行的最新有效值信息
3. 遍历逻辑：从BM列向左（列号递减）遍历到D列，第一个符合条件的数值即为"最新有效值"（最靠近Forecast列的有效数据）

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