What am I doing wrong with this VBA range?
 

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.

What am I doing wrong with this VBA range?
 

try

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"jbarrington" <"jbarrington -at- comcast period net" wrote in message
...
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.

What am I doing wrong with this VBA range?
 

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)?

--
Rick (MVP - Excel)


"jbarrington" <"jbarrington -at- comcast period net" wrote in message
...
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.