vba 匹配名称并从工作表 1 复制到匹配名称旁边的工作表 2
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7895398/
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
Match name and copy from sheet 1 to sheet 2 next to matched name
提问by user1013478
I have an Excel sheet with names in column A and an amount in column B for sheet 1.
我有一个 Excel 工作表，A 列中有名称，B 列中有一个金额用于工作表 1。
I have a another sheet that is sheet2 with names in A just like in sheet 1 and column B is blank.
我有另一个工作表是工作表 2，名称在 A 中，就像在工作表 1 中一样，而 B 列是空白的。
How can I check sheet 1 A name to check with sheet2 A name, if they match then take amount next to that name on sheet1 and copy the amount into the cell next to the matching name on sheet2 next to the name? The names on sheet1 change daily.
如何检查工作表 1 A 名称以检查工作表 2 A 名称，如果它们匹配，则在工作表 1 上该名称旁边取金额并将金额复制到名称旁边的工作表 2 上匹配名称旁边的单元格中？sheet1 上的名称每天都在变化。
I have tried this and get nothing.
我已经试过了，但一无所获。
Sub Macro1()
'
' Macro1 Macro
'
Dim RowIndex As Integer
Sheets("Sheet1").Select
RowIndex = Cells.Row
While DoOne(RowIndex)
RowIndex = RowIndex + 3
Wend
End Sub
Function DoOne(RowIndex As Integer) As Boolean
Dim Key
Dim Target
Dim Success
Success = False
If Not IsEmpty(Cells(RowIndex, 1).Value) Then
Key = Cells(RowIndex, 1).Value
Sheets("sheet2").Select
Set Target = Columns(2).Find(Key, LookIn:=xlValues)
If Not Target Is Nothing Then
Rows(Target.Row).Select
Selection.Copy
Sheets("Sheet1").Select
Rows(RowIndex + 1).Select
Selection.Insert Shift:=xlDown
Rows(RowIndex + 2).Select
Application.CutCopyMode = False
Success = True
End If
End If
DoOne = Success
End Function
Sheet 1:
第 1 页：
A B
A One Preservation .00
A&D Recovery, Inc. ,108.46
A&S Field Services, Inc. ,941.56
A&T Jax Inc ,842.48
Sheet 2:
第 2 页：
A B - blank cell
A One Preservation - Calvin & Renee
A&D Recovery, Inc. - Drew & Adam
A&S Field Services, Inc. - Aaron
A&T Jax Inc - Tyson
回答by brettdj
This code uses an Index/Match solution to copy the matched B values from sheet1 from sheet2. The code will work with variable sheet names
此代码使用索引/匹配解决方案从 sheet2 复制来自 sheet1 的匹配 B 值。该代码将适用于可变工作表名称
- blank cells are ignored
- Non-matches on the second sheet are flagged as "no match".
The code removes the formulae from column B on the second sheet by updating with values only
Update: if you second sheet names are the same as sheet1, but have a " -some text" to the right, then use this updated part of the code
With rng1.Offset(0, 1) .FormulaR1C1 = "=IF(RC[-1]<>"""",IF(NOT(ISERROR(MATCH(LEFT(RC[-1],FIND("" -"",RC[-1])-1),'" & ws1.Name & "'!C[-1],0))),INDEX('" & ws1.Name & "'!C,MATCH(LEFT(RC[-1],FIND("" -"",RC[-1])-1),'" & ws1.Name & "'!C[-1],0)),""no match""),"""")" .Value = .Value End Withoriginal
Sub QuickUpdate() Dim ws1 As Worksheet Dim ws2 As Worksheet Dim rng1 As Range Set ws1 = Sheets(1) Set ws2 = Sheets(2) Set rng1 = ws2.Range(ws2.[a1], ws2.Cells(Rows.Count, "A").End(xlUp)) With rng1.Offset(0, 1) .FormulaR1C1 = "=IF(RC[-1]<>"""",IF(NOT(ISNA(MATCH(RC[-1],'" & ws1.Name & "'!C[-1],0))),INDEX('" & ws1.Name & "'!C,MATCH(RC[-1],'" & ws1.Name & "'!C[-1],0)),""no match""),"""")" .Value = .Value End With End Sub
- 空白单元格被忽略
- 第二张纸上的不匹配被标记为“不匹配”。
该代码通过仅更新值来从第二个工作表上的 B 列中删除公式
更新：如果您的第二个工作表名称与工作表 1 相同，但右侧有一个“-some text”，则使用代码的此更新部分
With rng1.Offset(0, 1) .FormulaR1C1 = "=IF(RC[-1]<>"""",IF(NOT(ISERROR(MATCH(LEFT(RC[-1],FIND("" -"",RC[-1])-1),'" & ws1.Name & "'!C[-1],0))),INDEX('" & ws1.Name & "'!C,MATCH(LEFT(RC[-1],FIND("" -"",RC[-1])-1),'" & ws1.Name & "'!C[-1],0)),""no match""),"""")" .Value = .Value End With原来的
Sub QuickUpdate() Dim ws1 As Worksheet Dim ws2 As Worksheet Dim rng1 As Range Set ws1 = Sheets(1) Set ws2 = Sheets(2) Set rng1 = ws2.Range(ws2.[a1], ws2.Cells(Rows.Count, "A").End(xlUp)) With rng1.Offset(0, 1) .FormulaR1C1 = "=IF(RC[-1]<>"""",IF(NOT(ISNA(MATCH(RC[-1],'" & ws1.Name & "'!C[-1],0))),INDEX('" & ws1.Name & "'!C,MATCH(RC[-1],'" & ws1.Name & "'!C[-1],0)),""no match""),"""")" .Value = .Value End With End Sub
回答by lurker
Why not use the VLOOKUP function?
为什么不使用 VLOOKUP 函数？
Sheet1 has your names in column A, and values in column B. Sheet2 has your lookup names in column A, and in column B, you put:
Sheet1 在 A 列中有你的名字，在 B 列中有值。 Sheet2 在 A 列中有你的查找名称，在 B 列中，你输入：
=VLOOKUP(A1,Sheet1!$A:$B$n,2,FALSE)
Where 'n' is the number of rows in your Sheet1 table.
其中“n”是 Sheet1 表中的行数。
The only issue with this is it will put an #N/A if it can't find the name in Sheet1. There's likely a way to put in an alternate entry using a conditional.
唯一的问题是如果在 Sheet1 中找不到名称，它会输入 #N/A。可能有一种方法可以使用条件输入替代条目。
