vba 宏执行 VLOOKUP 搜索特定列名称以输入特定信息

Macro to do a VLOOKUP that searches for specific column names to input specific information

提问by user2048816

I need some help with a macro/vlookup. Originally, I recorded the macro and just did the VLOOKUP manually thinking that would always work. Lately, it hasn't been functioning properly (and I'm not sure why except that it's possible columns are not always in the same place from time to time). Is there a way to write this macro out so that it will work each time?

我需要一些有关宏/vlookup 的帮助。最初，我录制了宏，只是手动进行了 VLOOKUP，认为这将始终有效。最近，它没有正常运行（我不知道为什么，除了可能列有时不总是在同一个地方）。有没有办法把这个宏写出来，让它每次都能工作？

Here is what I'm trying to do:

这是我想要做的：

I have a master spreadsheet and a vacancy spreadsheet. The master spreadsheet has information that I need to move to the vacancy spreadsheet. Not everyone on the master spreadsheet is listed on the vacancy spreadsheet (and vice versa) nor is either in a specified order. I already have a macro that adds the column names I need to the vacancy spreadsheet so that's set. I just need to get the information in there.

我有一个主电子表格和一个空缺电子表格。主电子表格包含我需要移至空缺电子表格的信息。并非主电子表格中的每个人都列在空缺电子表格中（反之亦然），也不是按特定顺序列出的。我已经有一个宏，可以将我需要的列名添加到空缺电子表格中，以便设置。我只需要在那里获取信息。

I want something that does the following: Take the employee number from the vacancy spreadsheet and search for it in the master spreadsheet, then copies the information from specific column names (off the Master spreadsheet) into the columns (of the same name) on the Vacancy spreadsheet

我想要执行以下操作：从空缺电子表格中获取员工编号并在主电子表格中搜索它，然后将信息从特定列名称（从主电子表格中删除）复制到空缺电子表格

Ex: employee number 12345 is on the vacancy spreadsheet. The macro searches the Master Spreadsheet for employee number 12345, then moves the information (within the Master Spreadsheet) from the columns Category, Center Name, Job, Location, Code, Tracking, Reason, Salary over to the columns of the same name on the vacancy spreadsheet.

例如：员工编号 12345 在空缺电子表格中。宏在主电子表格中搜索员工编号 12345，然后将信息（在主电子表格内）从类别、中心名称、工作、位置、代码、跟踪、原因、工资列移动到空缺电子表格。

There are many more columns that I need the information from, but that's a sampling. These spreadsheets are huge with tens of thousands of rows of data. Any assistance you can offer would be greatly appreciated!

我需要从中获取信息的列还有很多，但这只是一个示例。这些电子表格非常庞大，包含数万行数据。您能提供的任何帮助将不胜感激！

I hope this all made sense.

我希望这一切都是有道理的。

回答by David Zemens

Edit based on workbook file examples

基于工作簿文件示例进行编辑

In the SAMPLE Milestone Report.xlsx file, column M, place the following formula:

在 SAMPLE Milestone Report.xlsx 文件的 M 列中，放置以下公式：

=INDEX('[SAMPLE Master Active.xlsx]Sheet1'!$1:$4,MATCH($A2,'[SAMPLE Master Active.xlsx]Sheet1'!$A:$A,FALSE),MATCH(M$1,'[SAMPLE Master Active.xlsx]Sheet1'!$1:$1,FALSE))

=INDEX('[SAMPLE Master Active.xlsx]Sheet1'!$1:$4,MATCH($A2,'[SAMPLE Master Active.xlsx]Sheet1'!$A:$A,FALSE),MATCH(M$1,'[SAMPLE Master Active.xlsx]Sheet1'!$1:$1,FALSE))

You can then copy & paste this formula across the remainder of the rows/columns. Some of them are going to return #N/Abecause the column labels don't match exactly. A similar error would return if the Employee Number could not be found.

然后，您可以在行/列的其余部分复制并粘贴此公式。其中一些将返回，#N/A因为列标签不完全匹配。如果找不到员工编号，则会返回类似的错误。

The Index function refers to rows 1:4 of the Master Active workbook, sheet 1.

索引函数引用主活动工作簿，工作表 1 的 1:4 行。

The next argument uses the MATCH() function to find the row # of the employee number in column A of the Master Active Workbook, sheet 1.

下一个参数使用 MATCH() 函数在主活动工作簿的工作表 1 的 A 列中查找员工编号的行号。

The next argument uses the MATCH() function to find the column # of the column label, in Row 1 of the Master Active workbook, sheet 1.

下一个参数使用 MATCH() 函数在主活动工作簿的第 1 行工作表 1 中查找列标签的列 #。

So this way we are able to refer to the range containing all data in your Master Active workbook, and dynamically identify the ROW in which the employee number exists, and the COLUMN in which the column label exists. The Index function then returns the value from this intersection.

这样我们就可以在你的 Master Active 工作簿中引用包含所有数据的范围，并动态识别员工编号所在的 ROW，以及列标签所在的 COLUMN。Index 函数然后从这个交集返回值。

Original Answer

原答案

The VLOOKUPfunction requires three arguments with an optional fourth argument.

该VLOOKUP函数需要三个参数和一个可选的第四个参数。

1. Lookup_Value - this is the value you're searching for
2. Table_Array - this is the range/table in which you're searching for lookup_value
3. Column_# - the formula will return the value in matching row, for this column #, within the table_array.
4. Range_lookup (optional) - tells Excel whether to return an approximate match or an exact match. I have never needed to use approxiamte match, so I always set this to False.

1. Lookup_Value - 这是您要搜索的值
2. Table_Array - 这是您要搜索的范围/表格 lookup_value
3. Column_# - 公式将返回匹配行中的值，对于此列 #，在table_array.
4. Range_lookup（可选）- 告诉 Excel 是返回近似匹配还是精确匹配。我从来不需要使用近似匹配，所以我总是将它设置为False.

What you need to do is make the Column_#a dynamic value, based on some other criteria. You can probably do this with the MATCH()function.

您需要做的是Column_#根据其他一些标准使该值成为动态值。你可能可以用这个MATCH()函数来做到这一点。

The following examples use worksheet function conventions, but shoould easily translate to VBA.

以下示例使用工作表函数约定，但应该很容易转换为 VBA。

=VLOOKUP("steve", "A:C", 3, False)will return the value in column 3 from the row where "steve" is found in column A.

=VLOOKUP("steve", "A:C", 3, False)将从在 A 列中找到“史蒂夫”的行返回第 3 列中的值。

But assuming your data will not always be in column 3... lets say you are looking for "Category" and it might be in any column, but you know that the ccolumn labels will be in row 1. Then, instead of "3" in the VLOOKUP...formula, you would do:

但是假设您的数据并不总是在第 3 列中......假设您正在寻找“类别”并且它可能在任何列中，但您知道 ccolumn 标签将在第 1 行中。然后，而不是“3 " 在VLOOKUP...公式中，你会这样做：

Match("Category",'Vacancy_Sheet_Name'!1:1, False)

匹配（“类别”，'Vacancy_Sheet_Name'！1：1，假）

so your end resulting formula would be:

所以你最终得到的公式是：

=VLOOKUP("steve", "A:C", Match("Category",'Vacancy_Sheet_Name'!1:1, False), False)

=VLOOKUP("steve", "A:C", Match("Category",'Vacancy_Sheet_Name'!1:1, False), False)

In vba, this would be:

在 vba 中，这将是：

= Application.WorksheetFunction.Vlookup("steve", "A:C", Application.Match("Category",Sheets("Vacancy_Sheet_Name").Range("1:1"),False), False)

= Application.WorksheetFunction.Vlookup("steve", "A:C", Application.Match("Category",Sheets("Vacancy_Sheet_Name").Range("1:1"),False), False)

An important note:With VLOOKUPit is ALWAYS looking in the first column of Table_Arrayfor the Lookup_Value. If the structure of your data has changed, and the lookup_valueis no longer in the FIRST column, VLOOKUPis not the appropriate function to use. In cases like these, it will probably be necessary to use a combination of INDEX()and MATCH()functions, because VLOOKUPwill not work.

重要提示：由于VLOOKUP它总是在寻找的第一列Table_Array的Lookup_Value。如果您的数据结构已更改，并且lookup_value不再位于 FIRST 列中，VLOOKUP则不是使用合适的函数。在这种情况下，可能需要使用INDEX()和MATCH()函数的组合，因为VLOOKUP不会起作用。