not according to the desired result of the vba code-CodePudding

If you look at the sheet "test" results in column T there is an error 2042 and cause the row table to be more, what solution only slight modifications but without changing the structure of the code.

Thanks 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

SHEET TEST SHEET SOURCe SHEET TEST INPUT

CodePudding user response：

Determine Last Row When Rows Are Blank (But Not Empty)

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

If you don't care about accuracy and testing you can use:

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

End With