| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Mass Creation of Named Ranges?
Hey all,
I am trying to setup of a worksheet that has ranges A:F as a named ranged using the name "DB1_#" (# = Cells 1 - 65535), I have noticed that when I try to create a mass quantity of named ranges Excel craps out on me at around 65407...anyone have any idea as to why this is or if there is a macro I could run to create a worksheet with 65535 named ranges using that setup? See example below if your confused... ROW 1: DB1_1 (Ranges A:F) ROW 2: DB1_2 (Ranges A:F) ROW 3: DB1_3 (Ranges A:F) ROW 4: DB1_4 (Ranges A:F) ||||||||||||||||||||||||||||| ROW 65536: DB1_65536 (Ranges A:F) Thank you kindly! |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Mass Creation of Named Ranges?
responded in excel.misc
-- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) wrote in message ps.com... Hey all, I am trying to setup of a worksheet that has ranges A:F as a named ranged using the name "DB1_#" (# = Cells 1 - 65535), I have noticed that when I try to create a mass quantity of named ranges Excel craps out on me at around 65407...anyone have any idea as to why this is or if there is a macro I could run to create a worksheet with 65535 named ranges using that setup? See example below if your confused... ROW 1: DB1_1 (Ranges A:F) ROW 2: DB1_2 (Ranges A:F) ROW 3: DB1_3 (Ranges A:F) ROW 4: DB1_4 (Ranges A:F) ||||||||||||||||||||||||||||| ROW 65536: DB1_65536 (Ranges A:F) Thank you kindly! |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Mass Creation of Named Ranges?
You asked me a question...you didn't respond to my question though...
Bob Phillips wrote: responded in excel.misc -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) wrote in message ps.com... Hey all, I am trying to setup of a worksheet that has ranges A:F as a named ranged using the name "DB1_#" (# = Cells 1 - 65535), I have noticed that when I try to create a mass quantity of named ranges Excel craps out on me at around 65407...anyone have any idea as to why this is or if there is a macro I could run to create a worksheet with 65535 named ranges using that setup? See example below if your confused... ROW 1: DB1_1 (Ranges A:F) ROW 2: DB1_2 (Ranges A:F) ROW 3: DB1_3 (Ranges A:F) ROW 4: DB1_4 (Ranges A:F) ||||||||||||||||||||||||||||| ROW 65536: DB1_65536 (Ranges A:F) Thank you kindly! |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Mass Creation of Named Ranges?
The number of names in a workbook is "limited by available memory". Either
that is the reason for your problem or whatever code you are using to create names is eventually trying to create an invalid name. That aside your approach will lead you to other problems. Regards, Peter T wrote in message ps.com... Hey all, I am trying to setup of a worksheet that has ranges A:F as a named ranged using the name "DB1_#" (# = Cells 1 - 65535), I have noticed that when I try to create a mass quantity of named ranges Excel craps out on me at around 65407...anyone have any idea as to why this is or if there is a macro I could run to create a worksheet with 65535 named ranges using that setup? See example below if your confused... ROW 1: DB1_1 (Ranges A:F) ROW 2: DB1_2 (Ranges A:F) ROW 3: DB1_3 (Ranges A:F) ROW 4: DB1_4 (Ranges A:F) ||||||||||||||||||||||||||||| ROW 65536: DB1_65536 (Ranges A:F) Thank you kindly! |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Mass Creation of Named Ranges?
If Excel can get to 65,407 rows with names then it sure as hell can
finish the last 100 or so...I don't think this is an issue with my memory for I have 1 gig of it in my computer. If there is someone with enough intelligence to give me a script to insert to pull the names and jump to the next line I would be most appreciative... Peter T wrote: The number of names in a workbook is "limited by available memory". Either that is the reason for your problem or whatever code you are using to create names is eventually trying to create an invalid name. That aside your approach will lead you to other problems. Regards, Peter T wrote in message ps.com... Hey all, I am trying to setup of a worksheet that has ranges A:F as a named ranged using the name "DB1_#" (# = Cells 1 - 65535), I have noticed that when I try to create a mass quantity of named ranges Excel craps out on me at around 65407...anyone have any idea as to why this is or if there is a macro I could run to create a worksheet with 65535 named ranges using that setup? See example below if your confused... ROW 1: DB1_1 (Ranges A:F) ROW 2: DB1_2 (Ranges A:F) ROW 3: DB1_3 (Ranges A:F) ROW 4: DB1_4 (Ranges A:F) ||||||||||||||||||||||||||||| ROW 65536: DB1_65536 (Ranges A:F) Thank you kindly! |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Mass Creation of Named Ranges?
Someone want to give me some suggestions instead of questioning my
method? Maybe I should have explained what it is I am trying to do... I am designing a program that will do the following... 1.) Allow someone to enter information in to a user form (already created) 2.) The ability to add information to a database (worksheet) 3.) The ability to edit information from the database (worksheet) 4.) The ability to delete information from the database (worksheet) Using names allows for my macros to pin point specific information and pull the information and allow for it to edited and resubmitted to the same area it came from...I don't understand why this is so confusing for people...I guess not many people have attempted to write full blown programs like this before in Excel? -Cheers- keepITcool wrote: what's the use of 65000 names? looks like of lot of overhead to me. -- keepITcool | www.XLsupport.com | keepITcool chello nl | amsterdam wrote in groups.com You asked me a question...you didn't respond to my question though... Bob Phillips wrote: responded in excel.misc -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) wrote in message ps.com... Hey all, I am trying to setup of a worksheet that has ranges A:F as a named ranged using the name "DB1_#" (# = Cells 1 - 65535), I have noticed that when I try to create a mass quantity of named ranges Excel craps out on me at around 65407...anyone have any idea as to why this is or if there is a macro I could run to create a worksheet with 65535 named ranges using that setup? See example below if your confused... ROW 1: DB1_1 (Ranges A:F) ROW 2: DB1_2 (Ranges A:F) ROW 3: DB1_3 (Ranges A:F) ROW 4: DB1_4 (Ranges A:F) ROW 65536: DB1_65536 (Ranges A:F) Thank you kindly! |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Mass Creation of Named Ranges?
Never underestimate "the People" :) I would not use Names for every record in a database. Using names will give you (and Excel) way!!! too much overhead. The Names collection just isn't fast/good enough with 10000+ members. Also I've seen plenty of corrupted workbooks where NAME objects were the cause. I'd create a "primaryKey" column in the database and fill that with a unique (non editable) number for "pinpointing". -- keepITcool | www.XLsupport.com | keepITcool chello nl | amsterdam wrote in oups.com Using names allows for my macros to pin point specific information and pull the information and allow for it to edited and resubmitted to the same area it came from...I don't understand why this is so confusing for people...I guess not many people have attempted to write full blown programs like this before in Excel? |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Mass Creation of Named Ranges?
Hi Shaka,
Have you considered using Excel's DataForm? Alternatively,For a more sophisticated alternative to the DataForm, see John Walkenbach's Enhanced DataForm, which may be downloaded, free of charge, at: http://j-walk.com/ss/dataform/index.htm Additionally, for a small charge, you can also obtain the source code. --- Regards, Norman wrote in message ps.com... Someone want to give me some suggestions instead of questioning my method? Maybe I should have explained what it is I am trying to do... I am designing a program that will do the following... 1.) Allow someone to enter information in to a user form (already created) 2.) The ability to add information to a database (worksheet) 3.) The ability to edit information from the database (worksheet) 4.) The ability to delete information from the database (worksheet) Using names allows for my macros to pin point specific information and pull the information and allow for it to edited and resubmitted to the same area it came from...I don't understand why this is so confusing for people...I guess not many people have attempted to write full blown programs like this before in Excel? -Cheers- keepITcool wrote: what's the use of 65000 names? looks like of lot of overhead to me. -- keepITcool | www.XLsupport.com | keepITcool chello nl | amsterdam wrote in groups.com You asked me a question...you didn't respond to my question though... Bob Phillips wrote: responded in excel.misc -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) wrote in message ps.com... Hey all, I am trying to setup of a worksheet that has ranges A:F as a named ranged using the name "DB1_#" (# = Cells 1 - 65535), I have noticed that when I try to create a mass quantity of named ranges Excel craps out on me at around 65407...anyone have any idea as to why this is or if there is a macro I could run to create a worksheet with 65535 named ranges using that setup? See example below if your confused... ROW 1: DB1_1 (Ranges A:F) ROW 2: DB1_2 (Ranges A:F) ROW 3: DB1_3 (Ranges A:F) ROW 4: DB1_4 (Ranges A:F) ROW 65536: DB1_65536 (Ranges A:F) Thank you kindly! |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Mass Creation of Named Ranges?
Am I the only one who uses their own stuff when making applications for
businesses? This guy "j-walk.com" has a interesting plug-in however, its limited to what I need it to do...The user form works fine with editing records, deleting information from it before its submitted, and eventually being able to submit it to a database. I suppose I could have used Range(ActiveCell.Value).Select to go to a location, however, this application I am designing is not being used by the "elite computer users"....using names will allow for easier pull, manipulation, and storage methods for the data...its pretty self explanitory however I am finding using the boards to not be very helpful this time...no one has posted an answer that would work so I think I am stuck using 300,000 names with in 5 diffrent worksheets. Norman Jones wrote: Hi Shaka, Have you considered using Excel's DataForm? Alternatively,For a more sophisticated alternative to the DataForm, see John Walkenbach's Enhanced DataForm, which may be downloaded, free of charge, at: http://j-walk.com/ss/dataform/index.htm Additionally, for a small charge, you can also obtain the source code. --- Regards, Norman wrote in message ps.com... Someone want to give me some suggestions instead of questioning my method? Maybe I should have explained what it is I am trying to do... I am designing a program that will do the following... 1.) Allow someone to enter information in to a user form (already created) 2.) The ability to add information to a database (worksheet) 3.) The ability to edit information from the database (worksheet) 4.) The ability to delete information from the database (worksheet) Using names allows for my macros to pin point specific information and pull the information and allow for it to edited and resubmitted to the same area it came from...I don't understand why this is so confusing for people...I guess not many people have attempted to write full blown programs like this before in Excel? -Cheers- keepITcool wrote: what's the use of 65000 names? looks like of lot of overhead to me. -- keepITcool | www.XLsupport.com | keepITcool chello nl | amsterdam wrote in groups.com You asked me a question...you didn't respond to my question though... Bob Phillips wrote: responded in excel.misc -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) wrote in message ps.com... Hey all, I am trying to setup of a worksheet that has ranges A:F as a named ranged using the name "DB1_#" (# = Cells 1 - 65535), I have noticed that when I try to create a mass quantity of named ranges Excel craps out on me at around 65407...anyone have any idea as to why this is or if there is a macro I could run to create a worksheet with 65535 named ranges using that setup? See example below if your confused... ROW 1: DB1_1 (Ranges A:F) ROW 2: DB1_2 (Ranges A:F) ROW 3: DB1_3 (Ranges A:F) ROW 4: DB1_4 (Ranges A:F) ROW 65536: DB1_65536 (Ranges A:F) Thank you kindly! |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Mass Creation of Named Ranges?
Explain this "primarykey" to me because as far as I am concerned its
only good for refrencing the VLookup code and not for submitting data to worksheet... keepITcool wrote: Never underestimate "the People" :) I would not use Names for every record in a database. Using names will give you (and Excel) way!!! too much overhead. The Names collection just isn't fast/good enough with 10000+ members. Also I've seen plenty of corrupted workbooks where NAME objects were the cause. I'd create a "primaryKey" column in the database and fill that with a unique (non editable) number for "pinpointing". -- keepITcool | www.XLsupport.com | keepITcool chello nl | amsterdam wrote in oups.com Using names allows for my macros to pin point specific information and pull the information and allow for it to edited and resubmitted to the same area it came from...I don't understand why this is so confusing for people...I guess not many people have attempted to write full blown programs like this before in Excel? |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Mass Creation of Named Ranges?
Why not just put whatever you're using as the range name into a column and use .Find() to locate the row?
-- Tim Williams Palo Alto, CA wrote in message ups.com... Explain this "primarykey" to me because as far as I am concerned its only good for refrencing the VLookup code and not for submitting data to worksheet... keepITcool wrote: Never underestimate "the People" :) I would not use Names for every record in a database. Using names will give you (and Excel) way!!! too much overhead. The Names collection just isn't fast/good enough with 10000+ members. Also I've seen plenty of corrupted workbooks where NAME objects were the cause. I'd create a "primaryKey" column in the database and fill that with a unique (non editable) number for "pinpointing". -- keepITcool | www.XLsupport.com | keepITcool chello nl | amsterdam wrote in oups.com Using names allows for my macros to pin point specific information and pull the information and allow for it to edited and resubmitted to the same area it came from...I don't understand why this is so confusing for people...I guess not many people have attempted to write full blown programs like this before in Excel? |
|
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Mass Creation of Named Ranges?
What is ".Find()"?
Tim Williams wrote: Why not just put whatever you're using as the range name into a column and use .Find() to locate the row? -- Tim Williams Palo Alto, CA wrote in message ups.com... Explain this "primarykey" to me because as far as I am concerned its only good for refrencing the VLookup code and not for submitting data to worksheet... keepITcool wrote: Never underestimate "the People" :) I would not use Names for every record in a database. Using names will give you (and Excel) way!!! too much overhead. The Names collection just isn't fast/good enough with 10000+ members. Also I've seen plenty of corrupted workbooks where NAME objects were the cause. I'd create a "primaryKey" column in the database and fill that with a unique (non editable) number for "pinpointing". -- keepITcool | www.XLsupport.com | keepITcool chello nl | amsterdam wrote in oups.com Using names allows for my macros to pin point specific information and pull the information and allow for it to edited and resubmitted to the same area it came from...I don't understand why this is so confusing for people...I guess not many people have attempted to write full blown programs like this before in Excel? |
|
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Mass Creation of Named Ranges?
Hi Shaka,
What is ".Find()"? Look at VBAhelp for the Find method. --- Regards, Norman wrote in message oups.com... What is ".Find()"? Tim Williams wrote: Why not just put whatever you're using as the range name into a column and use .Find() to locate the row? -- Tim Williams Palo Alto, CA |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Mass Creation of Named Ranges? | Excel Discussion (Misc queries) | |||
| Like 123, allow named ranges, and print named ranges | Excel Discussion (Misc queries) | |||
| using named ranges | Excel Programming | |||
| named ranges - changing ranges with month selected | Excel Programming | |||
| Named Ranges and VBA Add-ins | Excel Programming | |||
Linear Mode
