VBA: XLOOKUP Return Value Error

[zero269]

Hello,

I'm trying to insert a return value based on the value from a drop-down list using WorksheetFunction.Xlookup... within an existing Worksheet Change event.

Scenario:

1. I select a Student Name from a drop-down list.
   
   • This triggers a Worksheet Change event to create the drop-down list values (Year.Quarters) for the given Student (Period column). This works just fine.
2. I then need to select the active Period (Year.Quarter) from this new list of values. EX: 3.2

Plan:
What I'm trying to do now is automatically set the cell value based on the Students active Period… without breaking the Data Validation (drop-down) list.

Tried:
If I run the following code by itself, it will return and enter the correct value into the cell.

Sub WIP_Set_Active_Period()
Range("t_Period_Active[Period]").Value = _
  WorksheetFunction.XLookup(Range("t_Period_Active[Name]"), _
  Range("t_Students[Name]"), Range("t_Students[Period]"))
End Sub

However, when I try to use this code in the Worksheet Change code (below), it returns the following error:

My complete Worksheet Change code is:

Private Sub Worksheet_Change(ByVal Target As Range)

   Application.EnableEvents = False
 
  'If Name is changed
  If Not Intersect(Target, Range("t_Period_Active[Name]")) Is Nothing Then
 
    'Refresh Period List
    wsLists.Range("l_PlannerPeriod_DV").Calculate
   
    'Set Active Period
'    Range("t_Period_Active[Period]").Value = WorksheetFunction.XLookup(Range("t_Period_Active[Name]"), Range("t_Students[Name]"), Range("t_Students[Period]"))
     
  'If Period is changed
  ElseIf Not Intersect(Target, Range("t_Period_Active[Period]")) Is Nothing Then
    wsLists.Range("c_Planner_DateList").Calculate

    'Calculate Table Data
    wsPlanner.Calculate

    Application.EnableEvents = True
 
End Sub

Any help would be greatly appreciated.
Thank you,

Microsoft Office 365 (latest update)

 

[Kaper]

Basically, it shall work. I even tried to recreate small samle and it does. So 2 questions after playing with my small sample file:

1) Are you sure you have visible and initialized worksheet variables wsLists and wsPlanner?
2) Have you in your real code closing EndIf statement? (in presented above it is missing)

or may be also a third one - is the (currently commented-out line with Xlookup highlighted yellow when you see the error message from the screenshot?

 

[Kaper]

also a fourth one
- how is defined l_PlannerPeriod_DV name

 

[RoryA]

If any of those ranges are not on the sheet that the code is in, you will get that error. I'd suggest using this:

Private Sub Worksheet_Change(ByVal Target As Range)

   Application.EnableEvents = False
 
  'If Name is changed
  If Not Intersect(Target, Application.Range("t_Period_Active[Name]")) Is Nothing Then
 
    'Refresh Period List
    wsLists.Range("l_PlannerPeriod_DV").Calculate
   
    'Set Active Period
'    Application.Range("t_Period_Active[Period]").Value = WorksheetFunction.XLookup(Application.Range("t_Period_Active[Name]"), Application.Range("t_Students[Name]"), Application.Range("t_Students[Period]"))
     
  'If Period is changed
  ElseIf Not Intersect(Target, Application.Range("t_Period_Active[Period]")) Is Nothing Then
    wsLists.Range("c_Planner_DateList").Calculate

    'Calculate Table Data
    wsPlanner.Calculate

    Application.EnableEvents = True
 
End Sub

Or, since you are using tables, set Listobject type variables to them and use those.

 

[zero269]

Hello Kaper,

Are you sure you have visible and initialized worksheet variables wsLists and wsPlanner?

Yes. I've recently started using sheet CodeNames where I need to run vba code against.

Have you in your real code closing EndIf statement? (in presented above it is missing)

Yes. I trimmed my original code and forgot to include the ending. However, I have recently changed from ElseIf to separate IF blocks recommended in another thread that seemed to be a better approach for this specific Worksheet.

is the (currently commented-out line with Xlookup highlighted yellow when you see the error message from the screenshot?

Yes, that was the line being flagged with the error.

how is defined l_PlannerPeriod_DV name

It's a Named Range for a list of data on my Lists worksheet.
Name: l_PlannerPeriod_DV
Refers To: =Lists!$V$5#
Scope: Workbook

It's this named range that uses a Filter formula to build out the values for the Period drop-down list (Data Validation). I'm still looking for a VBA-based solution. I'll post a new thread eventually.

@RoryA mentioned that I should be using Application.Range where it refers to another Worksheet. I was unaware of that... in this instance. I think in your test, you may have had all the ranges on the same sheet...?

Thanks for taking the time to look into my issue.

Best regards,

 

[zero269]

If any of those ranges are not on the sheet that the code is in, you will get that error. I'd suggest using this:

Private Sub Worksheet_Change(ByVal Target As Range)

   Application.EnableEvents = False
 
  'If Name is changed
  If Not Intersect(Target, Application.Range("t_Period_Active[Name]")) Is Nothing Then
 
    'Refresh Period List
    wsLists.Range("l_PlannerPeriod_DV").Calculate
  
    'Set Active Period
'    Application.Range("t_Period_Active[Period]").Value = WorksheetFunction.XLookup(Application.Range("t_Period_Active[Name]"), Application.Range("t_Students[Name]"), Application.Range("t_Students[Period]"))
    
  'If Period is changed
  ElseIf Not Intersect(Target, Application.Range("t_Period_Active[Period]")) Is Nothing Then
    wsLists.Range("c_Planner_DateList").Calculate

    'Calculate Table Data
    wsPlanner.Calculate

    Application.EnableEvents = True
 
End Sub

Or, since you are using tables, set Listobject type variables to them and use those.

Hello RoryA,

"If any of those ranges are not on the sheet that the code is in, you will get that error."

You are absolutely correct. I tested Application.Range where the range refers to another sheet. The code now runs successfully.

I also noticed that I was using wsLists.Range in other areas, and it didn't dawn on me to use my worksheet Codename in this situation as well. I think I just expected the WorksheetFunction.Xlookup to know where those Table ranges were automagically.

Table "t_Period_Active[Period]" is on the same sheet, so Application.Range wasn't needed. However, the Table "t_Students[Name]" is located on my Lists worksheet, so I used Application.Range in those instances; later changed to wsLists.Range; my worksheet Codename. All is good!

Code changes:

From:

Range("t_Period_Active[Period]").Value = WorksheetFunction.XLookup(Range("t_Period_Active[Name]"), _
      Range("t_Students[Name]"), Range("t_Students[Period]"))

To:
Using Application.Range:

Range("t_Period_Active[Period]") = WorksheetFunction.XLookup(Range("t_Period_Active[Name]"), _
      Application.Range("t_Students[Name]"), Application.Range("t_Students[Period]"))

To:
Using my Sheets Codename:

    Range("t_Period_Active[Period]") = WorksheetFunction.XLookup(Range("t_Period_Active[Name]"), _
      wsLists.Range("t_Students[Name]"), wsLists.Range("t_Students[Period]"))

Thanks again Rory for your help, and best regards.


My final working code:

Private Sub Worksheet_Change(ByVal Target As Range)

  Application.EnableEvents = False
  
  'If Name is changed
  If Not Intersect(Target, Range("t_Period_Active[Name]")) Is Nothing Then
    wsLists.Range("l_PlannerPeriod_DV").Calculate 'Refresh Period List Data
    
    'Set Active Marking Period
    Range("t_Period_Active[Period]") = WorksheetFunction.XLookup(Range("t_Period_Active[Name]"), _
      wsLists.Range("t_Students[Name]"), wsLists.Range("t_Students[Period]"))
  End If
  
  'If Marking Period is changed
  If Not Intersect(Target, Range("t_Period_Active[Period]")) Is Nothing Then
    wsLists.Range("c_Planner_DateList").Calculate
  End If
    
  'If Planner 'Start Date' is changed
  If Not Intersect(Target, Range("C11")) Is Nothing Then
    wsLists.Range("c_Planner_DateList").Calculate
  End If
  
  'If Date Filter is changed
  If Not Intersect(Target, Range("L10")) Is Nothing Then
    Range("L10,O15").Calculate
    Call Planner_Book_List_LOAD
  End If
  
  'Calculate Worksheet
  wsPlanner.Calculate
  
  Application.EnableEvents = True