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...
 

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,059
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,059
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,059
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,540
Messages
5,572,794
Members
412,483
Latest member
deezina07
Top