PDA

View Full Version : Dynamically Update Defined Names Range



Rem0ram
01-31-2013, 06:42 AM
Hi ExcelGeeks

Can some one help me in changing the named ranges dynamically.

Attached the sample file with requirement.

Best
Rem0

CodeNinja
01-31-2013, 08:13 AM
RemOram,
You can use the following function to re-name a range. Your post was not clear about the conditions that would cause you to activate this function, so I will leave that to you, or you can explain further those conditions.

For example, if you want this done when the user adds a new row, you could call this from the worksheet change event etc...

Oh, the function will return true if it worked, false if there was a problem...


Function reName(ByRef sDefinedName As String, ByRef rng As Range, ByRef sht As Worksheet) As Boolean

On Error GoTo errHandler:
ActiveWorkbook.Names(sDefinedName).RefersTo = sht.Name & "!" & rng.Address

reName = True
Exit Function
errHandler:
reName = False

End Function

Jan Karel Pieterse
01-31-2013, 08:42 AM
if the sheetname contains a space you're required to add ' around it...

Rem0ram
01-31-2013, 09:12 AM
Hi CodeNinja

ManyThanks for looking in to this and sorry about the vague information.

I can have a command button to facilitate this code. Can you update the code to the sample file as i have only limited knowledge about vba.

Hi Jan Karel

Thanks for the info.

Best
Rem0

CodeNinja
01-31-2013, 12:16 PM
RemOram,
A command button would work, but how would you determine exactly what range you want to use in the named ranges? Would there only be named ranges for "BS", "CFG", "MSB", "ULT", and "WM" or should this dynamically look for anything past RMSG/RMS/ and name a range for that?

By the way, good point Jan Karl, you are 100% correct, and I will add that to the function. Thanks.

CodeNinja.

Rem0ram
02-01-2013, 12:19 AM
Hi CodeNinja

ManyThanks!

I'm thinking of a way read the string in each cell, for example for RMSG/RMS/BS group first 11 characters common.

Is there a way to use the string?

Best
Rem0

snb
02-01-2013, 02:09 AM
You'd better dive into the fundamentals of VBA first.

CodeNinja
02-01-2013, 07:33 AM
I am really trying to learn the fundamentals of VBA snb :razz2:

snb
02-01-2013, 07:45 AM
@CodeNinja

Unless I use @name, I am addressing the OP in his/her thread.
Although diving into fundamentals isn't a bad thing to do for anybody, I wasn't addressing you, codeninja.

Kenneth Hobs
02-01-2013, 07:47 AM
Rem, for code to dynamical set the range to a named range, there must be logic. You must tell us the rules since it is not obvious from your example.

You would be better off breaking those into columns and use a dynamic range formula method for each column. Then, no VBA would be needed.

CodeNinja
02-01-2013, 02:25 PM
@CodeNinja

Unless I use @name, I am addressing the OP in his/her thread.
Although diving into fundamentals isn't a bad thing to do for anybody, I wasn't addressing you, codeninja.

Ya, I knew... I was being silly snb... My very weird sense of humor. :)

snb
02-01-2013, 02:37 PM
@CN

sorry for misinterpreting you......;)

Rem0ram
02-04-2013, 07:33 AM
Hi Ken

Thanks a lot for the tip.

I'm really not that familiar with Offset formula however managed to obtain the result.

Thanks a Trillion....

Best
Rem0