Problems with VBA macro for creating named ranges - ARRRG!

  • Thread starter Thread starter excelguru
  • Start date Start date
E

excelguru

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?
 
Hi Excelguru,

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


Wolf
 
Well, I'll be damned...

Thanks!

Wolf said:
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?


.
Click to expand...
Click to expand...
 
Or this one.

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

Regards Robert

excelguru said:
Well, I'll be damned...

Thanks!

Wolf said:
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?


.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top