Update named range via VBA

theDrew

Board Regular
Joined
May 6, 2003
Messages
104
Hello!

In an application, I use VBA to update named ranges. The ranges are used in a series of data validation functions. Unfortunately I can't use dynamic named ranges, otherwise the data validation won't work.

I wrote a function to update the named ranges, and put a function call in the Worksheet_Deactivate event. For each range the code looks like this:

Code:
LastRow = Range("Class_Schedule!E65536").End(xlUp).Row
ActiveWorkbook.Names("class").RefersToR1C1 = "=Class_Schedule!R2C5:R" & LastRow & "C5"

That worked fine until I had to insert some columns into the Class_Schedule sheet. Because I had to move some of the ranges, the column number and letter changed. It took me some serious time to sort out all of the code changes. How can I update the range without hard coding the column number?

I tried the following:
Code:
LastRow = Range("Class_Schedule!E65536").End(xlUp).Row
Column = ActiveWorkbook.Names("class").RefersToRange.Column
ActiveWorkbook.Names("class").RefersToR1C1 = "=Class_Schedule!R2C" & Column & ":R" & LastRow & "C" & Column

That works, but the "E" in ("Class_Schedule!E65536") is still hard-coded. I can't figure out how to extract a value of "E" from the range properties. I tried
Code:
ActiveWorkbook.Names("class").RefersToRange.Column

but that returns a value of "5"

Help?
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Hi,

to convert a columnnumber to columnletter use the address of the first cell in that column and delete the "1" from it
Code:
Dim col As Integer
Dim ColLetter As String

col = ActiveWorkbook.Names("class").RefersToRange.Column
ColLetter = Application.Substitute(Cells(1, col).Address(0, 0), 1, "")

MsgBox ColLetter

what do you mean here ?
Unfortunately I can't use dynamic named ranges, otherwise the data validation won't work.
see http://www.datapigtechnologies.com/flashfiles/dynamicranges.html

kind regards,
Erik
 

theDrew

Board Regular
Joined
May 6, 2003
Messages
104
Hi,

to convert a columnnumber to columnletter use the address of the first cell in that column and delete the "1" from it
Code:
Dim col As Integer
Dim ColLetter As String

col = ActiveWorkbook.Names("class").RefersToRange.Column
ColLetter = Application.Substitute(Cells(1, col).Address(0, 0), 1, "")

MsgBox ColLetter

Thanks, but that gives me an "Application-defined or object-defined" error. It doesn't seem to like "Application.Substitute"

what do you mean here ?
Unfortunately I can't use dynamic named ranges, otherwise the data validation won't work.

The data validation that I use involves two drop-down boxes. In the first, the user selects a class. In the second, the user selects a date that is associated with the class in question. There is a term for this type of validation, but it slips my mind at the moment.

At any rate, dynamic named ranges can't be used in this scenario, otherwise the "date" combo box displays as empty. The tutorial that showed me how to do this type of data validation specifically stated that dynamic named ranges can't be used here.
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
it would surprise me that substitute would give an error

1.
what do you get when inserting a line
Code:
MsgBox ActiveWorkbook.Names("class").RefersToRange.Address

2.
click in the word SUBSTITUTE
hit function key F1 to get the help
what happens ?
what Excelversion do you have ?

3.
alternative for substitute
Code:
ColLetter = Left(Cells(1, col).Address(0, 0), Len(Cells(1, col).Address(0, 0)) - 1)

please help me to gather information
if the alternatice works, I would still be interested in the answer to questions 1. and 2.
 

theDrew

Board Regular
Joined
May 6, 2003
Messages
104

ADVERTISEMENT

1. $E$2:$E$15

2. I have Excel 2002.
Hitting F1 here just opens up a generic help page on using Excel functions in VBA.

3. This appears to work. Thanks for the help!
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
you're WELCOME :)

still need your feedback
2. I have Excel 2002.
Hitting F1 here just opens up a generic help page on using Excel functions in VBA.
you hit F1 from within Excel, while you need to hit F1 being in VBA-Editor
click in the word SUBSTITUTE
hit function key F1 to get the help

EDIT: please try
Code:
MsgBox Application.Substitute("abcde", "a", "")
MsgBox Application.WorksheetFunction.Substitute("abcde", "a", "")
 

theDrew

Board Regular
Joined
May 6, 2003
Messages
104
you're WELCOME :)

still need your feedback
2. I have Excel 2002.
Hitting F1 here just opens up a generic help page on using Excel functions in VBA.
you hit F1 from within Excel, while you need to hit F1 being in VBA-Editor
click in the word SUBSTITUTE
hit function key F1 to get the help

I was in the VBA editor. The help page that opened is "Using Microsoft Excel Worksheet Functions in VBA."

EDIT: please try
Code:
MsgBox Application.Substitute("abcde", "a", "")
MsgBox Application.WorksheetFunction.Substitute("abcde", "a", "")

"bcde" both times.

I think I may have made a syntax error in entering the code that used Application.Substitute, because I erased it and entered it again and it now works.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,093
Messages
5,545,916
Members
410,711
Latest member
Josh324
Top