|
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
Numbering cells in a range
On 11$B7n(B19$BF|(B, $B2<8a(B9$B;~(B56$BJ,(B, Rick S. wrote:
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- $Bp,i6Ho0zMQJ8;z(B - - $Bp}<(Ho0zMQJ8;z(B - Renumber base on selected range. Sub PerRenumber() '~~ 2007/11/14 cxleung Dim iRows As Long Dim iCols As Long Dim iR, iC As Long Dim iNum As Long Dim iText, iChange As Long If Selection Is Nothing Then MsgBox "No Open Worksheet", vbCritical Exit Sub End If iRows = Selection.Rows.Count iCols = Selection.Columns.Count iNum = MsgBox("Renumber... 10,20,...,n", vbYesNo + vbQuestion) If iNum = vbNo Then Exit Sub iNum = 0 iChange = 0 iText = 0 For iC = 1 To iCols For iR = 1 To iRows If VBA.IsNumeric(Selection.Item(iR, iC).Value) Then If iNum < Selection.Item(iR, iC).Value Then iChange = iChange + 1 iNum = iNum + 10 Selection.Item(iR, iC).Value = iNum End If Else iText = iText + 1 End If Next iR Next iC If iText 0 Then MsgBox "Number of change = " & iChange & Chr(13) & _ "Number of text found = " & iText End If End Sub |
| All times are GMT +1. The time now is 03:45 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com