naming a column & naming a cell with a number

This is a two part question. I did try a search first, but I must be doing something wrong -- nothing comes up.

Forgive me if these turn out to be dumb questions. I am a little rusty with VBA!

#1
I would like to name a column so I can refer to cells within the column using the cells property, with index numbers, but using the column name instead of the column number. This is because I want to allow users of the spreadsheet to move columns around to suit themselves, but when the master spreadsheet uploads the user's spreadsheet data it needs to be able to find the data even if it has been moved around.

I named column X "Stage" , or I should say range X:X "stage", but when I try to do something like: worksheet("summary").cells(51, "stage").value =

it crashes. I have tried stage without quotations, I have even tried

worksheets("summary").cells(51, worksheets("summary").range("stage")).value

to no avail. What am I doing wrong??


#2
For each new entry the user creates, I am experimenting with having VBA name each cell so it can quickly find that cell no matter where it's been moved to. I wish to use the naming convention "[userID][opp#][type]" where userID is a unique user name (probably initials), opp# is a unique numerical number incrementing for each new opp, and type is type of data in a column (for example "city", or "zip code") (In my first question, I want to name a column "city").

You can not name a range with a number, just text and acceptable characters, such as "_" (underscore). Is there a work-around? Am I going to have to use Roman numerals or some other means of using alpha characters to represent a number (for example, a = 1, b = 2, etc, so "aad" would equal 114)??

Originally Posted by pjoaquin

Hi evanzo,

1) Try:

Please Login or Register  to view this content.

2) You can use numbers in names, the whole name can't be just a number though (as you've found). Can you just insert an underscore before the number? e.g. _114 or _1829

THANKS! I was doing something dumb... I haven't written VBA in over 3 years; just got rusty. Thanks for the refresher!