vba - 从右到左搜索（多个范围）

Question

所有我有下面的For Each循环，它返回最后一列的值<> 0。虽然我确信有更有效的方法，但这很好用。

    Dim LastColFcst As Integer
    For Each p In Range("E44:P44,S44:AD44,AG44:AR44,AU44:BF44,BI44:BU44")
    If p.Value <> 0 And p.Offset(-35, 0).Value = "Forecast" Then
    LastColFcst = p.Column
    End If
    Next p

我现在希望为不同的行 86,88,95,99 获得值 <>0 的最后一列。我可以通过复制并粘贴四行的原始代码并使用 IF 语句来实现这一点。然而，这似乎是一种非常低效的方式来完成一项简单的任务。下面的示例代码；**编辑完整示例（下面发布的长效低效代码）

    Dim LastColFcst As Integer
For Each p In Range("E86:P86,S86:AD86,AG86:AR86,AU86:BF86,BI86:BU86")
If p.Value <> 0 And p.Offset(-77, 0).Value = "Forecast" Then
LastColFcst = p.Column
End If
Next p

For Each L In Range("E88:P88,S88:AD88,AG88:AR88,AU88:BF88,BI88:BU88")
If L.Value <> 0 And L.Offset(-79, 0).Value = "Forecast" And L.Column > LastColFcst Then
LastColFcst = L.Column
End If
Next L

For Each J In Range("E95:P95,S95:AD95,AG95:AR95,AU95:BF95,BI95:BU95")
If J.Value <> 0 And J.Offset(-79, 0).Value = "Forecast" And J.Column > LastColFcst Then
LastColFcst = J.Column
End If
Next J

For Each K In Range("E97:P97,S97:AD97,AG97:AR97,AU97:BF97,BI97:BU97")
If K.Value <> 0 And K.Offset(-88, 0).Value = "Forecast" And K.Column > LastColFcst Then
LastColFcst = K.Column
End If
Next K        

谁能建议解决这种情况的最佳方法是什么？

标签： vbaexcelfor-loopsearch