XLookup Structured Reference VBA

[TheGREATrandino]

I'm trying to use xlookup in a vba macro to search a table and I can't make it work.

in the spreadsheet this formula works =XLOOKUP(E3,tbl_DateRanges[Date Range],tbl_DateRanges[To])
In VBA Private Sub Worksheet_Change(ByVal Target As Range) this does not work
Range("I3").Value = Application.WorksheetFunction.XLookup(E3, Range("tbl_DateRanges[Date Range]"), Range("tbl_DateRanges[To]")
It generates Run-Time error "1004': Method "Range" of object _Worksheet' failed

when I try
Range("I3").Value = Application.WorksheetFunction.XLookup(E3,tbl_DateRanges[Date Range], tbl_DateRanges[To])
I get Compile error: Expected; list separator or )

 

[Fluff]

The E3 needs to be specified as a range

Range("I3").Value = Application.WorksheetFunction.XLookup(Range("E3"), Range("tbl_DateRanges[Date Range]"), Range("tbl_DateRanges[To]"))

 

[TheGREATrandino]

Range("I3").Value = Application.WorksheetFunction.XLookup(Range("E3"), Range("tbl_DateRanges[Date Range]"), Range("tbl_DateRanges[To]"))

didn't work.
Method "Range" of object "_Worksheet" failed
Also tried

 Range("I3").Value = Application.WorksheetFunction.XLookup(Range("E3").Value, Range("tbl_DateRanges[Date Range]"), Range("tbl_DateRanges[To]"))

 

[TheGREATrandino]

Got it to work

Range("I3").Value = Application.WorksheetFunction.XLookup(Range("E3"), Tables.Range("tbl_DateRanges[Date Range]"), Tables.Range("tbl_DateRanges[To]"))

had to specify the name of the sheet because the proc that called xlookup was not on the same sheet as the tables

 

[Fluff]

Glad you sorted it & thanks for the feedback.