Why does this VBA range reference fail?

mrblister

Board Regular
Joined
Nov 20, 2016
Messages
162
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
 

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,779
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
634
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
162
Office Version
  1. 2019
Platform
  1. Windows
OK great! Thank you for the replies.
 

shg

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

Watch MrExcel Video

Forum statistics

Threads
1,122,491
Messages
5,596,466
Members
414,069
Latest member
StudExcel

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