Assign different named ranges based on variable number of entries

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?

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!

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

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.

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....

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.

Originally Posted by BrotherNeptune

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.

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