problem using Set statement to define range object
 

In the subroutine below I set a range variable SourceRng
= to a column in a named worksheet range. When I try to
use a for next to step through the cells in SourceRng I
get a type mismatch when comparing cel.value to the value
of a single cell. Using the immediate window I found The
cel.address to be a range (i.e. a1..a10) and not a cell
address (i.e. A1). I found a work around by selecting the
first column of the range and then using the set command
Set Sourcerng = selection, but why does the other not
work.


Sub getEmplDescrTest()
Dim SourceRng As Range, DestRng As Range, Cel As Range,
Mycel As Range
Set SourceRng = ActiveWorkbook.Worksheets("Fy2004").Range
("FY2004_personnel_list").Columns(3)
Set DestRng = ActiveWorkbook.Worksheets
("BossFy2004").Range("FY2004Boss_personnel_list"). Columns
(3)
'Set DestRng = ActiveWorkbook.Worksheets
("BossFy2004").Range("FY2004Boss_personnel_list ")
For Each Cel In DestRng ' just pick the name column of
the range
For Each Mycel In SourceRng
If Cel.Value = Mycel.Value Then
Cel.Offset(0, 2).Value = Mycel.Offset(0, -1).Value
End If
Next Mycel
Next Cel
'clear objects
Set SourceRng = Nothing
Set DestRng = Nothing
End Sub