Naming non-contigent cells with vba

[Howard]

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

 

[Ben McClave]

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

 

[Howard]

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

 

[GS]

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

 

[Ben McClave]

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'squestion. 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

 

[GS]

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