VBA Excel Xlookup refer to table column

Question

I have a code for my ComboBox2 which works properly. Now I want to change values in combobox2 and based on that in the textbox6 get a number which is in a column (Total) which is the 6th column of the table (Table1) in another sheet (sheet2).

Private Sub combobox2_Change()

Dim tbl As ListObject
Dim rng As Range

   
Set tbl = ActiveWorkbook.Worksheets("sheet2").ListObjects("Table1")

Set rng = tbl.ListColumns(6).DataBodyRange

Me.TextBox6.Value = Application.WorksheetFunction.XLookup(Me.ComboBox2.Value, Sheets("sheet2").Range("A2:A7"), Sheets("sheet2").Rng)

End Sub

Could you please tell me why my code does not work? Thanks

I tried the code but I dont now how to get the third variable in the XLOOKUP correct.

Answer 1

Assuming you want to lookup the combobox value in the 1st table column and return the value in the 6th.

Private Sub ComboBox2_Change()

    Dim rng As Range
    With ActiveWorkbook.Worksheets("sheet2")
        Set rng = .ListObjects("Table1").DataBodyRange
        Me.TextBox6.Value = Application.WorksheetFunction.XLookup _
              (Me.ComboBox2.Value, rng.Columns(1), rng.Columns(6))
    End With

End Sub