| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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= "" |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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= "" . |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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= "" |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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= "" . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| reset the range of FOR loop | Excel Discussion (Misc queries) | |||
| Loop through last cell/row of used range | Excel Discussion (Misc queries) | |||
| Excel VBA - changing variables during a loop | Excel Programming | |||
| Increasing a range in a loop | Excel Programming | |||
| Loop for changing cell formatting | Excel Programming | |||
Hybrid Mode
