Update named range via VBA

[theDrew]

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:

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:

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

ActiveWorkbook.Names("class").RefersToRange.Column

but that returns a value of "5"

Help?

 

[erik.van.geit]

Hi,

to convert a columnnumber to columnletter use the address of the first cell in that column and delete the "1" from it

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]

Hi,

to convert a columnnumber to columnletter use the address of the first cell in that column and delete the "1" from it

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]

it would surprise me that substitute would give an error

1.
what do you get when inserting a line

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

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]

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]

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

MsgBox Application.Substitute("abcde", "a", "")
MsgBox Application.WorksheetFunction.Substitute("abcde", "a", "")

 

[theDrew]

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

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.

 

[erik.van.geit]

TADAAA !!

thank you for checking