Assign different named ranges based on variable number of entries

[BrotherNeptune]

Hi All

I’m still fairly new to VB code, so I’ve been trying to practice by creating spreadsheets to make my life at work easier.

This spreadsheet will be used as a template each year. What I’d like to do is use VB to create named ranges based on each State grouping (i.e. Range(“$A16:$A21”)=“Arkansas”, Range(“$A22:$A24”)=”Connecticut”, etc.).

I’ve tried to code using VB to name each State’s range. I'm sure I've completely fubar'd this, so I need some expert help. The code below is where I appear to be running into problems….

Please Login or Register  to view this content.

Obviously, each time a user enters data for a new year, each State will be assigned different ranges than the year before. I have tried to create the VB sub, but I can’t seem to get the hang of it yet.

I’ve attached a sample spreadsheet for reference.
Any ideas?

[JBeaucaire]

This is how I would update the named ranges:

Please Login or Register  to view this content.

[skywriter]

Well mine is nowhere near as cool as Jerry's and it assumes you tell it which name to lookup to name the range.

But it's different and if you are learning code you could learn a little bit about the find method, from this code.

Good Luck.

Please Login or Register  to view this content.

[BrotherNeptune]

Excellent!

This works perfectly, Jerry..... based on what I submitted as an example.....

I changed the names of the agencies to State names to protect the innocent... How could I make this work if the range was a city... specifically, "Chicago, City of".. or "Orlando, City of".. or "Cook County"...

Could I use CONCATENATE somewhere in the code?

Thanks!

[JBeaucaire]

Sorry, no Dr Miagi voodoo code... please post a workable exact sample and we'll take another look.

[BrotherNeptune]

Thank you very much for extra effort. I've attached a copy of the original with several different city names.

[JBeaucaire]

NAMED RANGES have to be a single text string with no special characters or spaces. IN the original code I was replacing spaces with underscores, so "New York" created a named range "New_York".

You new problem is commas. So we'll just remove those altogether.

Please Login or Register  to view this content.

[BrotherNeptune]

Thank you! This is precisely what I'm looking for. I hope you don't mind if I pick your brain a little more... If you need me to start a new thread, I can do that.

I'm having a difficult time understanding the code that you've provided. How would I reference each Named Range, i.e.

Please Login or Register  to view this content.

.... where cell2 is the Named Range....

Ultimately, I need to compare $$ amounts for each Named Range and add $$ to those that haven't reached their max. I'm hoping that by using these Named Ranges it will allow me to create the necessary macro to do this.

[JBeaucaire]

I'm not following how you're connecting named ranges this cell2 concept.

Start anew and present the problem in a non-VBA context, referencing your workbook with BEFORE and AFTER examples. That should clarify it for me, we can code backwards from there.

[BrotherNeptune]

Jerry

I've attached a Word document that outlines the steps taken to reach the end product. I've also attached a new spreadsheet that shows the Before and After.

I've also tried documenting the code below to match the formula that needs to be followed, but like I said, I'm still learning.... it just seems a bit clunky to me.

Essentially, I anticipate it would be best to calculate z within each Named Range.

I’m hoping that the following code will work, or can be eventually tweaked to make it work correctly:

Please Login or Register  to view this content.

Advice? Criticisms?

[JBeaucaire]

I'll admit I'm a bit stumped by this. Will draw in some other eyes to consider it.

[BrotherNeptune]

Thanks, Jerry. I hope my explanation was at least somewhat comprehensible. It makes sense to me, but I've been working with it for several years now....

[quekbc]

Hi BrotherNeptune, I think I understand what you are trying to do, however, I fail to tally the Butte numbers and I have identified the following.

In your word document, you stated that "Therefore, 150.94 will be added to Rows 21, 22, and 23 each."
The way I understood it, you should add 150.94 / 3 = 50.31 to each 21, 22 and 23.

Can you confirm that my understanding is correct?

EDIT: Thought I'd just upload what I have so far. I.e. a working example based on my understanding above.
NOTE: I've changed the coding for the named ranges as well as they were previously recording addresses as texts.

[BrotherNeptune]

Hi quekbc

You are correct. The 150.94 should be divided amongst the remaining 3. Apparently, my explanation and example were a bit off.

I tried your code with the sheet you provided, and everything works like magic! Next test: a copy of the original spreadsheet with over 100 Agencies.

You mention that you changed the coding for the Named Ranges... How were you able to identify the Agency name as a Range?

Many kudos to everyone for your help!! Although, I do see I have much more learning to do, and with your codes, I will understand VBA that much more.

[BrotherNeptune]

Trying to post a response, but I keep getting errors.

Tried it on a copy of the workbook I hope to apply this code to....

The calculations work perfectly!! I couldn't have asked for a better script.

An item to note:

If there are blank rows in the table, I receive an Application-defined or Object-defined error in the Named Range code. I've tried adding the Code to this post, but I continue to receive errors.

I hope that the end-user will not have blank rows, but I can't guarantee it, so I'd like to anticipate that they will.

Also, if there were a reason to skip a Named Range, or to save a Named Range until last, say "Chicago, City of", where and how would I do that?

[quekbc]

You mention that you changed the coding for the Named Ranges... How were you able to identify the Agency name as a Range?

The Agency name is a Range object, from a list of Names. To extract the Ranges that was referred to by the Named range, you use the Names.RefersToRange property.

For example, if Chicago_City_Of is referring to cells A1:A10 then
Names("Chicago_City_Of") is a Name object
Names("Chicago_City_Of").RefersToRange is the Range object such that you can do...

Please Login or Register  to view this content.

As for the empty rows, perhaps you can change the macro to first find these rows and delete them.

As for the inclusion or exlusion of certain cities from calculation, you'll probably need to create a new table listing all the names with a Yes/No next to it. Then amend the macro code to check these Yes/Nos on whether to process them or not. If No, proceed to next name.

[BrotherNeptune]

Excellent! Thank you so much for all of your help!

Thanks to Jerry, skywriter, and quekbc!! I've learned a heck of a lot here

[JBeaucaire]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.