Named Range, Strange VBA thingy too

Engineer Joe

Well-known Member
Joined
Jun 10, 2005
Messages
543
I have two Problems:

1. In my code, I search for ranges containing identifying text and label them (BodyStart and BodyEnd) like this.
Code:
Set BodyStart = Cells.Find(what:="General", searchorder:=xlByRows, searchformat:=False)

Then I try to name all of the cells between BodyStart and BodyEnd. I've selected the name BodyEntire. Like this...

Code:
Set BodyEntire = range(range("BodyStart"), range("BodyEnd"))

But with the above line of code, i always get the dreaded error 1004 - Method 'range' of object '_global' failed.

Am I naming the range incorrectly?

2. Also, shouldn't the " r " in range be automatically capitalized for all of this crap? I don't know why " r " is not capitalized, but that isn't disturbing the program, because it works everywhere else with the lower case...
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,343
Office Version
  1. 365
Platform
  1. Windows
Joe

The reason for the error is because you are trying to use BodyStart as a named range. It isn't a named range, it's a range object.

Does this work?
Code:
Set BodyEntire = Range(BodyStart, BodyEnd)
 

Engineer Joe

Well-known Member
Joined
Jun 10, 2005
Messages
543
Thanks Norie...yeah, I shoulda seen it. It's just been a good long time since i've screwed with VBA. Got any ideas about why is giving me the lower case "r" in range???
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,343
Office Version
  1. 365
Platform
  1. Windows
Joe

Do you have a variable called range?
 

Engineer Joe

Well-known Member
Joined
Jun 10, 2005
Messages
543

ADVERTISEMENT

Actually, that's the first thing that I looked for...and I don't have one called "range". If i did, wouldn't it really screw up every code in every module for which the variable was operable???

Could it have something to do with my plugins? I loaded some utilities into my vba editor from this sight. That wouldn't do it would it?
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,343
Office Version
  1. 365
Platform
  1. Windows
The only way I was able to simulate your problem was by declaring a variable called range.

Where is this happening? Is it every instance of range?
 

Engineer Joe

Well-known Member
Joined
Jun 10, 2005
Messages
543
Norie,

It appears that it happens for every occurence of "range" for every module in My Personal Workbook. However, upon looking at other excel workbooks that have their own isolated modules, I found that Range was capitalized. The plot thickens.
 
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,163,812
Messages
5,833,803
Members
430,233
Latest member
cbutts

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