arrays - 不符合vba代码的预期结果

Question

如果查看T列的工作表“测试”结果有错误2042并导致行表更多，什么解决方案只是稍微修改但不改变代码结构。
谢谢
jack

Sub test()
  Dim Z, U&
With Sheets("test").Range("t2:t" & Sheets("test").Cells(Rows.Count, "H").End(xlUp))
       .Formula = "=LOOKUP([@Date]+0.5,Table2[Date]/(Table2[ITEM NO]=[@[ITEM NO]]),Table2[PRICE])"
        Z = .Value
        For U = 1 To UBound(Z):  Z(U, 1) = CStr(Z(U, 1)):  Next
       .Value2 = Z
    End With
End Sub

Answer 1

当行为空白(但不为空)时确定最后一行

Dim lRow As Long
With Worksheets("test").Range("H2")
    Dim lCell As Range: Set lCell = .Resize(.Worksheet.Rows.Count _
        - .Row + 1).Find("*", , xlValues, , , xlPrevious)
    If lCell Is Nothing Then Exit Sub
    lRow = lCell.Row
End With
    
With Worksheets("test").Range("T2:T" & lRow)
' continue...

End With

如果您不关心准确性和测试，您可以使用:

With Worksheets("test").Range("T2:T" & Worksheets("test").Columns("H").Find("*", , xlValues, , , xlPrevious).Row)
' continue...

End With