Problem with VLookup Using Application.WorksheetFunction.VLookup

[MarkMueller]

I am working on a small Excel Project that builds a UserForm with Selected Data from a Workbook that has 4 sheets of which 2 sheets are important Sheet 2 (Data) and Sheet 3 (Lists).
The (Data) sheet has an area that is populated based on input from the UserForm called: frmTraining. And uses a Criteria Range and Extract Range to highlight the Listbox. Controles at the bottom of the Listbox provide the ability to Input New Trainers, Update Trainer Information, and/or Add New Training.

So I am attempting to lookup the Course Code then automatically populate the Course Name Associated with the Course Code during [Adding New Trainers], I have added a section (Sub) for the Course Name Textbox and within the cmdAdd (Sub), and cmdTraining (Sub). Highlighted the VLookup sections with "-------------" Top and Bottom.

So when the Form loads and I select, from the drop-down [Course Code], the Course Name is not populated. The cmdTraining (Sub) has a different Lookup than the cmdAdd and Reg7 (Sub) I was testing different options.

So I guess I really need to understand what is happening and why? There is obviously a better solution, but I do not know what it is and I have been trying for a week.

Receiving the following error 1004, unable to get VLookup property of the WorksheetFunction class, from the Reg7 (Sub).

 

[Alex Blakenburg]

Unless I am mistaken, your code is trying to get a value from Column 6 in the range Name LookCourse, which only has 2 columns.

Reg7.Value = Application.WorksheetFunction.VLookup(Reg6.Value, Sheet3.Range("LookCourse"), 6, 1)

LookCourse = Lists!$H$7:I$388 (where Lista has a code name of Sheet3)

 

[MarkMueller]

Unless I am mistaken, your code is trying to get a value from Column 6 in the range Name LookCourse, which only has 2 columns.

Reg7.Value = Application.WorksheetFunction.VLookup(Reg6.Value, Sheet3.Range("LookCourse"), 6, 1)

LookCourse = Lists!$H$7:I$388 (where Lista has a code name of Sheet3)

View attachment 50731

Thanks for that, will make that change.

 

[MarkMueller]

Still receiving the error 1004. Copied as an image.

 

[MarkMueller]

Training_Mng_new.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
6	First Name	Last Name	Score	StaffID	Role	Course Code	Course Name	Version	Started	Frequency	Due	ID		Frequency	Started	Due	Last Name	Role
7	Sebastion	Therrien	10	AHS21	Shovel Operator	AHS-TDS-195	3 FrontRunner Restart	340	09/16/2020	30	10/16/2020	101						Shovel Operator
8	Vincent	Pike	10	MMSI4	Shovel Operator	AHS-TDS-193	1 FrontRunner Foundations	320	04/10/2020	30	05/10/2020	103
9	Vanessa	Simon	8	AHS05	Mechanic	AHS-TDS-195	3 FrontRunner Restart	320	09/16/2020	200	04/04/2021	144
10	Carl	Perkins	10	MMSI2	Manager	AHS-TDS-195	3 FrontRunner Restart	320	01/28/2019	30	02/27/2019	145
11	Jesse	MacDonald	7	MMSI3	Manager	AHS-TDS-196	6 FrontRunner Suspend & Release	340	11/10/2020	90	02/08/2021	149
12	Kevin	Janowsky	10	AHS22	Shovel Operator	AHS-TDS-677	FR340-STD-LHS-MA-02-entry-emv-collision-risk-ML	340	12/12/2019	60	02/10/2020	150
13	Vladimir	Villarroel	9	AHS10	Blaster	AHS-TDS-678	FR340-STD-LHS-MA-02-entry-emv-emergency-stop-failure-ML	340	03/18/2020	90	06/16/2020	152
14	Bree	Gorman	8	AHS18	Electrician	AHS-TDS-196	4 FrontRunner Obstacles	320	08/22/2020	120	12/20/2020	154
15	Mark	Mueller	9	AHS99	Administration	AHS-TDS-195	5 FrontRunner Escort	330	10/11/2020	30	11/10/2020	156
16	Test	Tester	3	TST1	Shovel Operator	AHS-TDS-195	3 FrontRunner Restart	320	10/11/2020	120	02/08/2021	157
17	Testical	Balls	3	TEST1	Blaster	AHS-TDS-196	6 FrontRunner Suspend & Release	320	10/11/2020	30	11/10/2020	158
Data

Cell Formulas
Range	Formula
K17,K12:K15,K8:K10	K8	=I8+J8

 

[MarkMueller]

Training_Mng_new.xlsm
	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE
4		Criteria Area									Extract Area
5
6	Frequency	Started	Due	Last Name	Role		First Name	Last Name	Score	StaffID	Role	Course Code	Course Name	Version	Started	Frequency	Due	ID
7					Shovel Operator		Sebastion	Therrien	10	AHS21	Shovel Operator	AHS-TDS-195	3 FrontRunner Restart	340	09/16/2020	30	10/16/2020	101
8							Vincent	Pike	10	MMSI4	Shovel Operator	AHS-TDS-193	1 FrontRunner Foundations	320	04/10/2020	30	05/10/2020	103
9							Kevin	Janowsky	10	AHS22	Shovel Operator	AHS-TDS-677	FR340-STD-LHS-MA-02-entry-emv-collision-risk-ML	340	12/12/2019	60	02/10/2020	150
10							Test	Tester	3	TST1	Shovel Operator	AHS-TDS-195	3 FrontRunner Restart	320	10/11/2020	120	02/08/2021	157
Data

 

[Fluff]

What is Reg6?

 

[MarkMueller]

Training_Mng_new.xlsm
	H	I
5	LookCourse Name Range
6	Course Code	Course Name
7	AHS-TDS-193	193-1 FrontRunner Foundations
8	AHS-TDS-195	195-3 FrontRunner Restart
9	AHS-TDS-196	196-4 FrontRunner Obstacles
10	AHS-TDS-197	197-5 FrontRunner Escort
11	AHS-TDS-198	198-6 FrontRunner Suspend & Release
12	AHS-TDS-199	199-7 FrontRunner Refine Locations
13	AHS-TDS-200	200-8 FrontRunner Roads & Locations
14	AHS-TDS-201	201-9 FrontRunner Pass Area
15	AHS-TDS-202	202-10 FrontRunner Taught Course
16	AHS-TDS-203	203-11 FrontRunner Dry Run
17	AHS-TDS-204	204-12 FrontRunner AHT Inspection
18	AHS-TDS-205	205-13 FrontRunner Loading Unit
19	AHS-TDS-206	206-14 FrontRunner Dozer Operator
20	AHS-TDS-207	207-15 FrontRunner Crusher
21	AHS-TDS-208	208-16 FrontRunner FuelBay Operator
22	AHS-TDS-209	209-17 FrontRunner Grader
23	AHS-TDS-210	210-1 FrontRunner Foundations
24	AHS-TDS-211	211-2 FrontRunner Entry EMV
25	AHS-TDS-212	212-3 FrontRunner Restart
Lists

 

[MarkMueller]

What is Reg6?

It is a control at the bottom of the Form. Attached Image.

 

[MarkMueller]

So Reg6 represents Course Code on the WorkSheet. Reg7 Represents Course Name. Together they provide the course detail.