VLookup to a different workbook

[Jimbo213]

I got this code from another thread but am having trouble modifying it.
The code is in module1 on worksheet.xlsm but I want to look up something on
an ALREADY OPEN workbook CreateManifestEmail_V1.xlsm

I get "Subscript out of range" at <-- ERROR shown below in code.

Your help is appreciated in advance.

~~~~~~~~~~~~

Function LookupEmailNames (sServer As String) As String
' looks at CreateManifestEmail_V1.xlsm (eMail Names) for sServer in Col A
' returns TO names from Col B

Dim OFFICE As Workbook
Dim Result As String
Dim EmailRange As Variant 'it can return a blank

Set OFFICE = Workbooks("CreateManifestEmail_v1.xlsm") <- ERROR
With OFFICE.Worksheets("eMail Names")
Set EmailRange = .Range("A1:B1000")
End With

Result = Application.VLookup(sServer, EmailRange, 2, False)

If IsError(Result) Then
MsgBox "Not found"
Else
MsgBox Result, vbOKOnly
End If

End Sub

 

[arjen van...]

I tried to work through a similar example and got it to work as follows:

Dim EMailRange As Range
Set EMailRange =
Workbooks("Book3.xls").Worksheets("Sheet1").Range("A1:B1000")

Dim result As String
result = WorksheetFunction.VLookup(sServer, EMailRange, 2, False)

Just plug in your workbook & worksheet names & it should work.

 

[Jimbo213]

Wow that worked GREAT. Thanks.

How do I trap the error if VLookup does not find a match.
I want to set result = ""

Thanks for your reply & assistance.
Jimbo213

 

[arjen van...]

To handle a possible error and set the result = "" you just need to add a few
lines as follows:

On Error Resume Next
result = WorksheetFunction.VLookup(sServer, EMailRange, 2, False)

If Err.Number <> 0 Then
result = ""
End If