Changing range name in loop
Thanks a bunch
Jeff
-----Original Message-----
Hi Jeff,
Names don't quite work like that.
Try something like this:
Sub B()
Dim rng As Range
Dim c As Range
Dim n As Long: n = 1
Dim WS As Worksheet
Set rng = Selection
Set WS = ActiveSheet
For Each c In rng
WS.Names.Add "Name" & n, c.Address
n = n + 1
Next c
End Sub
HTH
Peter Beach
"Jeff" wrote in
message
...
Hey group,
I'm having a problem with looping through a macro and
renaming a range accordingly.
First step i name my range . Which i can get. but when
the
loop progresses im trying to have it change according to
the amount of times the procedure loops. i was hoping
someone could help me. Thank you in advance.
Jeff
P.s there are notes where im having difficulty
'''''''
Dim DMrng As String
Dim Fname As String
Dim Sierra As String, Yukon As String, Malibu As String
Dim Cnt As Integer
'''sets CNT initial value
Cnt = 0
'''''Loop in question begins
Do
Fname = ActiveWorkbook.Name
'''selects if there is only one cell in range
If ActiveCell.Offset(-1, 0).Value < "" Then Range
(Selection, Selection.End(xlUp)).Name = "DMrng" & Cnt
'' if multiple cells in range the selects
If ActiveCell.Offset(-1, 0).Value = "" Then
ActiveCell.Name = "DMrng" & Cnt
'XXXXXXXXXXXX here is the problem the name i can not
'''XXXXXXXXXXX GET range to be named
correctly'''''''''''''''''''''
Sierra = "=COUNTIF(" & "'" & Fname & "'!" & DMrng &
cnt",Sierra)"
Yukon = "=COUNTIF(" & "'" & Fname & "'!DMrng &
cnt,""Yukon"")"
Malibu = "=COUNTIF(" & "'" & Fname & "'!DMrng &
cnt,""Malibu"")"
ActiveCell.FormulaR1C1 = Sierra
ActiveCell.Offset(1, 0).Select
ActiveCell.FormulaR1C1 = Yukon
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = Malibu
Cnt = Cnt + 1
loop until activecell.value= ""
.
|