如何使用 VBA Vlookup 填充单元格
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18476793/
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
How to populate cells using VBA Vlookup
提问by Carlos
I'm trying to populate a worksheet cells with some fields from another worksheet on the same workbook using VBA Vlookupand I'm stuck.
我正在尝试使用 VBA 使用同一工作簿上另一个工作表中的某些字段填充工作表单元格，但Vlookup我被卡住了。
Worksheet users have the following data, column A contains the Logincolumn B contains Number; it's empty but the goal is to populate it with data from another worksheet:
工作表用户有以下数据，ALogin列包含B列包含Number；它是空的，但目标是用另一个工作表中的数据填充它：
Login Number
===============
ffff
bbbb
cccc
dddd
eeee
aaaa
In the "Data" worksheet I've got the following:
在“数据”工作表中，我有以下内容：
Login Number
===============
aaaa 1234
bbbb 1235
cccc 1236
dddd 1237
eeee 1238
ffff 1239
Currently I'm using this code
目前我正在使用此代码
Sub VL()
Dim Login As Range
Set Login = Sheets("Users").Cells(2, 1)
Do Until Len(Login) = 0 'This will loop until the first empty cell
Login.Offset(0, 1).FormulaR1C1 = Application.WorksheetFunction.VLookup( _
Sheets("Users").Range("$A:$A"), [Table], 2, False)
Calculate
Login.Offset(0, 1).Value = Login.Offset(0, 1).Value
Set Login = Login.Offset(1, 0)
Loop
End Sub
And the result isn't as expected:
结果并不如预期：
Login Number
===============
ffff #N/A
bbbb
cccc
dddd
eeee
aaaa
I've searched on the forum but didn't find anything to help me solve this.
我在论坛上搜索过，但没有找到任何可以帮助我解决此问题的内容。
回答by tigeravatar
Sub VL()
With Sheets("Users").Range("A2", Sheets("Users").Cells(Rows.Count, "A").End(xlUp))
.Offset(, 1).Formula = "=VLOOKUP(A" & .Row & ",'Data'!$A:$B,2,FALSE)"
.Offset(, 1).Value = .Offset(, 1).Value
End With
End Sub
