The problem is when the reference to Cells in the line

Set TargetRange = Worksheets("NL-opens").Range(Cells(ContactStartRow,
ContactCol),Cells(ContactLastRow, ContactCol))


Here, the Cells reference does not point to cells on worksheet
"NL-opens". Instead, because it is not qualified with a specific
worksheet, it points to the ActiveSheet. You can use a With statement
to refer to worksheet "NL_Opens". For example


With Worksheets("NL_opens")
Set TargetRange = .Range( _
.Cells(ContactStartRow, ContactCol), _
.Cells(ContactLastRow, ContactCol))
End With

Note the period before the words "Range" and both "Cells". The period
tells the code that the property prefixed with the period refers to
the object named in the With statement. This code is equivalent to

Set TargetRange = Worksheets("NL_opens").Range( _
Worksheets("NL_opens").Cells(ContactStartRow, ContactCol), _
Worksheets("NL_opens").Cells(ContactLastRow, ContactCol))

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)



On Thu, 16 Oct 2008 06:38:01 -0700, Kermitp
wrote:

Per,

I appreciate your response but I get an error message of run-time error
‘1004’ on the Set TargetRange statement.

I don’t see anything wrong as far as variable names or misspelling

I tried to simplify the example to remove confusion on my part. Looks like
it comes down to the format of the Set TargetRange statement.

Any suggestions will be greatly appreciated

Sub testrange()

Dim TargetRange As Range

ContactCol = 2
ContactStartRow = 2
ContactRow = 2
ContactLastRow = 300


UserName = Worksheets("NL-opens-by-NL").Cells(ContactRow, "A").Value
Set TargetRange = Worksheets("NL-opens").Range(Cells(ContactStartRow,
ContactCol),Cells(ContactLastRow, ContactCol))

Set c = TargetRange.Find(UserName, LookIn:=xlValues)

End Sub