VBA vlookup参考在不同的表 [英] VBA vlookup reference in different sheet

问题描述

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

  Range（E2）= Application.WorksheetFunction.VLookup（Range（ D2），_ 
工作表（Sheet1）。范围（A1：C65536），1，False）
  

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

  ThisWorkbook.Worksheets（Sheet1）。选择
  

但是搜索到的值Range（D2）不属于Sheet 1，并且在将Sheet 1带入视图后仍然返回代码1004。

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

解决方案

自从我发布了这个问题以来，它已经有很多功能，宏和对象。我在处理这个问题的方法之一是通过创建一个字符串函数来处理由vlookup函数生成的错误，并返回一个或者没有vlookup的结果。

 函数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）
如果IsError（s）然后
 fsVlookup =
 Else 
 fsVlookup = s 
结束如果
结束函数
  

人们可以争论错误处理的位置或缩短这段代码，但是在所有情况下都适用于我，正如他们所说的那样：如果没有破坏，不要尝试修复它。 >

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

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:

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.

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

解决方案

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.

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".

这篇关于VBA vlookup参考在不同的表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！