Changing range name in loop
 

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= ""

Changing range name in loop
 

Why not just make your ranges dynamic so that they can expand and contract
automatically without a macro
refers to=
=offset($a$2,0,0,counta($a:$a)+1,5)
will autosize $a$2:$e$whateverrow

--
Don Guillett
SalesAid Software

"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= ""

Changing range name in loop
 

Don,

This is due to the procedure having to run continuely. I
wish to run the procedure multiple times and set it so
that no matter how many ranges there are it will work
through it all.

Thanks though for your advice

Jeff

-----Original Message-----
Why not just make your ranges dynamic so that they can
expand and contract
automatically without a macro
refers to=
=offset($a$2,0,0,counta($a:$a)+1,5)
will autosize $a$2:$e$whateverrow

--
Don Guillett
SalesAid Software

"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= ""


.

Changing range name in loop
 

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= ""

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= ""


.