Vlookup Error

[Marnie]

I am trying to captures the contents of a field in a worksheet based on the
value of a field in a different worksheet. This field will become the To
list in an email message but I am receiving an error in the Vlookup function:

Dim CCList As String
Dim tmplist As String
Dim LkRange As Range

Set LkRange = Worksheets("Contact List").Range("A2:F25")
If Sheets("Hold Reasons").Range("C28") = "X" Then
tmplist = Application.WorksheetFunction.VLookup(D6, LkRange, 2, False)
CCList = CCList + tmplist
End If

The above code generates the error "Run Time Error 1004; Unable to get the
Vlookup property of the Worksheet Function class" on the Vlookup line. When
stepping through this code LkRange has no value so the failure appears to be
in capturing the range. I tried putting the range directly into the Vlookup
function and that generated syntax errors.

Any help would be appreciated!
Marnie

 

[Don Guillett]

range("d6")
or use vba FIND instead along with OFFSET

 

[Marnie]

Range worked!

Thanks!

range("d6")
or use vba FIND instead along with OFFSET

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

 

[Dave Peterson]

You may still have a problem. If the value in D6 doesn't match anything in
A2:A25 of Contact list, you'll get the same error.

I'd use:

Dim CCList As String
Dim tmplist As Variant 'could return an error
Dim LkRange As Range

Set LkRange = Worksheets("Contact List").Range("A2:F25")
If Sheets("Hold Reasons").Range("C28") = "X" Then
'dropped the .worksheetfunction portion
tmplist = Application.VLookup(somesheet.range("D6"), LkRange, 2, False)
if iserror(tmplist) then
tmplist = 0
end if
CCList = CCList + tmplist
End If

I'd qualify where D6 is located, too.