vba Excel 宏中的 Vlookup
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21011029/
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
Vlookup in Excel Macro
提问by user3175856
I have created a form in excel which takes input from users and saves the same in Sheet2. I have a combobox named Account from which users will select the name of account. However each account will have a unique Id associated with it. I dont want to expose this Id to the end users but at the same time I want the Id corresponding to the Account selected populated in sheet2.
我在 excel 中创建了一个表单，它接受用户的输入并将其保存在 Sheet2 中。我有一个名为 Account 的组合框，用户可以从中选择帐户名称。但是，每个帐户都将有一个唯一的 ID 与之关联。我不想将此 Id 公开给最终用户，但同时我希望与在 sheet2 中填充的所选帐户相对应的 Id。
I am trying to do a Vlookup to search for Id of the selected Account.
我正在尝试执行 Vlookup 以搜索所选帐户的 ID。
I have setup the Account values in Sheet3.
我已经在 Sheet3 中设置了帐户值。
Example: Column A ColumnB Account1 Id12345 Account2 Id23456 Account3 Id34567
My code in excel macro looks something like below.
我在 excel 宏中的代码如下所示。
Private Sub Save_Click()
Dim RowCount As Long
Dim myValue as String
RowCount = Worksheets("Sheet2").Range("A1").CurrentRegion.Rows.Count
With Worksheets("Sheet2").Range("A1")
.Offset(RowCount, 0).Value = Me.Account.Value
myValue = WorksheetFunction.VLookup(Range("A2"), Range("Sheet3!G1:G14"), 2, False)
I am not sure why it is not working. Any suggestion will be greatly appreciated.
我不确定为什么它不起作用。任何建议将不胜感激。
Thanks,
谢谢，
采纳答案by Jerome Montino
Try this:
尝试这个：
Private Sub Save_Click()
Dim RowCount As Long
Dim myValue As Variant
Dim Sh2 As Worksheet, Sh2 As Worksheet
Dim RefRange As Range
With ThisWorkbook
Set Sh2 = .Sheets("Sheet2")
Set Sh3 = .Sheets("Sheet3")
End With
RowCount = Sh2.Range("A1").CurrentRegion.Rows.Count
Set RefRange = Sh3.Range("G1:H14") '--Change as necessary.
Sh2.Range("A1").Offset(RowCount, 0).Value = Me.Account.Value
On Error GoTo myValueErrHandle
myValue = WorksheetFunction.VLookup(Sh2.Range("A2").Value, RefRange, 2, False)
'--More code, etc etc.
myValueErrHandle:
If Err.Number = 1004 Then
MsgBox "No match using VLOOKUP."
End If
Exit Sub
End Sub
As much as possible, qualify everything you can and use succinct but meaningful code. Don't use Range("A2")if you want what's inside it. Use .Valueas this is the best practice. As far as I can see, there's nothing wrong. I also added an Error 1004handling for no matches. Lastly, note that I changed myValueto a Variant, just to cover bases.
尽可能地限定一切，并使用简洁但有意义的代码。Range("A2")如果你想要里面的东西，不要使用。使用，.Value因为这是最佳实践。就我所见，没有任何问题。我还添加了Error 1004不匹配的处理。最后，请注意我改为myValuea Variant，只是为了覆盖基地。
Let us know if this works.
让我们知道这是否有效。
回答by L42
try this? : Sorry too long to put in comment:
尝试这个？: 抱歉太久不能发表评论：
With Worksheets("Sheet2")
.Range("A1").Offset(RowCount, 0).Value = Me.Account.Value
myValue = WorksheetFunction.VLookup(.Range("A2"), Sheeet3.Range("G1:H14"), 2, False)"
End With
Just maybe, so try it out.
只是也许，所以尝试一下。
回答by Bibhash Choudhury
Sub Lookup()
Dim Emp As String
Dim Lookup_Range As Range
Dim Age As Single
Emp = "Jitu"
Set Lookup_Range = Range("A1:B8")
Age = Application.WorksheetFunction.VLookup(Emp, Lookup_Range, 2, False)
MsgBox "Age is : $ " & Age
End Sub
Table: Emp Agebibhash 22 rahul 22 abhisek 23 jitu 21 sujit 24 tinku 25 rudra 26
表： Emp Agebibhash 22 rahul 22 abhisek 23 jitu 21 sujit 24 tinku 25 rudra 26
