Why does this VBA range reference fail?

[mrblister]

In J1 is a1:b10
In K1 is a1
In L1 is b10

OK, so this works: Set rng = Range(Range("j1"))
This does not: Set rng = Range(Range("K1"), Range("L1")). I get an error. Why won't this work? I get an error "type mismatch".


Not sure if it's important, here's the test program I was running:

Private Sub CommandButton1_Click()


Dim rng As Range, cell As Range
Set rng = Range(Range("j1"))


For Each cell In rng
cell.Value = cell.Value * 2
Next cell


End Sub

 

[shg]

In the first instance, Range(someRange) works because it uses the default property of a range, which is Value.

In the second case, it uses the specified ranges (not their values) as the corners, so you need to be explicit:

  Set rng = Range(Range("K1").Value, Range("L1").Value)

And the first would be better written as

Set rng = Range(Range("J1").Value)

 

[Perpa]

In J1 is a1:b10
In K1 is a1
In L1 is b10

OK, so this works: Set rng = Range(Range("j1"))
This does not: Set rng = Range(Range("K1"), Range("L1")). I get an error. Why won't this work?

Sorry shg...I did see your post before my own...please ignore
Cell J1 has the colon...your Set rng does not...
Change this:

Set rng = Range(Range("K1"), Range("L1"))

To this:

Set rng = Range(Range("K1") & ":" & Range("L1"))

Perpa

 

[mrblister]

OK great! Thank you for the replies.

 

[shg]

You're welcome.