not according to the desired result of the vba code

Question

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

See [this](https://stackoverflow.com/questions/43631926/lastrow-and-excel-table) perhaps for how to find the last row in a table. — BigBen, Feb 25 '22 at 02:45
@BigBen , Thanks for your reply, can you create a solution from my code and answer in return for your answer. — roy, Feb 25 '22 at 02:57

VBasic2008 · Accepted Answer · 2022-02-25T06:14:38.900

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

not according to the desired result of the vba code

1 Answers1

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