CODEWITHSUNDEEP
excelvbavlookup
swoootie
swoootie

Reputation: 1

How do I use Vlookup using multiple workbooks when using VBA

I am attempting to use VBA to pick up 2 different values from 2 worksheets. My code at present is as follows:

Sub New()

Dim RANGEREF1 As String
Dim RANGEREF2 As String
Dim DATEREF As String

DATEREF ="202217"

workbooks.open("X:\test.xlsx")

RANGEREF1 = Application.WorksheetFunction.VLookup(DATEREF, Workbooks("test.xlsx").Sheets("Sheet1").Range("A1:C999"), 2, 0)

End Sub

This returns: runtime error 1004 method 'range' of object '_Global' failed

Can anyone see what I'm doing wrong?

Thanks in advance.

Upvotes: 0

Views: 79

Answers (1)

Tim Williams
Tim Williams

Reputation: 166366

Here's an example of using Vlookup in VBA:

Sub New()

    Dim RANGEREF1 As String, wb As Workbook
    Dim RANGEREF2 As String
    Dim DATEREF As String
    
    DATEREF ="202217"
    
    Set wb = Workbooks.open("X:\test.xlsx") 'get a reference to the opened workbook
    
    'If you drop the `WorksheetFunction` you will not get a 
    '   runtime error if no match is found
    RANGEREF1 = Application.VLookup(DATEREF, wb.Sheets("Sheet1").Range("A1:C999"), 2, False)
    If Not IsError(RANGEREF1) Then
        'got a match - use RANGEREF1
    Else
        'no match found
    End If

End Sub

Upvotes: 1

Related Questions