Name cells consecutively in first row as a loop?

Why do you want to name the cells individually?

You could name the entire range and then refer to each individual cell using Cells.

Why name the cells individually though?

You could name the entire range,

Please Login or Register  to view this content.

then you can refer to the individual cells like this.

Please Login or Register  to view this content.

Sorry, I don't see why you need to name each cell individually.

What are you going to do with the names?

Do you want Named Ranges or are you adding column headers to your database sheet?

The results from the questionnaire (UPPS among many others) are exported as txt files that we export/import to statistical programs such as SPSS and Stata used in research. I have a macro that transforms the names of the cells to variable names. Because I have also a sheet with neuropsychological tests (about 250!!), in addition to many other questionnaires, some with 10, some with 75, some with 170 items, to keep some track of the workbook (it is enormous), I need to name cells. All questionnaires and neuropsych test also have sumscores with a lot of formulas beneath (vlookup, etc).

But I see your point, I have asked myself, do I need to name the cells. I believe it is worth the effort. It is very useful also when I make the userforms for data entry with one textbox that have a code that places the entered value in the corresponding (and named) cell. Eg. The textbox for upps_1, I label "tb_upps_1", which then should insert the entered value in the worksheet cell named "upps_1" (instead of the default parameters "textbox1" and cell "A1" in sheet1.

In the macro that transforms the named of the cells to variable names do you have 45 variables named upps_1, upps_2, upps_3 etc?

If you do wouldn't it be an idea to use an array to store all 45 values?

For example if you had named the 45 cells with a single name, eg UPPS you could put the values into an array like this.c

Please Login or Register  to view this content.

You can then easily manipulate the array, refer to individual items within it, output it's content to a text file/other worksheet etc.

The macro that retrieves the names of the cells and transforms them to variable names - yes - they are named individually as you proposed (named upps_1, upps_2, upps_3 etc? though I'm still working on the UPPS...).

If I make an array (with which I'm not that familiar with, but we have many array names), rather than individual naming, I believe it will be harder for our macros to retrieve the "names" of the cells and make variable names out of it in a simple way (?).

The reason for the extensive naming (individual, not arrays) is also the following: We have many users of the sheet. All users use different tests/questionnaires. In the individual databases each user creates, the variable names are automatically generated from the named cells (which is different than the heading of the cells). E.g. the name for the cell containing the value for UPPS item 1 is "upps_1" (upps item one), but the "heading is UPPS 1. Named cells have no spaces which in required in statistcal programs (SPSS, Stata). UPPS 1 is unusable as a variable name...

I'm not sure if I made it clearer...?

If you used an array you wouldn't need 45 variables.

If you really want to name the individual cells you can use this.

Please Login or Register  to view this content.