get the last balance from last row in listbox into textbox on userform

[Ali M]

Hi
I search for way when search the item based on column D into textbox1 and populate the data in listbox should get the last balance from the last row is existed in listbox for the last column in listbox (H), and populate the last balance into textbox2
should not summing , just brings the last value from the last row for the last column in listbox to textbox2 .

here is the code

Private Sub Searchbutton_Click()
    With Sheets("Inventory List")
        If IsError(Application.Match(TextBox1.Text, .Columns(4), 0)) Then MsgBox "Modelnumber not found!": Exit Sub
        r = .Range("A3", .Range("H" & .Rows.Count).End(xlUp)).Resize(, 9)
    End With
    With ListBox1
        .List = r
        .ColumnCount = UBound(r, 2)
        For i = 0 To .ListCount - 1
            .List(i, .ColumnCount - 1) = i + 3
        Next
        For i = .ListCount - 1 To 0 Step -1
            If UCase(.List(i, 3)) <> UCase(TextBox1.Text) Then .RemoveItem i
        Next
    End With
End Sub

 

[rlv01]

Perhaps something like this

TextBox1.Value = ListBox1.List(UBound(ListBox1.List, 1), UBound(ListBox1.List, 2))

near the end of your sub.

 

[Ali M]

thanks for your guessing , but unfortunately this is not what I look for it
based on your suggestion
this is what I got

obviously indicate to last row number into sheet as I mark by red .
but what I want as marking by red for textbox,listbox

thanks again

 

[Domenic]

Try . . .

    With Me.ListBox1
        Me.TextBox2.Value = .List(.ListCount - 1, .ColumnCount - 1)
    End With

Hope this helps!

 

[Ali M]

@Domenic thanks
nothing changes as in pic1

 

[Domenic]

My understanding is that you want the list item from the last row and last column, correct? If so, maybe you have other columns that are hidden?

In any case, since I see now that you actually specified Column H, try the following instead...

    With Me.ListBox1
        Me.TextBox2.Value = .Column(7, .ListCount - 1)
    End With

Note that column indexing starts at 0.

Hope this helps!

 

[Ali M]

My understanding is that you want the list item from the last row and last column, correct?

that's correct.

If so, maybe you have other columns that are hidden?

no ! there is no hidden

In any case, since I see now that you actually specified Column H, try the following instead...

it works perfectly
thanks very much

 

[Domenic]

Hmmm.... I don't understand why the solution offered by @rlv01 and myself didn't give you the desired result. Oh well, I'm glad you have what you need.

Cheers!