Why does this VBA range reference fail?

mrblister

Board Regular
Joined
Nov 20, 2016
Messages
177
Office Version
  1. 2019
Platform
  1. Windows
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
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,833
Office Version
  1. 2010
Platform
  1. Windows
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:

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

And the first would be better written as

Code:
Set rng = Range(Range("J1").Value)
 
Last edited:

Perpa

Well-known Member
Joined
Dec 18, 2012
Messages
635
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:
Code:
Set rng = Range(Range("K1"), Range("L1"))
To this:
Code:
Set rng = Range(Range("K1") & ":" & Range("L1"))

Perpa
 
Last edited:

mrblister

Board Regular
Joined
Nov 20, 2016
Messages
177
Office Version
  1. 2019
Platform
  1. Windows
OK great! Thank you for the replies.
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,833
Office Version
  1. 2010
Platform
  1. Windows
You're welcome.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,424
Messages
5,837,155
Members
430,479
Latest member
mamush200

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top