Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Naming non-contigent cells with vba
This code from help works fine to name cells B1 to B5 with the text in A1 to A5.
How do I modify it to name cells B1, F1 and K1 with the text in A1 and to name cells B2, F2 and K2 with the text in A2 and so on? So with text in A1 = Data1 then cells B1,F1 and K1 are named Data1, the text in A2 = Data2 then cells B2, F2 and K2 are named Data2. This is for a possible solution to a poster who wants to produce the sum of three non contingent cells from one sheet to another sheet. I suggested naming the range Data and on the other sheet =SUM(Data). I then learn he has 200+ rows. Presents a tedious problem in naming. Code to sum the non-contigent cell on sheet1 and zip the totals over to sheet2 is certanily a possibility. However, the poster did not know how to name a range so I am assuming a very new user who would be more comfortable with some simpler formulas once all the naming gets done. Option Explicit Sub NameIt() Dim rangeToName As Range Set rangeToName = Worksheets("Sheet1").Range("A1:B5") rangeToName.CreateNames Left:=True End Sub 'This example creates names for cells B1:B3 based on the text in cells A1:A3. 'Note that you must include the cells that contain the names in the range, 'even though the names are created only for cells B1:B3. Thanks, Regards, Howard |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Naming non-contigent cells with vba
Howard,
This macro worked for me: Sub CreateNames() Dim rData As Range Dim lNames As Long Dim rNames As Range Dim sError As String Set rData = Sheet1.Range("B1,F1,K1") Set rNames = Sheet1.Range("A1") For lNames = 0 To 4 On Error Resume Next ThisWorkbook.Names.Add Name:=rNames.Offset(lNames, 0), RefersTo:= _ "=" & rData.Offset(lNames, 0).Address If Err.Number < 0 Then sError = sError & vbCr & rNames.Offset(lNames, 0) Err.Clear End If Next lNames If Len(sError) 0 Then MsgBox "The following names could not be added: " & _ vbCr & sError, vbExclamation, "Check names" End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Naming non-contigent cells with vba
On Sunday, November 4, 2012 7:31:55 PM UTC-8, Ben McClave wrote:
Howard, This macro worked for me: Sub CreateNames() Dim rData As Range Dim lNames As Long Dim rNames As Range Dim sError As String Set rData = Sheet1.Range("B1,F1,K1") Set rNames = Sheet1.Range("A1") For lNames = 0 To 4 On Error Resume Next ThisWorkbook.Names.Add Name:=rNames.Offset(lNames, 0), RefersTo:= _ "=" & rData.Offset(lNames, 0).Address If Err.Number < 0 Then sError = sError & vbCr & rNames.Offset(lNames, 0) Err.Clear End If Next lNames If Len(sError) 0 Then MsgBox "The following names could not be added: " & _ vbCr & sError, vbExclamation, "Check names" End Sub Works perfect...! Thanks. Regards, Howard |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Naming non-contigent cells with vba
Ben McClave laid this down on his screen :
Howard, This macro worked for me: Sub CreateNames() Dim rData As Range Dim lNames As Long Dim rNames As Range Dim sError As String Set rData = Sheet1.Range("B1,F1,K1") Set rNames = Sheet1.Range("A1") For lNames = 0 To 4 On Error Resume Next ThisWorkbook.Names.Add Name:=rNames.Offset(lNames, 0), RefersTo:= _ "=" & rData.Offset(lNames, 0).Address If Err.Number < 0 Then sError = sError & vbCr & rNames.Offset(lNames, 0) Err.Clear End If Next lNames If Len(sError) 0 Then MsgBox "The following names could not be added: " & _ vbCr & sError, vbExclamation, "Check names" End Sub ust curious why the names are being created at workbook level (global scope) and not sheet level (local scope)! -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Naming non-contigent cells with vba
Garry,
I suppose you could do it either way, depending on the need. I understood from Howard's original question that this was to help with another poster's question. That post needed to name a range of cells on Sheet1 for use on Sheet2. By making the named ranges Global, I intended for the name reference to always refer to the cells on Sheet1, regardless of what sheet uses the reference. Ben |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Naming non-contigent cells with vba
Ben McClave explained :
Garry, I suppose you could do it either way, depending on the need. I understood from Howard's original question that this was to help with another poster's question. That post needed to name a range of cells on Sheet1 for use on Sheet2. By making the named ranges Global, I intended for the name reference to always refer to the cells on Sheet1, regardless of what sheet uses the reference. Ben Ben I understand your point. Food for thought... I try to follow the so called 'best practice' of never using global names unless absolutely necessary. For me, global names aren't reusable on other sheets. Local names are reusable on other sheets and can be referenced simply by prefixing the defined name with the sheet name... 'Sheet2'!Data1 = 'Sheet1'!Data1 ...where both ref the same data area on both sheets. === Optionally, you could go as did Howard and name the Sheet2 data area "Data2" but that's a dis-association IMO... 'Sheet2'!Data2 = 'Sheet1'!Data1 === Finally, should Sheet2 need to be copied to another wkb the global names follow with their refs to the original wkb. This can/will raise name conflict issues if the target wkb happens to use the same names. If so, the user is prompted as to how to proceed (choose which version of the name to use), and depending on how the prompt is replied to all hell can break loose. Since local scope names are confined to the wks they're defined on, no name conflict issues are raised when copying sheets to another wkb. For example, what do you suppose would happen in the case of global scope names used on a template that gets inserted into any wkb the user chooses?. -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Range naming cells with blank cells through coding | Excel Discussion (Misc queries) | |||
Naming Cells - need help | Excel Programming | |||
naming cells | Excel Discussion (Misc queries) | |||
Naming cells... | Excel Worksheet Functions | |||
Naming Cells | Excel Programming |