A better way to create list (alphabet, numbers, etc).

[jomili]

This seems so simple, yet I can't find it on the internet. I frequently have to number a section of a column, like

Please Login or Register  to view this content.

or create part of the alphabet

Please Login or Register  to view this content.

How do I do that with VBA? Below is the code of how I'm doing it, but there's GOT to be a better way. Any ideas?

Please Login or Register  to view this content.

[JBeaucaire]

One of many ways:

Please Login or Register  to view this content.

[jomili]

Okay, I've got a way. It all depends on Autofill, and on making sure any "strange" lists, like mine with the zeros in front, are included in your custom lists. Here's my amended code:

Please Login or Register  to view this content.

JB, thanks for your contribution. That's a whole different approach that I wouldn't have thought of, or would have struggled with trying to get the syntax right. Thanks so much!

[snb]

Possibly disappointingly simple:

Please Login or Register  to view this content.

'formatted'

Please Login or Register  to view this content.

if you prefer uppercase characters:

Please Login or Register  to view this content.

lowercase

Please Login or Register  to view this content.

[StephenR]

Just do it once and then add it to your custom lists.

[snb]

For example:

Please Login or Register  to view this content.

[jomili]

Wow! More answers than I can shake a stick at!

SNB, I'm a littled puzzled by the codes you posted.

The first two, I can see, insert "1" (or "01") through "12", in column A, rows 1-12. However, how would I change them if I wanted, say, January through December, which is in my custom lists?

The next two, for uppercase and lowercase, won't do anything for numbers. So, I put "January" in B1, "February" in B2, but when I ran the code, I got a "#NAME?" error, How do I use these to put in text lists, whether upper or lowerclass.

For the last one, is it just adding to my custom lists, so Excel will recognize the patterns when I use them again?

Thanks, and I look forward to learning more.

Thanks,
John

[jomili]

SNB, okay, I think I've got your last post almost figured out. The third line is adding the range 01-100 to my custom lists. The two lines before it are adding the alphabet, as Lower and Upper case. But how about if I wanted to add the list

Please Login or Register  to view this content.

[snb]

Go back to your first question:

- you asked for a simple method to enter numbers: my first answer

- you asked for a simple method to enter formatted numbers: my second answer

- you asked for a simple method to enter uppercase characters: my third answer

- you didn't ask for a simple method to enter lowercase characters; nevertheless I provided one: my fourth answer.

If you want to enter monthnames you can make use of Excel's builtin Lists.

Please Login or Register  to view this content.

if you want the list to be entered:

Please Login or Register  to view this content.

If you have to use this list often you could consider to add it to your customlists.

Please Login or Register  to view this content.

So if you enter 'Dasher' in a cell you can autofill the adjacent cells (whether row or column) and the other names will appear.

[jomili]

SNB,

I wasn't trying to disparage your efforts; I appreciate all you've provided, but it's taken me a little while to understand it. Your "Lowercase" and "Uppercase" macros insert the Alphabet, lower and upper case. I didn't understand that at first, and thought they were to modify what was already in the cell. Now I understand it better.

When StephenR said "Just do it once and then add it to your custom lists" and you posted your macro for adding to the custom lists, I immediatly thought of using that to add "irregular" lists (weakly implied in my "etc" in the title), hence my question along those lines. I'm still interested in a method for doing that, say taking a list I've typed into column A and adding it to my Custom lists. I know how to do it manually, but not via VBA. That's beyond the scope of what I originally requested, so don't feel obligated to provide a solution.

I've played with the last two codes you've posted; one lists the contents of my custom lists (the 3 determines which list it pulls) and pastes in K. The other adds the list to Column A. I'm sure I'll use both macros. Thanks so much for providing them, and all the others. You've increased my knowledge base tremendously already today. Thank you.

[jomili]

SNB,

I don't know how I missed it, but I didn't see your "Sub snb_006()" macro when I typed my last letter. You've delivered way over and above what I originally asked for, and I am giddy with glee! Thank you so much!

[snb]

I know: I was editing (revising/ adding) that post while you were formulating your answer. Glad you found it and appreciate it.

[jomili]

I very much do appreciate anyone going above and beyond. Sometimes I'm slow on the uptake, but when the light dawns I can surely recognize true value. BTW, I don't really think I have any use for reindeer names, but I do frequently have to create "odd" lists, and reuse them over and over, so the "Reindeer" stuff is great code to have.

I've put all of the information I've learned from this post into a simple Word doc, attached, for any poor soul who stumbles across this post and wants it all spelled out (that's how I like it best). I also put in code for how to insert a custom list from a list on your spreadsheet (see SNB, I use what you teach!).

Thanks, everyone, for helping me out on this one!

[snb]

Your autofill solution can be reduced to:

Please Login or Register  to view this content.

[jomili]

Thanks SNB, I added that one to the Word Doc. I've got to learn more about that RESIZE function; I've seen it used and know it's extremely useful, but haven't ever researched it. Thanks for giving me another prompt to look into it.

[snb]

_________