CODEWITHSUNDEEP
vbaexcel
Cenderze
Cenderze

Reputation: 1222

Changing worksheet while using VLookup causes problems

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.

For this I've tried:

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

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.

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.

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

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

Edit2: 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: 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.

EDIT4: 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.

Neither using

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

or

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

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

Upvotes: 1

Views: 84

Answers (2)

Cenderze
Cenderze

Reputation: 1222

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.

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

Upvotes: 0

Paaqua Grant
Paaqua Grant

Reputation: 160

Your VLOOKUP range is a single Column, instead of a multi-column range. This will always fail.

As suggested in some of the comments, you also need to adjust all instances of current.wb to currentwb.

Upvotes: 1

Related Questions