Extract data from another workbook using VLOOKUP

Question

I am busy creating my own function in VBA to extract data from another workbook in the same directory. However, when I call it as a function from within the destination workbook (Workbook A), it gives me a VALUE!# error. When I call the function from within a sub procedure where I test my code, it successfully extracts the correct data.

Within Excel I call my function like this =CALCULATE_METERS("D1"), and to test it using the sub procedure I call it like this from within the same module:

Sub test()
  CALCULATE_METERS ("D1")
  CALCULATE_METERS ("D1,2")
  CALCULATE_METERS ("D1-3")
  CALCULATE_METERS ("D1-5,8,9")
  CALCULATE_METERS ("D1(2),4(2),23(1),24-28")
  CALCULATE_METERS ("D1,4(2),23-25,26-28(2);E1-4;A1-2")
End Sub

The function containing the VLOOKUP code is (this is called within the CALCULATE_METERS function):

Private Function GetSquareMeters(block As String, r As Integer) As Integer
  Dim result As String
  Dim meters As String
  Dim wrkb As Workbook
  Dim wrks As Worksheet
  Dim rng As Range
  Dim directory As String
  Dim filename As String
  filename = "DATA REFERENCE.xlsx"
  directory = ActiveWorkbook.Path
  filename = directory & "\" & filename
  Debug.Print (filename)

  Set wrkb = Workbooks.Open(filename)

  meters = Application.WorksheetFunction.VLookup(block, wrkb.Worksheets("Lande").Range("$A$2:$F$120"), 5)

  GetSquareMeters = CInt(meters) * r / 7
End Function

Everything prints in the immediate window up until the filename in the above function, but I believe the value error is generated on the lookup function. Why is it that calling the same function with the same arguments (one within excel, the other one within a sub procedure) gives different answers? How can I correct this so that I can use the function within an Excel cell?

Thanks!

Do you really want to open the same workbook over and over and over again ??? — Gary's Student
@Gary'sStudent sorry if it is a noob question, I am very new to VBA and excel in general. I would ideally not like to open the workbook at all, I just need to find and extract the data. I have really been struggling to find a solution to this problem so this is the code I have at the moment. A solution similar to the simple VLOOKUP function inside a worksheet cell would be ideal, but I need to be able to parse the string before doing a lookup since a cell could contain many values. Thats why I moved it to VBA. — Loupi

Konrad Konrad · Accepted Answer · 2020-05-31T13:10:39

Try to use Find isntead of VLookup:

Function GetSquareMeters(block As String, r As Integer) As Integer
  Dim result As String
  Dim meters As String
  Dim wrkb As Workbook
  Dim wrks As Worksheet
  Dim rng As Range
  Dim directory As String
  Dim filename As String
  filename = "DATA REFERENCE.xlsx"
  directory = ActiveWorkbook.Path
  filename = directory & "\" & filename
  Debug.Print (filename)

 For Each wb In Workbooks
    If wb.FullName = Filename Then
    Set wrkb = Workbooks(Filename)
    Else
    Set wrkb = Workbooks.Open(Filename)
    End If
 Next

    meters = wrkb.Sheets("Lande").Range("$A$2:$F$120").Find(block).Offset(0, 4).Value

  GetSquareMeters = CInt(meters) * r / 7
End Function

Extract data from another workbook using VLOOKUP

1 Answers