Problems with VBA macro for creating named ranges - ARRRG!
 

Okay, this is my first post to this group, so I will try to be as complete
and precise as I can. Here's the VBA code as it is written...

Sub Named_Range_Test()
'
' Named_Range_Test Macro
' Macro recorded 2/29/2004 by (name removed)
'
Dim RangeName As String
RangeName = ActiveSheet.Name
Range("D13").Activate
ActiveWorkbook.Names.Add Name:=RangeName, RefersToR1C1:= _
"='" & RangeName & "'!RC:R[956]C[6]"
Range("A14").Activate
End Sub

Okay, now. That macro creates a named range from cell D13 to J969 inclusive
and assigns the sheet's name to this range. No big deal right? Go ahead and
try it. It'll work just fine the first time.

Let's say you have a sheet named "Test1". You run this macro and, low and
behold, there's a named range called "Test1" in your drop-down list up in
the top left corner of the spreadsheet (works as it should). Well, that's
just fine and dandy... except... Click on cell D13 and then select the named
range from the drop-down. Looks fine, right? Great. Now select cell A1 and
choose the named range from the drop-down list again... see what I mean? For
some reason, the named range has been completely redefined using the
relative references that were written in the macro, this time based on cell
A1. (But the macro wasn't re-run was it?) Try choosing cell B9 and select
from the drop-down again. See? What the ???? Any clues? How do I write the
code using absolute references?

Problems with VBA macro for creating named ranges - ARRRG!
 

Hi Excelguru,

Sub Named_Range_Test()
range("d13:j969").name = activesheet.name
End Sub


Wolf

-----Original Message-----
Okay, this is my first post to this group, so I will try
to be as complete
and precise as I can. Here's the VBA code as it is
written...

Sub Named_Range_Test()
'
' Named_Range_Test Macro
' Macro recorded 2/29/2004 by (name removed)
'
Dim RangeName As String
RangeName = ActiveSheet.Name
Range("D13").Activate
ActiveWorkbook.Names.Add Name:=RangeName,
RefersToR1C1:= _
"='" & RangeName & "'!RC:R[956]C[6]"
Range("A14").Activate
End Sub

Okay, now. That macro creates a named range from cell
D13 to J969 inclusive
and assigns the sheet's name to this range. No big deal
right? Go ahead and
try it. It'll work just fine the first time.

Let's say you have a sheet named "Test1". You run this
macro and, low and
behold, there's a named range called "Test1" in your
drop-down list up in
the top left corner of the spreadsheet (works as it
should). Well, that's
just fine and dandy... except... Click on cell D13 and
then select the named
range from the drop-down. Looks fine, right? Great. Now
select cell A1 and
choose the named range from the drop-down list again...
see what I mean? For
some reason, the named range has been completely
redefined using the
relative references that were written in the macro, this
time based on cell
A1. (But the macro wasn't re-run was it?) Try choosing
cell B9 and select
from the drop-down again. See? What the ???? Any clues?
How do I write the
code using absolute references?


.

Problems with VBA macro for creating named ranges - ARRRG!
 

Well, I'll be damned...

Thanks!

"Wolf" wrote in message
...
Hi Excelguru,

Sub Named_Range_Test()
range("d13:j969").name = activesheet.name
End Sub


Wolf

-----Original Message-----
Okay, this is my first post to this group, so I will try
to be as complete
and precise as I can. Here's the VBA code as it is
written...

Sub Named_Range_Test()
'
' Named_Range_Test Macro
' Macro recorded 2/29/2004 by (name removed)
'
Dim RangeName As String
RangeName = ActiveSheet.Name
Range("D13").Activate
ActiveWorkbook.Names.Add Name:=RangeName,
RefersToR1C1:= _
"='" & RangeName & "'!RC:R[956]C[6]"
Range("A14").Activate
End Sub

Okay, now. That macro creates a named range from cell
D13 to J969 inclusive
and assigns the sheet's name to this range. No big deal
right? Go ahead and
try it. It'll work just fine the first time.

Let's say you have a sheet named "Test1". You run this
macro and, low and
behold, there's a named range called "Test1" in your
drop-down list up in
the top left corner of the spreadsheet (works as it
should). Well, that's
just fine and dandy... except... Click on cell D13 and
then select the named
range from the drop-down. Looks fine, right? Great. Now
select cell A1 and
choose the named range from the drop-down list again...
see what I mean? For
some reason, the named range has been completely
redefined using the
relative references that were written in the macro, this
time based on cell
A1. (But the macro wasn't re-run was it?) Try choosing
cell B9 and select
from the drop-down again. See? What the ???? Any clues?
How do I write the
code using absolute references?


.

Problems with VBA macro for creating named ranges - ARRRG!
 

Or this one.

Sub testname()
[D13:J969].Name = "RangeName"
End Sub

Regards Robert

"excelguru" wrote in message ...
Well, I'll be damned...

Thanks!

"Wolf" wrote in message
...
Hi Excelguru,

Sub Named_Range_Test()
range("d13:j969").name = activesheet.name
End Sub


Wolf

-----Original Message-----
Okay, this is my first post to this group, so I will try
to be as complete
and precise as I can. Here's the VBA code as it is
written...

Sub Named_Range_Test()
'
' Named_Range_Test Macro
' Macro recorded 2/29/2004 by (name removed)
'
Dim RangeName As String
RangeName = ActiveSheet.Name
Range("D13").Activate
ActiveWorkbook.Names.Add Name:=RangeName,
RefersToR1C1:= _
"='" & RangeName & "'!RC:R[956]C[6]"
Range("A14").Activate
End Sub

Okay, now. That macro creates a named range from cell
D13 to J969 inclusive
and assigns the sheet's name to this range. No big deal
right? Go ahead and
try it. It'll work just fine the first time.

Let's say you have a sheet named "Test1". You run this
macro and, low and
behold, there's a named range called "Test1" in your
drop-down list up in
the top left corner of the spreadsheet (works as it
should). Well, that's
just fine and dandy... except... Click on cell D13 and
then select the named
range from the drop-down. Looks fine, right? Great. Now
select cell A1 and
choose the named range from the drop-down list again...
see what I mean? For
some reason, the named range has been completely
redefined using the
relative references that were written in the macro, this
time based on cell
A1. (But the macro wasn't re-run was it?) Try choosing
cell B9 and select
from the drop-down again. See? What the ???? Any clues?
How do I write the
code using absolute references?


.





---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.593 / Virus Database: 376 - Release Date: 20/02/2004