Looks like the VBA course I want to go on should be sooner rather than later.
Once the spreadsheet has been set up I shouldn't be adding more rows. Just need to do it for the set up.
Thanks!
I'm not sure if this is a response to me or not. Although you can name each cell using VBA, naming the ranges, and then referring to the individual elements using the INDEX function, as I described earlier, is probably a better way of proceeding.
Each name that you use utilizes some memory, and the total number of names is memory limited. You are more likely to run into problems with your idea. I'm sure there are more efficient ways of doing what you want to do.
As an exercise, not recommended for "real use", one way of using VBA is below. Note that the macro will first delete ALL names in the workbook, so do not have any NAME'd ranges, that you want to keep, before you run the macro. If you run out of memory for the names, there will be an error message.
First enter all your data in rows.
Make the appropriate changes in the macro to the Name list (aNames)
Note that all of the names are followed by an underscore prior to their index number, so as to avoid naming conflicts with cell references (which are not allowed).
To enter this Macro (Sub), <alt-F11> opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.
To use this Macro (Sub), <alt-F8> opens the macro dialog box. Select the macro by name, and <RUN>.
======================================
Option Explicit
Sub NameRanges()
Dim aNames
Dim r As Range, c As Range
Dim n As Name
aNames = Array("Name_", "Age_", "Date_", "Other_")
Set r = ActiveSheet.UsedRange
For Each n In Names
n.Delete
Next n
For Each c In r
Names.Add Name:=aNames(c.Column - 1) & c.Row, _
RefersTo:="=" & c.Worksheet.Name & "!" & c.Address
Next c
End Sub
======================================
