Excel VBA中的最后一行评估

问题描述：

我正在使用以下代码:

Sub Evaluation_Formula()
    Dim i As Long
    With Worksheets("Sheet1")
        i = .Evaluate("MIN(IF((LEFT($B$1:$B$89,5)*1)=C1,$B$1:$B$89,""""))")
        .Range("F3").Value2 = i
    End With
End Sub

但是，该公式仅限于B89，如何使用公式中B列的最后一行?

However, the formula is limited to B89, how can I use Last Row in Column B in the formula ?

答

我想这会发布正确的代码:

I guess this will post proper code:

Sub Evaluation_Formula_w_LR()
Dim i As Long
Dim LR As Long
With Worksheets("Sheet1")
    LR = .Cells(.Rows.Count, 2).End(xlUp).Row
    i = Evaluate("MIN(IF((LEFT($B$1:$B$" & LR & ",5)*1)=C1,$B$1:$B$" & LR & ",""""))")
    .Range("F10").Value2 = i
End With
End Sub

Excel VBA中的最后一行评估

相关推荐