不同工作表中的 VBA vlookup 参考

VBA vlookup reference in different sheet

提问by CloseISQ

In Excel 2007, I am looping through the values of column 4 in Sheet 2. Still in Sheet 2, I want to output the result of my vlookup formula into column 5. The vlookup formula needs to refer to Sheet 1 where the reference columns are. In order to do so I have the following formula

在 Excel 2007 中，我循环遍历 Sheet 2 中第 4 列的值。仍然在 Sheet 2 中，我想将我的 vlookup 公式的结果输出到第 5 列中。 vlookup 公式需要引用 Sheet 1 中的参考列. 为了做到这一点，我有以下公式

Range("E2") = Application.WorksheetFunction.VLookup(Range("D2"), _
          Worksheets("Sheet1").Range("A1:C65536"), 1, False)

Problem, it returns error code 1004. I read that it was because I needed to Select Sheet 1 before running the formulas such as:

问题，它返回错误代码 1004。我读到这是因为我需要在运行公式之前选择工作表 1，例如：

ThisWorkbook.Worksheets("Sheet1").Select

But then the searched value Range ("D2") doesn't belong to Sheet 1 and it still return code 1004 after having brought Sheet 1 into view.

但是随后搜索到的值 Range ("D2") 不属于 Sheet 1，并且在将 Sheet 1 置于视图中后它仍然返回代码 1004。

What is the correct way to refer to a different sheet in this case?

在这种情况下，引用不同工作表的正确方法是什么？

采纳答案by CloseISQ

It's been many functions, macros and objects since I posted this question. The way I handled it, which is mentioned in one of the answers here, is by creating a string function that handles the errors that get generate by the vlookup function, and returns either nothing or the vlookup result if any.

自从我发布这个问题以来，已经有很多函数、宏和对象。我处理它的方式（此处的一个答案中提到）是通过创建一个字符串函数来处理由 vlookup 函数生成的错误，并且不返回任何内容或返回 vlookup 结果（如果有）。

Function fsVlookup(ByVal pSearch As Range, ByVal pMatrix As Range, ByVal pMatColNum As Integer) As String
    Dim s As String
    On Error Resume Next
    s = Application.WorksheetFunction.VLookup(pSearch, pMatrix, pMatColNum, False)
    If IsError(s) Then
        fsVlookup = ""
    Else
        fsVlookup = s
    End If
End Function

One could argue about the position of the error handling or by shortening this code, but it works in all cases for me, and as they say, "if it ain't broke, don't try and fix it".

人们可以争论错误处理的位置或通过缩短这段代码，但它对我来说适用于所有情况，正如他们所说，“如果它没有坏，不要尝试修复它”。

回答by L42

try this:

尝试这个：

Dim ws as Worksheet

Set ws = Thisworkbook.Sheets("Sheet2")

With ws
    .Range("E2").Formula = "=VLOOKUP(D2,Sheet1!$A:$C,1,0)"
End With

End Sub

This just the simplified version of what you want.
No need to use Applicationif you will just output the answer in the Range("E2").

这只是您想要的简化版本。
没有必要使用Application，如果你只输出了答案Range("E2")。

If you want to stick with your logic, declare the variables.
See below for example.

如果你想坚持你的逻辑，声明变量。
例如，请参见下文。

Sub Test()

Dim rng As Range
Dim ws1, ws2 As Worksheet
Dim MyStringVar1 As String

Set ws1 = ThisWorkbook.Sheets("Sheet1")
Set ws2 = ThisWorkbook.Sheets("Sheet2")
Set rng = ws2.Range("D2")

With ws2
    On Error Resume Next 'add this because if value is not found, vlookup fails, you get 1004
    MyStringVar1 = Application.WorksheetFunction.VLookup(rng, ws1.Range("A1:C65536").Value, 1, False)
    On Error GoTo 0
    If MyStringVar1 = "" Then MsgBox "Item not found" Else MsgBox MyStringVar1
End With

End Sub

Hope this get's you started.

希望这让你开始。

回答by chris neilsen

Your code work fine, provided the value in Sheet2!D2exists in Sheet1!A:A. If it does not then error 1004 is raised.

您的代码工作正常，前提是 中的值Sheet2!D2存在于Sheet1!A:A. 如果没有，则会引发错误 1004。

To handle this case, try

要处理这种情况，请尝试

Sub Demo()
    Dim MyStringVar1 As Variant
    On Error Resume Next
    MyStringVar1 = Application.WorksheetFunction.VLookup(Range("D2"), _
      Worksheets("Sheet1").Range("A:C"), 1, False)
    On Error GoTo 0
    If IsEmpty(MyStringVar1) Then
        MsgBox "Value not found!"
    End If

    Range("E2") = MyStringVar1

End Sub

回答by sancho.s ReinstateMonicaCellio

The answer your question: the correct way to refer to a different sheet is by appropriately qualifying each Rangeyou use. Please read this explanationand its conclusion, which I guess will give essential information.

回答您的问题：引用不同工作表的正确方法是适当地限定Range您使用的每个工作表。请阅读此解释及其结论，我想这将提供重要信息。

The error you are getting is likely due to the sought-for value Sheet2!D2not being found in the searched range Sheet1!A1:A65536. This may stem from two cases:

您得到的错误很可能是由于Sheet2!D2在搜索范围中找不到所寻求的值Sheet1!A1:A65536。这可能源于两种情况：

1. The value is actually not present (pointed out by chris nielsen).

2. You are searching the wrong Range. If the ActiveSheetis Sheet1, then using Range("D2")without qualifying it will be searching for Sheet1!D2, and it will throw the same error even if the sought-for value is present in the correct Range. Code accounting for this (and items below) follows:

   Sub srch()
       Dim ws1 As Worksheet, ws2 As Worksheet
       Dim srchres As Variant
   
       Set ws1 = Worksheets("Sheet1")
       Set ws2 = Worksheets("Sheet2")
   
       On Error Resume Next
       srchres = Application.WorksheetFunction.VLookup(ws2.Range("D2"), ws1.Range("A1:C65536"), 1, False)
       On Error GoTo 0
       If (IsEmpty(srchres)) Then
         ws2.Range("E2").Formula = CVErr(xlErrNA) ' Use whatever you want
       Else
         ws2.Range("E2").Value = srchres
       End If
   End Sub
   

1. 该值实际上不存在（由 chris nielsen 指出）。

2. 您正在搜索错误的范围。如果ActiveSheet是Sheet1，Range("D2")则不加限定地使用它将搜索Sheet1!D2，并且即使寻找的值存在于正确的范围中，它也会抛出相同的错误。对此（以及以下项目）的代码说明如下：

   Sub srch()
       Dim ws1 As Worksheet, ws2 As Worksheet
       Dim srchres As Variant
   
       Set ws1 = Worksheets("Sheet1")
       Set ws2 = Worksheets("Sheet2")
   
       On Error Resume Next
       srchres = Application.WorksheetFunction.VLookup(ws2.Range("D2"), ws1.Range("A1:C65536"), 1, False)
       On Error GoTo 0
       If (IsEmpty(srchres)) Then
         ws2.Range("E2").Formula = CVErr(xlErrNA) ' Use whatever you want
       Else
         ws2.Range("E2").Value = srchres
       End If
   End Sub
   

I will point out a few additional notable points:

我将指出一些额外的值得注意的点：

1. Catching the error as done by chris nielsen is a good practice, probably mandatory if using Application.WorksheetFunction.VLookup(although it will not suitably handle case 2 above).

2. This catching is actually performed by the function VLOOKUPas entered in a cell (and, if the sought-for value is not found, the result of the error is presented as #N/Ain the result). That is why the first soluton by L42 does not need any extra error handling (it is taken care by =VLOOKUP...).

3. Using =VLOOKUP...is fundamentally different from Application.WorksheetFunction.VLookup: the first leaves a formula, whose result may change if the cells referenced change; the second writes a fixed value.

4. Both solutions by L42 qualify Ranges suitably.

5. You are searching the first column of the range, and returning the value in that same column. Other functions are available for that (although yours works fine).

1. 像 chris nielsen 所做的那样捕获错误是一个很好的做法，如果使用可能是强制性的Application.WorksheetFunction.VLookup（尽管它不能适当地处理上面的情况 2）。

2. 这种捕获实际上是由VLOOKUP在单元格中输入的函数执行的（并且，如果未找到所寻求的值，则错误#N/A的结果将如结果中所示）。这就是为什么 L42 的第一个解决方案不需要任何额外的错误处理（由 处理=VLOOKUP...）。

3. Using=VLOOKUP...与 根本不同Application.WorksheetFunction.VLookup：第一个留下公式，如果引用的单元格改变，其结果可能会改变；第二个写入一个固定值。

4. L42 的两种解决方案都适用于范围。

5. 您正在搜索范围的第一列，并返回同一列中的值。其他功能可用（虽然你的工作正常）。