Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
add names
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
add names
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 "Scott" wrote: 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
add names
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. Scott wrote: 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. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
converting email address names in a range of cells to real names | Excel Worksheet Functions | |||
how to copy workbook names and worksheet names to columns in acces | Excel Programming | |||
Sorting and matching rows of names with Socials with master list and eliminating the extra names | Excel Worksheet Functions | |||
return all worksheet tab names and chart sheet tab names in report - an example | Excel Programming | |||
Change names of files in a folder to match names in Excel Column | Excel Programming |