Working with Dynamic Named Ranges in Excel

[pgrmdave]

So, I'm trying to keep track of some things at work, and one thing I'd like to set up is a simple spreadsheet where I can easily add people to a list (like a database, with phone numbers, e-mail etc...). I like all my bells and whistles, so I have it set up so that I could enter information into a form, click a button, and they'd be added. Furthermore, I wanted to have the macro use named cell references, because it's easier to look at later and understand the logic of

Range("NextName") = Range("NameToAdd")

 

than

 

Range(Cells(CountA(A:A),1)) = Range("F26")

 

 

The problem is that it doesn't seem to want to work. 'NextName' is a dynamic named range, equal to

OFFSET(Sheet2!$A$1,COUNTA(Sheet2!$A:$A),0,1,1)

which is able to correctly identify the next cell to be added to, but when I try to run the above code, I get "Run-Time Error 1004". I thought it was because it was a circular reference (putting something into "NextName" makes it point to the next cell below it) but even when I try:

 

c = Range("NextTag").Column 'c and r are both integers
r = Range("NextTag").Row

 

it gives me that error.

 

Any thoughts or solutions?

[alexander]

that error refers to activating a cell outside the selected range...

[Pyrotex]

So, I'm trying to keep track of some things at work, and one thing I'd like to set up is a simple spreadsheet where I can easily add people to a list ...Any thoughts or solutions?

I believe I have a solution.

 

You will need a button.

You will need a one-column list of strings, named "MyStuff"

You will need a single cell, named "NewThing"

 

The idea is, you type a word (string) into NewThing (remember to press the Enter key) and then click on the button. The new string is added to the list of strings, and the range name "MyStuff" is expanded to include the new string.

 

This solution assumes that everything is on one sheet.

Here is the macro attached to the button on the spreadsheet.

Sub AddNewThing()
   [color="SeaGreen"]' Adds a new data item to a named list of data items.
   ' Formats the new item to be same as rest of data list.
   ' Expands the range-name of the data list to include the new item.[/color]

   Dim NewThing As String      [color="SeaGreen"]'Gonna add it to MyStuff[/color]
   Dim AdrPlusOne As String    [color="SeaGreen"]'Build new range for MyStuff[/color]
   Dim MSrow As Integer        [color="SeaGreen"]'First row# of MyStuff[/color]
   Dim MSrwz As Integer        [color="SeaGreen"]'Number of rows in MyStuff[/color]
   
   [color="SeaGreen"]'Fetch NewThing; put in all the filtering you want[/color]
   NewThing = Range("NewThing").Value
   If NewThing = "" Then Exit Sub
   [color="SeaGreen"]'Get current start row and size of MyStuff[/color]
   MSrow = Range("MyStuff").Row
   MSrwz = Range("MyStuff").Rows.Count
   [color="SeaGreen"]'Select first (top) cell of MyStuff and copy formatting[/color]
   Range("MyStuff").Range("A1:A1").Select   [color="SeaGreen"]'Arcane![/color]
   Selection.Copy
   [color="SeaGreen"]'Select cell immediately below MyStuff, paste formatting only[/color]
   Range("MyStuff").Offset(MSrwz, 0).Range("A1:A1").Select   [color="SeaGreen"]'Arcane![/color]
   Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
       SkipBlanks:=False, Transpose:=False
   Application.CutCopyMode = False
   [color="SeaGreen"]'Put NewThing into this newly formatted cell[/color]
   ActiveCell.Value = NewThing
   [color="SeaGreen"]'Calculate new address for MyStuff (one extra row)[/color]
   MSrwz = MSrwz + 1
   AdrPlusOne = "A1:A" & MSrwz
   [color="SeaGreen"]'Use new address to redefine MyStuff[/color]
   Range("MyStuff").Range(AdrPlusOne).Select   [color="SeaGreen"]'Arcane![/color]
   Selection.Name = "MyStuff"
   [color="SeaGreen"]'Clear NewThing[/color]
   Range("NewThing").Select
   ActiveCell.Value = ""
End Sub

 

I assumed that the list named MyStuff is formatted a certain way. This solution assures that your formatting will be kept as well. :doh:

 

This solution uses an arcane macro trick where you define a region of cells with TWO range names in ONE command. (Wherever I used this trick in the code, I flagged it with the word "Arcane!".) The first range winds up defining only the upper left corner of your eventual selection. The second range defines the number of rows and columns to be in the eventual selection by the clever assumption that the upper left corner of whatever has been defined so far is "A1". In other words, the second range is a "Relative Addressing" extension.

 

For example,

Range("BigArea").Range("A1:A1").select

will select ONLY the upper left single cell of BigArea. Whereas,

Range("BigArea").Range("A1:C3").select

will select a 3X3 block of cells, starting with the upper left cell of BigArea.

 

This is all covered in my brilliant and fabulous Excel Manual, available here within Hypography.

 

Pyro

[pgrmdave]

Seems to work - thanks Pyro!