PDA

View Full Version : How to improve my code!?



hogg
11-10-2008, 01:57 PM
Hey!

I need to define the name of several individual cells over several different sheets. The basic name for each cell is the same but the prefix changes to denote the sheet number, for example:

Sheet 1 cell A1 = s1Description
Sheet 2 cell A1 = s2Description

Sheet 1 cell A2 = s2Type
Sheet 2 cell A2 = s2Type

I've recorded the basic code, but how do I automate the process as there are a lot more cell names to define!

Sheets("TO103").Select


ActiveWorkbook.Names.Add Name:="sh2Description", RefersToR1C1:="=TO103!R5C4"

ActiveWorkbook.Names.Add Name:="sh2Type", RefersToR1C1:="=TO103!R6C4"

ActiveWorkbook.Names.Add Name:="sh2Codes", RefersToR1C1:="=TO103!R7C4"

ActiveWorkbook.Names.Add Name:="sh2Number", RefersToR1C1:="=TO103!R8C4"


Sheets("TO104").Select

ActiveWorkbook.Names.Add Name:="sh3Description", RefersToR1C1:="=TO104!R5C4"

ActiveWorkbook.Names.Add Name:="sh3Type", RefersToR1C1:="=TO104!R6C4"

ActiveWorkbook.Names.Add Name:="sh3Codes", RefersToR1C1:="=TO104!R7C4"

ActiveWorkbook.Names.Add Name:="sh3Number", RefersToR1C1:="=TO104!R8C4"


End Sub


Thanks for your suggestions!

Bob Phillips
11-10-2008, 02:18 PM
Sub Addnames()

Call AddNamesPerSheet(2, "TO103")
Call AddNamesPerSheet(3, "TO104")

End Sub

Sub AddNamesPerSheet(ByVal Num As Long, ByVal SheetName As String)

With ActiveWorkbook.Names

.Add Name:="sh" & Num & "Description", RefersToR1C1:="=" & SheetName & "!R5C4"
.Add Name:="sh" & Num & "Type", RefersToR1C1:="=" & SheetName & "!R6C4"
.Add Name:="sh" & Num & "Codes", RefersToR1C1:="=" & SheetName & "!R7C4"
.Add Name:="sh" & Num & "Number", RefersToR1C1:="=" & SheetName & "!R8C4"
End With
End Sub

Dr.K
11-10-2008, 03:29 PM
XLD did a find job there. For more info on writing better code, see this article:

http://vbaexpress.com/forum/showthread.php?t=9882

hogg
11-11-2008, 02:36 AM
Thanks XLD for the code - works perfectly!

Thanks Dr.K for the pointer to the article - looks like some useful stuff!

Do I mark this thread as "solved" now? If so how!?

GTO
11-11-2008, 05:32 AM
Greetings,

Thanks for showing the interest in marking Solved too! Makes life much easier for those following a thread to help answer :-) Anyways, as you are the Original Poster (OP), when you open the thread, above your first post, there's a little section called Thread Tools. Click there, and select Solved.

Mark

hogg
11-13-2008, 06:23 AM
Mark,

I've logged in a few times over the last few days and still the option to select solve still doesn't appear in the thread tools.

I have three options:
Show printable version
Email this page
Unsubscribe from this thread

Sorry for being an idiot but I can't find it!

lucas
11-13-2008, 07:14 AM
hogg, does this thread have anything to do with your thread in the Word forum?

hogg
11-13-2008, 08:59 AM
This thread relates to the thread in the word forum - If I'd known what I was doing at the start I'd only have the one! Sorry chaps!

http://www.vbaexpress.com/forum/showthread.php?t=23468