使用VLookup时更改工作表会导致问题 [英] Changing worksheet while using VLookup causes problems

问题描述

我想从两个不同的工作表中总共导入两个值.我有工作表Site1和Site2.从这些中，我想分别导入与行(Product1,Cost)和(Product2,Cost)相对应的值.

I want to import two values in total from two different worksheets. I have the worksheets Site1 and Site2. From these I want to import the values corresponding to the rows (Product1,Cost) and (Product2,Cost) respectively.

为此，我尝试了:

  Set currentWb = ActiveWorkbook
  Set openWb = Workbooks.Open(filepath & Path) ' It imports the correct file
  Set openWs = openWb.Sheets("Site1")
  currentWb.Sheets("Sheet1").Cells(2,3).Value = Application.WorksheetFunction.VLookup("Product1", openWs.Range("A1:R30"), Application.Match("Cost", openWs.Range("A1:R1")))
  Set openWs = openWb.Sheets("Site2")
  currentWb.Sheets("Sheet1").Cells(3,3).Value = Application.WorksheetFunction.VLookup("Product2", openWs.Range("A1:R30"), Application.Match("Cost", openWs.Range("A1:R1")))

但是，这使我得到错误:Error code '13'.: Incompatible types在运行代码的第二行(请参阅工作表Site2).当我尝试调试此错误时，似乎在尝试调用工作表Site2时发生了错误.

This however gets me the error: Error code '13'.: Incompatible types at the second row where I run the code (referring to the worksheet Site2). When I have tried to debug this error, it seems that the error occurs when I try to call the worksheet Site2.

我已经查看了原始文件中的数据类型，在两种情况下都是数字.数据类型在我的"ActiveWorkbook"中也相同.我尝试使用新的Set openws2 = openWb.Sheets("Site2")并使代码适应该问题，但是仍然会导致相同的错误.

I have looked at the data types at the original file, and it is a number in both cases. The datatype is the same in my "ActiveWorkbook" as well. I've tried using a new Set openws2 = openWb.Sheets("Site2") and adapting the code to that, but it still caused the same error.

尝试在工作表Site2上使用VLookup时，我在做什么错?

What am I doing wrong when trying to use VLookup at worksheet Site2?

进行编辑以反映使VLookup跨多列的更改+添加"WorksheetFunction"作为VLookup的前缀.

Edited to reflect Changes to make VLookup span multiple columns + adding "WorksheetFunction" as a prefix to VLookup.

Edit2:我现在已经在代码行之间运行(使用F8).在行Set openWs = openWb.Sheets("Site2")之前和之后添加MsgBox Application.Match("Cost", openWs.Range("A1:R1"))返回了工作表"Site1"的正确列索引，但是在将openWs更改为Site2之后，根本没有返回任何内容.这是否意味着不可能以我已经完成的操作方式执行命令Set openWs = openWb.Sheets("Site2)，即程序会不会仅将其识别为打开一张新纸?

I have now ran my code line to line (using F8). Adding MsgBox Application.Match("Cost", openWs.Range("A1:R1")) before and after the line Set openWs = openWb.Sheets("Site2") returned the correct column index for worksheet "Site1", but did not return anything at all after I change openWs to Site2. Does this mean that it is not possible to conduct the command Set openWs = openWb.Sheets("Site2) in the mannor I have done - i.e. will the program not recognize this as merely opening up a new sheet?

Edit3:基本上每个工作表都由一个数组组成，其中A1为空，而A2:A30由产品名的行标题-Product1，Product2，....，Product30组成.范围A1:R1由数量"，成本"等列标题组成.我想将Application.Match与VLookup结合使用，以防万一其他用户插入新列时避免不匹配"列.因此，我使用VLookup，首先找到"product1"所在的行，然后使用Application.Match，我可以找到"Cost"所在的列.与(行，列)索引(Product1，Cost)相对应的值.当我要使用新的工作表"Site2"时会出现问题.我认为只需修改代码以捕获此工作表中的"Product2，Cost"即可.取而代之的是我得到了一个错误，我已经尝试调试，并且如果我理解它是正确的，则源于该程序没有将Set openWs = openWb.Sheets("Site2")转换为我想要实现的功能，只是一个新的工作表来执行相同类型的错误. Vlookup公式已启用.

Basically each Worksheets consists of an Array where A1 is empty, and A2:A30 consists of row headings of productnames - Product1, Product2, ...., Product30. The range A1:R1 consists of column headers of Quantity, Costs, etc. I want to use Application.Match in combination of VLookup as a way of avoiding "mismatching" of columns in case some other user inserts a, say, new column. I therefore use VLookup, first to find the row for which "product1" is located in, and then using Application.Match I can find which column "Cost" is located in. Through this I ought to find, and I also do find, the value corresponding to the (row,column) index (Product1,Cost). The problem arises when I want to use the new worksheet, "Site2". Simply adapting the code to capture "Product2,Cost" in this worksheet should work (I think). Instead I get an error, which I have tried to debug and if I understand it correct it stems from the fact that the program doesn't translate Set openWs = openWb.Sheets("Site2") into what I wanted to achieve, simply a new worksheet to conduct the same type of Vlookup formula on.

由于问题可能是由特殊字符å引起的，因此当我尝试从工作表中导入数据时，我将其称为Site2(实际上是不同的，其中包括å-从现在开始，我们将其称为Site2å(以便尝试使用通配符)，我尝试了一些不同的方法.

Due to the fact that the problems may been caused by a specialcharacter å when I try to import the data from the sheet I call Site2 (which in reality is different, and includes an å - let's henceforth call it Site2å in order to try to use wildcards), I've tried some different approaches.

都不使用

 set openWs = openWb.Sheets(3) '3 is the index of `Site2å`

或

 For Each openWs In openWb.Worksheets
      If LCase(openWs.Name) Like "Site2?" Then
              openWs.Select
              Exit For
      End If
 Next openWs

有效.后者给出错误Object or With variable not set.我已将openWs定义为Dim openWs as Worksheet

works. The latter gives the error Object or With variable not set. I have defined openWs as Dim openWs as Worksheet

推荐答案

我终于找到了解决此问题的方法.我将其发布为答案，因为我一直认为VLookup或Application.Match与工作表中的开关一起导致了问题，在对这个问题进行了搜索之后，发现其他一些代码作者也相信这一点.

I have finally found a solution for this problem. I post this as an answer as I've been under the impression that VLookup or Application.Match along with the switch in worksheets caused the problems, and after googling this issue found out that some other code authors has believed the same.

对我来说，解决方案是仅在更改工作表之前关闭使用的工作簿.在我的代码中，我对代码进行了一些小的调整.

For me the solution was simply to Close the workbook used before changing the worksheet. In my code I have added the minor adjustments to the code.

  Set currentWb = ActiveWorkbook
  Set openWb = Workbooks.Open(filepath & Path) ' It imports the correct file
  Set openWs = openWb.Sheets("Site1")
  currentWb.Sheets("Sheet1").Cells(2,3).Value = Application.WorksheetFunction.VLookup("Product1", openWs.Range("A1:R30"), Application.Match("Cost", openWs.Range("A1:R1")))
  openWb.Close saveChanges:=False    ' Added this line
  Set openWs = openWb.Sheets("Site2")
  currentWb.Sheets("Sheet1").Cells(3,3).Value = Application.WorksheetFunction.VLookup("Product2", openWs.Range("A1:R30"), Application.Match("Cost", openWs.Range("A1:R1")))
  openWb.Cloe saveChanges:=False     'And this one 

希望这对其他任何人都可以有帮助！我肯定必须花很多时间才能意识到自己找错了地方.

Hopefully this can be of any help to anyone else! I sure had to google quite some time before realizing I was looking in the wrong place.

(我还要感谢@PaaquaGrant在这个主题上投入了很多时间，并成为一个很好的交谈对象).

(And I'd like to thank @PaaquaGrant for investing so much time in this topic, and being a very nice person to talk to).

这篇关于使用VLookup时更改工作表会导致问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！