vba 宏从单元格中查找数据并替换不同工作表上的数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18637892/
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
Macro to find data from cell and replace data on different sheet
提问by danwalk201
I'm after a hopefully simple Macro/VBA code for my sheet i am using, i have searched far and wide but my VBA knowledge is quite limited so am not sure what the coding is doing in other answers.
我正在为我正在使用的工作表寻找一个希望简单的宏/VBA 代码，我已经进行了广泛的搜索，但我的 VBA 知识非常有限，所以我不确定其他答案中的编码是做什么的。
Basically i have two sheets, Sheet2 has two columns, Column A has a number in it i.e. 2158 and column B has a name in it. What i want this macro to do is in Sheet1 i want to type in a number in Cell A1 and a name in cell B1 and then the macro finds that number used in Sheet1 Cell A1 in sheet 2 and then replaces the name from Sheet1 B1 with the correpsonding name in sheet2?
基本上我有两张工作表，Sheet2 有两列，A 列中有一个数字，即 2158，B 列中有一个名称。我想让这个宏做的是在 Sheet1 中，我想在单元格 A1 中输入一个数字，在单元格 B1 中输入一个名称，然后宏在工作表 2 中的 Sheet1 单元格 A1 中找到该数字，然后将 Sheet1 B1 中的名称替换为sheet2 中的对应名称？
Hope that makes sense!!
希望这是有道理的！！
Thanks
谢谢
Daniel
丹尼尔
采纳答案by Gary's Student
Give this a try:
试试这个：
Sub dural()
Dim s1 As Worksheet, s2 As Worksheet
Set s1 = Sheets("Sheet1")
Set s2 = Sheets("Sheet2")
v1 = s1.Range("A1")
v2 = s1.Range("B1")
s2.Activate
For Each r In Intersect(ActiveSheet.UsedRange, Range("A:A"))
If r.Value = v1 Then
r.Offset(0, 1).Value = v2
End If
Next
End Sub
回答by Chai Xiong
If you don't do anything with the name in sheet1!b1, you can pull the corresponding value from sheet2!b1 to sheet1!b1 using the VLOOKUP function.
如果对 sheet1!b1 中的名称不做任何处理，则可以使用 VLOOKUP 函数将 sheet2!b1 中的相应值拉到 sheet1!b1。
In Sheet1 Cell B1, enter the formula below. =VLOOKUP(A1,Sheet2!A:B,2,FALSE)
在 Sheet1 Cell B1 中，输入下面的公式。=VLOOKUP(A1,Sheet2!A:B,2,FALSE)
Then you can enter your number on sheet1!A1, the corresponding value from sheet2!b2 should display on sheet1!b1. If there's no matching found, you will get the #N/A.
然后你可以在 sheet1!A1 上输入你的数字，sheet2!b2 中的相应值应该显示在 sheet1!b1 上。如果未找到匹配项，您将获得 #N/A。
