| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Numbering cells in a range
I am trying to figure out how to number cells in a range in a numerical
ascending order. By recording a macro I can see how its done on one sheet. '===== Range("A6").Select ActiveCell.FormulaR1C1 = "1" Selection.AutoFill Destination:=Range("A6:A26"), Type:=xlFillDefault '===== If I change ActiveCell.FormulaR1C1 = "1" to ActiveCell.FormulaR1C1 = MyVar the numbering sequence stops and only repeats the value of MyVar in each cell. The reason I need a variable is I wont know the number of sheets in the workbook and I need to number the cells 1 thru 21 and adding 21 for each sheet to continue numbering. -- Regards Rick XP Pro Office 2007 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Numbering cells in a range
See if changing to this Destination will work.
Selection.AutoFill Destination:=Range("A6:A" & Sheets.Count), Type:=xlFillDefault "Rick S." wrote: I am trying to figure out how to number cells in a range in a numerical ascending order. By recording a macro I can see how its done on one sheet. '===== Range("A6").Select ActiveCell.FormulaR1C1 = "1" Selection.AutoFill Destination:=Range("A6:A26"), Type:=xlFillDefault '===== If I change ActiveCell.FormulaR1C1 = "1" to ActiveCell.FormulaR1C1 = MyVar the numbering sequence stops and only repeats the value of MyVar in each cell. The reason I need a variable is I wont know the number of sheets in the workbook and I need to number the cells 1 thru 21 and adding 21 for each sheet to continue numbering. -- Regards Rick XP Pro Office 2007 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Numbering cells in a range
Eh?
That created an error 1004, merged cells must be identically sized The cells are not merged cells in my range; A6:A26; and they are identical in size. -- Regards Rick XP Pro Office 2007 "JLGWhiz" wrote: See if changing to this Destination will work. Selection.AutoFill Destination:=Range("A6:A" & Sheets.Count), Type:=xlFillDefault "Rick S." wrote: I am trying to figure out how to number cells in a range in a numerical ascending order. By recording a macro I can see how its done on one sheet. '===== Range("A6").Select ActiveCell.FormulaR1C1 = "1" Selection.AutoFill Destination:=Range("A6:A26"), Type:=xlFillDefault '===== If I change ActiveCell.FormulaR1C1 = "1" to ActiveCell.FormulaR1C1 = MyVar the numbering sequence stops and only repeats the value of MyVar in each cell. The reason I need a variable is I wont know the number of sheets in the workbook and I need to number the cells 1 thru 21 and adding 21 for each sheet to continue numbering. -- Regards Rick XP Pro Office 2007 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Numbering cells in a range
Rick, do you mean you need 21 rows per sheet? If so then modify the line to
this: Selection.AutoFill Destination:=Range("A6:A" & Sheets.Count * 21), Type:=xlFillDefault "Rick S." wrote: I am trying to figure out how to number cells in a range in a numerical ascending order. By recording a macro I can see how its done on one sheet. '===== Range("A6").Select ActiveCell.FormulaR1C1 = "1" Selection.AutoFill Destination:=Range("A6:A26"), Type:=xlFillDefault '===== If I change ActiveCell.FormulaR1C1 = "1" to ActiveCell.FormulaR1C1 = MyVar the numbering sequence stops and only repeats the value of MyVar in each cell. The reason I need a variable is I wont know the number of sheets in the workbook and I need to number the cells 1 thru 21 and adding 21 for each sheet to continue numbering. -- Regards Rick XP Pro Office 2007 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Numbering cells in a range
That won't work either. You will need to create a variable up front like
rng = (Sheets.Count * 21) + 6 Then use: Selection.AutoFill Destination:=Range("A6:A" & rng), Type:=xlFillDefault Now it will accomodate the starting point on A6 and autofill 21 rows for every sheet. "Rick S." wrote: I am trying to figure out how to number cells in a range in a numerical ascending order. By recording a macro I can see how its done on one sheet. '===== Range("A6").Select ActiveCell.FormulaR1C1 = "1" Selection.AutoFill Destination:=Range("A6:A26"), Type:=xlFillDefault '===== If I change ActiveCell.FormulaR1C1 = "1" to ActiveCell.FormulaR1C1 = MyVar the numbering sequence stops and only repeats the value of MyVar in each cell. The reason I need a variable is I wont know the number of sheets in the workbook and I need to number the cells 1 thru 21 and adding 21 for each sheet to continue numbering. -- Regards Rick XP Pro Office 2007 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Numbering cells in a range
This creates a range larger than I need, at least that is how I see it.
"rng = (Sheets.Count * 21) + 6" (what does "+6" do?) "Selection.AutoFill Destination:=Range("A6:A" & rng), Type:=xlFillDefault" Any cells beyond A6 or A26 may be merged cells in my file and the above statement creates 46 rows or a range of A6:A52 and will error out. I am failing to create the logic for numbering per sheet. My line of thought is the range needs to remain a constant, A6:A26, and the line to start the counting, ActiveCell.Value = "" & rng & "", needs to be repaired? Corrected? JLG, please do not take this out of context, sometimes you guru's confuse the hell out of me and I may not see what you are doing. Once the cell A26 has been reached I need to switch sheets and begin counting with the next available value, IE, 22, 23, 24, etc; once again in A6 thru A26 etc. -- Regards Rick XP Pro Office 2007 "JLGWhiz" wrote: That won't work either. You will need to create a variable up front like rng = (Sheets.Count * 21) + 6 Then use: Selection.AutoFill Destination:=Range("A6:A" & rng), Type:=xlFillDefault Now it will accomodate the starting point on A6 and autofill 21 rows for every sheet. "Rick S." wrote: I am trying to figure out how to number cells in a range in a numerical ascending order. By recording a macro I can see how its done on one sheet. '===== Range("A6").Select ActiveCell.FormulaR1C1 = "1" Selection.AutoFill Destination:=Range("A6:A26"), Type:=xlFillDefault '===== If I change ActiveCell.FormulaR1C1 = "1" to ActiveCell.FormulaR1C1 = MyVar the numbering sequence stops and only repeats the value of MyVar in each cell. The reason I need a variable is I wont know the number of sheets in the workbook and I need to number the cells 1 thru 21 and adding 21 for each sheet to continue numbering. -- Regards Rick XP Pro Office 2007 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Numbering cells in a range
I have managed to get this to iterate thru all sheets, regardless of count,
and number the range of cells "1 thru 21" on EACH sheet. I am stuck on how to make this number in a continuous order per sheet. 1 thru 21, 22 thru 42, 43 thru 63 etc.. -- Regards Rick XP Pro Office 2007 "Rick S." wrote: I am trying to figure out how to number cells in a range in a numerical ascending order. By recording a macro I can see how its done on one sheet. '===== Range("A6").Select ActiveCell.FormulaR1C1 = "1" Selection.AutoFill Destination:=Range("A6:A26"), Type:=xlFillDefault '===== If I change ActiveCell.FormulaR1C1 = "1" to ActiveCell.FormulaR1C1 = MyVar the numbering sequence stops and only repeats the value of MyVar in each cell. The reason I need a variable is I wont know the number of sheets in the workbook and I need to number the cells 1 thru 21 and adding 21 for each sheet to continue numbering. -- Regards Rick XP Pro Office 2007 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Numbering cells in a range
Rick S,
There are 3 ways that you can use. 1. A static variable that is incremented each pass thru the code 2. A module level variable that is incremented each pass thru the code 3. A worksheet cell the is incremented each pass thru the code. This will provide you with consecutive numbering for as many worksheets that you need. Ron "Rick S." wrote: I have managed to get this to iterate thru all sheets, regardless of count, and number the range of cells "1 thru 21" on EACH sheet. I am stuck on how to make this number in a continuous order per sheet. 1 thru 21, 22 thru 42, 43 thru 63 etc.. -- Regards Rick XP Pro Office 2007 "Rick S." wrote: I am trying to figure out how to number cells in a range in a numerical ascending order. By recording a macro I can see how its done on one sheet. '===== Range("A6").Select ActiveCell.FormulaR1C1 = "1" Selection.AutoFill Destination:=Range("A6:A26"), Type:=xlFillDefault '===== If I change ActiveCell.FormulaR1C1 = "1" to ActiveCell.FormulaR1C1 = MyVar the numbering sequence stops and only repeats the value of MyVar in each cell. The reason I need a variable is I wont know the number of sheets in the workbook and I need to number the cells 1 thru 21 and adding 21 for each sheet to continue numbering. -- Regards Rick XP Pro Office 2007 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Numbering cells in a range
VBA.Noob.Confused
I ended up programming for up to 20 sheets max. '====== Sub test() Sheets("Sheet1").Select For i = 1 To Worksheets.Count Sheets(i).Select '=======' good for 20 sheets If Sheets(i).Name = "Sheet1" Then x = 1 GoTo xSheet End If If Sheets(i).Name = "Sheet2" Then x = 22 GoTo xSheet End If If Sheets(i).Name = "Sheet3" Then x = 43 GoTo xSheet End If If Sheets(i).Name = "Sheet4" Then x = 64 GoTo xSheet End If If Sheets(i).Name = "Sheet5" Then x = 85 GoTo xSheet End If If Sheets(i).Name = "Sheet6" Then x = 106 GoTo xSheet End If If Sheets(i).Name = "Sheet7" Then x = 127 GoTo xSheet End If If Sheets(i).Name = "Sheet8" Then x = 148 GoTo xSheet End If If Sheets(i).Name = "Sheet9" Then x = 169 GoTo xSheet End If If Sheets(i).Name = "Sheet10" Then x = 190 GoTo xSheet End If If Sheets(i).Name = "Sheet11" Then x = 211 GoTo xSheet End If If Sheets(i).Name = "Sheet12" Then x = 232 GoTo xSheet End If If Sheets(i).Name = "Sheet13" Then x = 253 GoTo xSheet End If If Sheets(i).Name = "Sheet14" Then x = 274 GoTo xSheet End If If Sheets(i).Name = "Sheet15" Then x = 295 GoTo xSheet End If If Sheets(i).Name = "Sheet16" Then x = 316 GoTo xSheet End If If Sheets(i).Name = "Sheet17" Then x = 337 GoTo xSheet End If If Sheets(i).Name = "Sheet18" Then x = 358 GoTo xSheet End If If Sheets(i).Name = "Sheet19" Then x = 379 GoTo xSheet End If If Sheets(i).Name = "Sheet20" Then x = 400 GoTo xSheet End If '========== xSheet: 'MsgBox Sheets(i).Name 'for testing Rows("6:6").Select ActiveWindow.FreezePanes = True Range("A6").Select ActiveCell.Value = "" & x & "" Selection.AutoFill Destination:=Range("A6:A26"), Type:=xlFillDefault Next End Sub '====== Thanks to all who have helped!!! -- Regards Rick XP Pro Office 2007 "ron" wrote: Rick S, There are 3 ways that you can use. 1. A static variable that is incremented each pass thru the code 2. A module level variable that is incremented each pass thru the code 3. A worksheet cell the is incremented each pass thru the code. This will provide you with consecutive numbering for as many worksheets that you need. Ron "Rick S." wrote: I have managed to get this to iterate thru all sheets, regardless of count, and number the range of cells "1 thru 21" on EACH sheet. I am stuck on how to make this number in a continuous order per sheet. 1 thru 21, 22 thru 42, 43 thru 63 etc.. -- Regards Rick XP Pro Office 2007 "Rick S." wrote: I am trying to figure out how to number cells in a range in a numerical ascending order. By recording a macro I can see how its done on one sheet. '===== Range("A6").Select ActiveCell.FormulaR1C1 = "1" Selection.AutoFill Destination:=Range("A6:A26"), Type:=xlFillDefault '===== If I change ActiveCell.FormulaR1C1 = "1" to ActiveCell.FormulaR1C1 = MyVar the numbering sequence stops and only repeats the value of MyVar in each cell. The reason I need a variable is I wont know the number of sheets in the workbook and I need to number the cells 1 thru 21 and adding 21 for each sheet to continue numbering. -- Regards Rick XP Pro Office 2007 |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Numbering cells in a range
I guess I did not understand what you wanted.
"Rick S." wrote: VBA.Noob.Confused I ended up programming for up to 20 sheets max. '====== Sub test() Sheets("Sheet1").Select For i = 1 To Worksheets.Count Sheets(i).Select '=======' good for 20 sheets If Sheets(i).Name = "Sheet1" Then x = 1 GoTo xSheet End If If Sheets(i).Name = "Sheet2" Then x = 22 GoTo xSheet End If If Sheets(i).Name = "Sheet3" Then x = 43 GoTo xSheet End If If Sheets(i).Name = "Sheet4" Then x = 64 GoTo xSheet End If If Sheets(i).Name = "Sheet5" Then x = 85 GoTo xSheet End If If Sheets(i).Name = "Sheet6" Then x = 106 GoTo xSheet End If If Sheets(i).Name = "Sheet7" Then x = 127 GoTo xSheet End If If Sheets(i).Name = "Sheet8" Then x = 148 GoTo xSheet End If If Sheets(i).Name = "Sheet9" Then x = 169 GoTo xSheet End If If Sheets(i).Name = "Sheet10" Then x = 190 GoTo xSheet End If If Sheets(i).Name = "Sheet11" Then x = 211 GoTo xSheet End If If Sheets(i).Name = "Sheet12" Then x = 232 GoTo xSheet End If If Sheets(i).Name = "Sheet13" Then x = 253 GoTo xSheet End If If Sheets(i).Name = "Sheet14" Then x = 274 GoTo xSheet End If If Sheets(i).Name = "Sheet15" Then x = 295 GoTo xSheet End If If Sheets(i).Name = "Sheet16" Then x = 316 GoTo xSheet End If If Sheets(i).Name = "Sheet17" Then x = 337 GoTo xSheet End If If Sheets(i).Name = "Sheet18" Then x = 358 GoTo xSheet End If If Sheets(i).Name = "Sheet19" Then x = 379 GoTo xSheet End If If Sheets(i).Name = "Sheet20" Then x = 400 GoTo xSheet End If '========== xSheet: 'MsgBox Sheets(i).Name 'for testing Rows("6:6").Select ActiveWindow.FreezePanes = True Range("A6").Select ActiveCell.Value = "" & x & "" Selection.AutoFill Destination:=Range("A6:A26"), Type:=xlFillDefault Next End Sub '====== Thanks to all who have helped!!! -- Regards Rick XP Pro Office 2007 "ron" wrote: Rick S, There are 3 ways that you can use. 1. A static variable that is incremented each pass thru the code 2. A module level variable that is incremented each pass thru the code 3. A worksheet cell the is incremented each pass thru the code. This will provide you with consecutive numbering for as many worksheets that you need. Ron "Rick S." wrote: I have managed to get this to iterate thru all sheets, regardless of count, and number the range of cells "1 thru 21" on EACH sheet. I am stuck on how to make this number in a continuous order per sheet. 1 thru 21, 22 thru 42, 43 thru 63 etc.. -- Regards Rick XP Pro Office 2007 "Rick S." wrote: I am trying to figure out how to number cells in a range in a numerical ascending order. By recording a macro I can see how its done on one sheet. '===== Range("A6").Select ActiveCell.FormulaR1C1 = "1" Selection.AutoFill Destination:=Range("A6:A26"), Type:=xlFillDefault '===== If I change ActiveCell.FormulaR1C1 = "1" to ActiveCell.FormulaR1C1 = MyVar the numbering sequence stops and only repeats the value of MyVar in each cell. The reason I need a variable is I wont know the number of sheets in the workbook and I need to number the cells 1 thru 21 and adding 21 for each sheet to continue numbering. -- Regards Rick XP Pro Office 2007 |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Numbering cells in a range
None the less, your involvment is appreciated!
-- Regards Rick XP Pro Office 2007 "JLGWhiz" wrote: I guess I did not understand what you wanted. "Rick S." wrote: VBA.Noob.Confused I ended up programming for up to 20 sheets max. '====== Sub test() Sheets("Sheet1").Select For i = 1 To Worksheets.Count Sheets(i).Select '=======' good for 20 sheets If Sheets(i).Name = "Sheet1" Then x = 1 GoTo xSheet End If If Sheets(i).Name = "Sheet2" Then x = 22 GoTo xSheet End If If Sheets(i).Name = "Sheet3" Then x = 43 GoTo xSheet End If If Sheets(i).Name = "Sheet4" Then x = 64 GoTo xSheet End If If Sheets(i).Name = "Sheet5" Then x = 85 GoTo xSheet End If If Sheets(i).Name = "Sheet6" Then x = 106 GoTo xSheet End If If Sheets(i).Name = "Sheet7" Then x = 127 GoTo xSheet End If If Sheets(i).Name = "Sheet8" Then x = 148 GoTo xSheet End If If Sheets(i).Name = "Sheet9" Then x = 169 GoTo xSheet End If If Sheets(i).Name = "Sheet10" Then x = 190 GoTo xSheet End If If Sheets(i).Name = "Sheet11" Then x = 211 GoTo xSheet End If If Sheets(i).Name = "Sheet12" Then x = 232 GoTo xSheet End If If Sheets(i).Name = "Sheet13" Then x = 253 GoTo xSheet End If If Sheets(i).Name = "Sheet14" Then x = 274 GoTo xSheet End If If Sheets(i).Name = "Sheet15" Then x = 295 GoTo xSheet End If If Sheets(i).Name = "Sheet16" Then x = 316 GoTo xSheet End If If Sheets(i).Name = "Sheet17" Then x = 337 GoTo xSheet End If If Sheets(i).Name = "Sheet18" Then x = 358 GoTo xSheet End If If Sheets(i).Name = "Sheet19" Then x = 379 GoTo xSheet End If If Sheets(i).Name = "Sheet20" Then x = 400 GoTo xSheet End If '========== xSheet: 'MsgBox Sheets(i).Name 'for testing Rows("6:6").Select ActiveWindow.FreezePanes = True Range("A6").Select ActiveCell.Value = "" & x & "" Selection.AutoFill Destination:=Range("A6:A26"), Type:=xlFillDefault Next End Sub '====== Thanks to all who have helped!!! -- Regards Rick XP Pro Office 2007 "ron" wrote: Rick S, There are 3 ways that you can use. 1. A static variable that is incremented each pass thru the code 2. A module level variable that is incremented each pass thru the code 3. A worksheet cell the is incremented each pass thru the code. This will provide you with consecutive numbering for as many worksheets that you need. Ron "Rick S." wrote: I have managed to get this to iterate thru all sheets, regardless of count, and number the range of cells "1 thru 21" on EACH sheet. I am stuck on how to make this number in a continuous order per sheet. 1 thru 21, 22 thru 42, 43 thru 63 etc.. -- Regards Rick XP Pro Office 2007 "Rick S." wrote: I am trying to figure out how to number cells in a range in a numerical ascending order. By recording a macro I can see how its done on one sheet. '===== Range("A6").Select ActiveCell.FormulaR1C1 = "1" Selection.AutoFill Destination:=Range("A6:A26"), Type:=xlFillDefault '===== If I change ActiveCell.FormulaR1C1 = "1" to ActiveCell.FormulaR1C1 = MyVar the numbering sequence stops and only repeats the value of MyVar in each cell. The reason I need a variable is I wont know the number of sheets in the workbook and I need to number the cells 1 thru 21 and adding 21 for each sheet to continue numbering. -- Regards Rick XP Pro Office 2007 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| numbering cells | Excel Discussion (Misc queries) | |||
| Numbering a Range Using Autofilter | Excel Discussion (Misc queries) | |||
| Auto numbering cells | Excel Programming | |||
| Merged cells numbering | Excel Programming | |||
| How do I set up auto-numbering on cells? | Excel Programming | |||
Hybrid Mode
