vba 如何将值从 VLOOKUP 返回到单元格中？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19208085/
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 return value from VLOOKUP into a cell?
提问by SAY
I have the following piece of code for Vlookup. The function works fine but the found out value aint getting displayed in the cell. However if i had a used Msgbox function the found out value is shown. The question is doesnt VLOOKUP result be captured in a cell?
我有以下一段用于 Vlookup 的代码。该函数工作正常，但发现的值并未显示在单元格中。但是，如果我有一个使用过的 Msgbox 函数，则会显示找到的值。问题是不是在单元格中捕获 VLOOKUP 结果？
Sub Example_of_Vlookup()
Dim lookFor As Range
Dim rng As Range
Dim col As Integer
Dim found As String
Dim lastrowrange As Long
Dim area As Range
lastrowrange = [A65536].End(xlUp).Row
Set lookFor = Sheets("Sheet2").Range("b2")
Set rng = Sheets("Sheet2").Columns("t:u")
Set taxRange = Range("f2", Cells(lastrowrange, 22))
col = 2
On Error Resume Next
For i = 1 To lastrowrange
found = Application.VLookup("B2", "T1:U4", 2, True)
If IsError(found) Then
MsgBox lookFor & " not found"
Else
area.Cells(i, 2).Value = found
End If
Next i
On Error GoTo 0
End Sub
回答by stobin
You did not set the range "area" equal to anything, so this line won't show your answer properly:
您没有将范围“区域”设置为等于任何值，因此此行将无法正确显示您的答案：
area.Cells(i, 2).Value = found
Change area.Cells(i,2).valueto sheets("Sheet2").Cells(i,2).valueor wherever you want your answer to show. Or, set area equal to something if you want to use area.cells.
切换area.Cells(i,2).value到sheets("Sheet2").Cells(i,2).value您希望答案显示的位置或任何位置。或者，如果您想使用area.cells.
回答by Siddharth Rout
Idea is simple - I have country names in Column B.Inention is to pull out the Area under which country belongs - My look up values are in column S(country) and T(area) and display the result in column F – Sayanth Sasidharan 25 mins ago
想法很简单 - 我在 B 列中有国家名称。意图是提取国家所属的区域 - 我的查找值在 S（国家）和 T（区域）列中，并在 F 列中显示结果 – Sayanth Sasidharan 25 分钟前
If my understanding is correct as per your explanation then you do not need to use a loop. Let Excel do the Dirty Work ;) You will end up with far less code.
如果根据您的解释我的理解是正确的，那么您不需要使用循环。让 Excel 来做脏活；) 你最终会得到更少的代码。
Let's say your sheet looks like this
假设你的工作表看起来像这样
Logic:
逻辑：
- Find the last row of Col B
- Insert the
Vlookupformula inF1:F & LastRowin one go - Convert them to values.
- 找到 Col B 的最后一行
- 一次性插入
Vlookup公式F1:F & LastRow - 将它们转换为值。
Code:
代码：
Option Explicit
Sub Example_of_Vlookup()
Dim ws As Worksheet
Dim lRow As Long
Set ws = ThisWorkbook.Sheets("Sheet2")
With ws
lRow = .Range("B" & .Rows.Count).End(xlUp).Row
'~~> =IF(ISERROR(VLOOKUP(B1,S:T,2,0)),"",VLOOKUP(B1,S:T,2,0))
.Range("F1:F" & lRow).Formula = _
"=IF(ISERROR(VLOOKUP(RC[-4],C[13]:C[14],2,0)),"""",VLOOKUP(RC[-4],C[13]:C[14],2,0))"
.Range("F1:F" & lRow).Value = .Range("F1:F" & lRow).Value
End With
End Sub
Result:
结果：
