add names

[Scott]

I have to define a few dozens of names in an Excel files. I put all the names
in Range ("A1:A35") and their respective addresses in Range ("B1:B35"), such
as "sheet1!$a$1:$c$20". What is the macro to run this job?

Also, want to know what are the codes to delete all these names later on if
necessary.

Thanks.

 

[Joel]

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 1/23/2008 by Joel Warburg
'

'
For RowCount = 1 To 35
ActiveWorkbook.Names.Add Name:=Range("A" & RowCount), _
RefersTo:="=" & Range("B" & RowCount)
Next RowCount
End Sub

For Each nm In ActiveWorkbook.Names
nm.Delete
Next nm

 

[Dave Peterson]

This adds the names as global names (workbook level):

Option Explicit
Sub testme()

Dim myRng As Range
Dim myCell As Range
Dim TestRng As Range

With Worksheets("sheet1")
Set myRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))
End With

For Each myCell In myRng.Cells
myCell.Offset(0, 2).Value = "ok"
Set TestRng = Nothing
On Error Resume Next
Set TestRng = Application.Range(myCell.Offset(0, 1).Value)
On Error GoTo 0

If TestRng Is Nothing Then
myCell.Offset(0, 2).Value = "Invalid address!"
Else
On Error Resume Next
TestRng.Name = myCell.Value
If Err.Number <> 0 Then
myCell.Offset(0, 2).Value = "Invalid name!"
Err.Clear
End If
On Error GoTo 0
End If
Next myCell

End Sub


And this would delete the names:
Option Explicit
Sub testme02()
Dim myName As Name
Dim myRng As Range
Dim myCell As Range

With Worksheets("sheet1")
Set myRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))
End With

For Each myCell In myRng.Cells
On Error Resume Next
ThisWorkbook.Names(myCell.Value).Delete
If Err.Number <> 0 Then
myCell.Offset(0, 3).Value = "error while deleting"
Err.Clear
Else
myCell.Offset(0, 3).Value = "Deleted"
End If
Next myCell
End Sub

Since you're working with names, get Jan Karel Pieterse's (with Charles Williams
and Matthew Henson) Name Manager:

You can find it at:
NameManager.Zip from http://www.oaltd.co.uk/mvp

You'll find it very valuable.