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

Answer 2

Your issue is that there is no Sheets("sheet2").Rng object, variable rng would suffice as 3rd argument in your XLookUp function.

Assuming range A2:A7 is part of your listobject, you might code as follows.

Private Sub combobox2_Change()

'a) reference Listobject in Sheet2
    Dim tbl As ListObject
    Set tbl = Sheet2.ListObjects("Table1")          ' referencing the worksheet's Code(Name) Sheet2
'b) reference listobject data ranges
    Dim lookupRng As Range
    Set lookupRng = tbl.ListColumns(1).DataBodyRange    ' or: identify column by its name string in bracket
    Dim returnRng As Range
    Set returnRng = tbl.ListColumns(6).DataBodyRange    ' or: identify column by its name string in bracket
'c) get result providing for non-finding
    Dim result
    result = Application.WorksheetFunction.XLookup(Me.ComboBox2.value, lookupRng, returnRng, "")
'd) write result
    If result <> vbNullString Then
        Me.TextBox6.value = result
    End If

End Sub

**Help reference XLookUp function