Range worked!

Thanks!

"Don Guillett" wrote:

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Marnie" wrote in message
...
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