vba Excel - 如何根据唯一的列标题名称从另一个工作表填充列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20617096/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel - How populate a column from another sheet based on unique column header names
提问by AlexS
This cant be difficult but I just can't see how to do it.
这并不难，但我就是不知道该怎么做。
I have Sheet 1 with i.e 3 empty columns, the top row is a data validation dropdown list of i.e. 50 unique header names present in Sheet 2.
我的工作表 1 有 3 个空列，顶行是数据验证下拉列表，即工作表 2 中存在的 50 个唯一标题名称。
Under each of the 50 headers in Sheet 2 there is an unknown number of rows of data.
在 Sheet 2 中的 50 个标题中的每一个标题下，都有未知数量的数据行。
From each of the 3 dropdown menus in Sheet 1, I simply want to populate that column with all data under that column header in Sheet 2.
从工作表 1 的 3 个下拉菜单中的每一个，我只想用工作表 2 中该列标题下的所有数据填充该列。
Is there a VBA solution?
有VBA解决方案吗？
回答by Julien Marrec
You don't need VBA for that, just use INDEX and MATCH, with ROW()
您不需要 VBA，只需使用 INDEX 和 MATCH，以及 ROW()
In Sheet1, cell A2 for example (if your header for that is in cell A1)
在 Sheet1 中，例如单元格 A2（如果您的标题在单元格 A1 中）
=IF(INDEX(Sheet2!$A:$M,ROW(),MATCH(Sheet3!A,Sheet2!$A:$M,0))=0,"",INDEX(Sheet2!$A:$M,ROW(),MATCH(Sheet3!B,Sheet2!$A:$M,0)))
You'll need to adjust the reference but you'll get the idea. You could also use pivot tables for that...
你需要调整参考，但你会明白的。您也可以为此使用数据透视表...
回答by Siddharth Rout
Under each of the 50 headers in Sheet 2 there is an unknown number of rows of data.
在 Sheet 2 中的 50 个标题中的每一个标题下，都有未知数量的数据行。
I would always prefer formulas over vba however if you have unknownnumber of rows and 50headers then personally I will never opt in for formulas. Specially if I have to drag it down. Here is a VBA solution.
我总是比 vba 更喜欢公式，但是如果你有unknown很多行和50标题，那么我个人永远不会选择公式。特别是如果我必须把它拖下来。这是一个 VBA 解决方案。
Let's say your Sheet2 looks like this
假设您的 Sheet2 看起来像这样
Paste this in Sheet1worksheet code area.
将此粘贴到Sheet1工作表代码区域中。
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Whoa
If Target.Cells.CountLarge > 1 Then Exit Sub
Dim wsI As Worksheet, wsO As Worksheet
Dim lRow As Long, nCol As Long
Dim sSrch As String
Dim aCell As Range, rng As Range
Set wsI = ThisWorkbook.Sheets("Sheet2")
Set wsO = ThisWorkbook.Sheets("Sheet1")
Application.EnableEvents = False
If Not Intersect(Target, Range("A1:C1")) Is Nothing Then
sSrch = Cells(1, Target.Column).Value
Set aCell = wsI.Rows(1).Find(What:=sSrch, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If Not aCell Is Nothing Then
nCol = aCell.Column
lRow = wsI.Cells(wsI.Rows.Count, nCol).End(xlUp).Row
Set rng = wsI.Range(wsI.Cells(2, nCol), wsI.Cells(lRow, nCol))
End If
If Not rng Is Nothing Then
Range(Cells(2, Target.Column), Cells(Rows.Count, Target.Column)).ClearContents
rng.Copy Cells(2, Target.Column)
End If
End If
Letscontinue:
Application.EnableEvents = True
Exit Sub
Whoa:
MsgBox Err.Description
Resume Letscontinue
End Sub
Output
输出
