What am I doing wrong with this VBA range?

[jbarrington]

This works:

Dim myRange As Range

Set myRange = Worksheets("Collection").Range("H2:H4")

But the change below doesn't work for some reason and presents an error:

Dim refColumn As String
Dim myRange As Range

refColumn = "H"

Set myRange = Worksheets("Collection").Range(refColumn & "2:" &
refColumn & "4")


What have I done wrong?

Thanks in advance.

 

[Don Guillett]

try

.range(cells(2,refcolumn),cells(4,refcolumn))

 

[Rick Rothstein]

That Set statement works fine for me (as it should) on my copy of XL2003.
Just out of curiosity, what error message are you getting? Are you sure the
Set statement is where your error is occurring at (that is, did you single
step through your code to see where the error pops up at)?