PDA

View Full Version : Solved: ? Better way to code


Spratt_Cary
09-12-2007, 11:51 PM
Is there a better way to code the following?
Private Sub CellName()
ActiveWorkbook.Names.Add name:="Form", RefersToR1C1:="=R3C1"
ActiveWorkbook.Names.Add name:="Site", RefersToR1C1:="=R3C2"
ActiveWorkbook.Names.Add name:="Description", RefersToR1C1:="=R3C3"
ActiveWorkbook.Names.Add name:="DBCDesc", RefersToR1C1:="=R3C4"
ActiveWorkbook.Names.Add name:="Tdesc", RefersToR1C1:="=R3C5"
ActiveWorkbook.Names.Add name:="Required", RefersToR1C1:="=R3C6"
ActiveWorkbook.Names.Add name:="Title", RefersToR1C1:="=R3C7"
ActiveWorkbook.Names.Add name:="Caller", RefersToR1C1:="=R3C8"
ActiveWorkbook.Names.Add name:="Date", RefersToR1C1:="=R3C9"
ActiveWorkbook.Names.Add name:="Personnel", RefersToR1C1:="=R3C10"
ActiveWorkbook.Names.Add name:="Study", RefersToR1C1:="=R1C1"
ActiveWorkbook.Names.Add name:="Sname", RefersToR1C1:="=R1C2"
End Sub
Bob Phillips
09-13-2007, 12:38 AM
A bit easier to read



Private Sub CellName()
With ActiveWorkbook.Names
.Add Name:="Form", RefersToR1C1:="=R3C1"
.Add Name:="Site", RefersToR1C1:="=R3C2"
.Add Name:="Description", RefersToR1C1:="=R3C3"
.Add Name:="DBCDesc", RefersToR1C1:="=R3C4"
.Add Name:="Tdesc", RefersToR1C1:="=R3C5"
.Add Name:="Required", RefersToR1C1:="=R3C6"
.Add Name:="Title", RefersToR1C1:="=R3C7"
.Add Name:="Caller", RefersToR1C1:="=R3C8"
.Add Name:="Date", RefersToR1C1:="=R3C9"
.Add Name:="Personnel", RefersToR1C1:="=R3C10"
.Add Name:="Study", RefersToR1C1:="=R1C1"
.Add Name:="Sname", RefersToR1C1:="=R1C2"
End With
End Sub
Bob Phillips
09-13-2007, 12:41 AM
Another, better IMO



Private Sub CellName()
With ActiveSheet
.Range("A3").Name = "Form"
.Range("B3").Name = "Site"
.Range("C3").Name = "Description"
.Range("D3").Name = "DBCDesc"
.Range("E3").Name = "Tdesc"
.Range("F3").Name = "Required"
.Range("G3").Name = "Title"
.Range("H3").Name = "Caller"
.Range("I3").Name = "Date"
.Range("J3").Name = "Personnel"
.Range("A1").Name = "Study"
.Range("B1").Name = "Sname"
End With
End Sub
Spratt_Cary
09-13-2007, 12:47 AM
Perfect, works great
unmarkedhelicopter
09-13-2007, 01:04 AM
So good, you coded it twice !!!
Bob Phillips
09-13-2007, 01:10 AM
No UMH, it was just that posting was taking so long then (over 3 minutes) that I abandoned the firdt time and tried again. ALthough I abandoned, it obviously posted anyway.
johnske
09-13-2007, 02:44 AM
fixed :)
Norie
09-13-2007, 09:44 AM
Perhaps overkill, but why not.:)

Private Sub CellName()
Dim arrRanges
Dim arrNames
Dim I As Long
arrNames = Array("Form", "Site", "Description", "DBCDesc", _
"Tdesc", "Required", "Title", "Caller", _
"Date", "Personnel", "Study", "Sname")
With ActiveSheet
arrRanges = Array(.Range("A3"), .Range("B3"), .Range("C3"), _
.Range("D3"), .Range("E3"), .Range("F3"), _
.Range("G3"), .Range("H3"), .Range("I3"), _
.Range("J3"), .Range("A1"), .Range("B1"))
End With

For I = LBound(arrNames) To UBound(arrNames)
arrRanges(I).Name = arrNames(I)
Next I

End Sub
Bob Phillips
09-13-2007, 10:15 AM
fixed :)

No, not fixed, just removed one of the manifestations of the problem. I still wait a long time.
unmarkedhelicopter
09-13-2007, 12:13 PM
If Bob is taking the high road then you are taking the one via Timbuktoo !!!