vba Microsoft Excel 2010 宏更新另一张纸上的行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5873414/
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
Microsoft Excel 2010 Macro To Update Rows on Another Sheet
提问by Leaum
I am having some issues creating a macro >.>.. And would like to request some help, because remaking sheets and re-doing all the data is really a pain.. Essentially I have Sheet1 and Sheet2, Sheet1 Contains "NEW" Data, and Sheet2 the "OLD" Data.
我在创建宏时遇到了一些问题 >.>.. 并且想请求一些帮助，因为重新制作表格并重新制作所有数据真的很痛苦.. 基本上我有 Sheet1 和 Sheet2，Sheet1 包含“新”数据和 Sheet2 是“旧”数据。
Inside Sheet2 There are specific column headers for row 1, Such as: (B1)title, (E1)quantity. And in Sheet1 there are the Headers of; (A1)Title, (G1) Quantity.
Sheet2里面的第1行有特定的列标题，如：(B1)title，(E1)quantity。在 Sheet1 中有标题；(A1) 标题，(G1) 数量。
Now my question is, can anyone create a working Macro that will scan through Sheet1 and Sheet2, Compare the Title Names, find the Common Titles.. And then simply UPDATE the quantity Column in Sheet2?
现在我的问题是，任何人都可以创建一个工作宏来扫描 Sheet1 和 Sheet2，比较标题名称，找到通用标题..然后简单地更新 Sheet2 中的数量列？
Sheet1 and Sheet2 Do NOT contain the same amount of Titles, but all the titles in Sheet2 should be found in Sheet1.. I just need it to Update the Quantity to the ones it finds in Sheet1.
Sheet1 和 Sheet2 不包含相同数量的标题，但 Sheet2 中的所有标题都应该在 Sheet1 中找到。我只需要将数量更新为它在 Sheet1 中找到的数量。
Does that make sense? (not the entire row either) Any help would be GREATLY appreciated. This has been driving me mad. Thank you in advanced.
那有意义吗？（也不是整行）任何帮助将不胜感激。这让我发疯。先谢谢了。
Edit for Lance:
为兰斯编辑：
Sheet1:
Title id Type Paperid Imageid uuid Quantity
Batman5132 23
Sheet2:
category title description price Quantity
Batman5132 54
And I would want the macro, to take Sheet2's Titles and look through Sheet1's titles and find the exact same titles, and JUST update the Quantity cells (With the Columns listed above)
我想要宏，获取 Sheet2 的标题并查看 Sheet1 的标题并找到完全相同的标题，然后更新数量单元格（使用上面列出的列）
回答by Lance Roberts
Dim sh1 As Worksheet
Dim rw2 As Range
Set sh1 = Worksheets("Sheet1")
For Each rw2 In Sheets("Sheet2").UsedRange.Rows
If Not sh1.UsedRange.Columns(1).Find(rw2.Cells(1, 2).Value,, xlValues, xlWhole) Is Nothing Then
rw2.Cells(1, 5) = sh1.UsedRange.Columns(1).Find(rw2.Cells(1, 2).Value,, xlValues, xlWhole).Offset(0, 6)
End If
Next rw2
回答by Ekkehard.Horner
If your data are decent and you are willing to execute a SQL statement from a Macro/Module Sub (Ref To ADO needed) or a script/program, the update could be done with a single
如果您的数据不错，并且您愿意从宏/模块子（需要 Ref To ADO）或脚本/程序执行 SQL 语句，则可以通过单个
UPDATE [OldSheet$] AS O INNER JOIN [NewSheet$] AS N ON O.[Title] = N.[Title] SET O.[Quantity] = N.[Quantity]
Evidence:
证据：
SELECT * FROM [NewSheet$]
!Title !Quantity!
!Title1 ! 10!
!Title2 ! 20!
!Title3 ! 30!
!Title4 ! 40!
!Title5 ! 50!
!Title6 ! 60!
!Title7 ! 70!
!Title8 ! 80!
!Title9 ! 90!
!Title10! 100!
SELECT * FROM [OldSheet$]
!Title !Quantity!
!Title1! 1!
!Title3! 3!
!Title5! 5!
!Title7! 7!
!Title9! 9!
UPDATE [OldSheet$] AS O INNER JOIN [NewSheet$] AS N ON O.[Title] = N.[Title] SET O.[Quantity] = N.[Quantity]
SELECT * FROM [OldSheet$]
!Title !Quantity!
!Title1! 10!
!Title3! 30!
!Title5! 50!
!Title7! 70!
!Title9! 90!
