Numbering cells in a range

R

Rick S.

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
 
J

JLGWhiz

See if changing to this Destination will work.

Selection.AutoFill Destination:=Range("A6:A" & Sheets.Count),
Type:=xlFillDefault
 
J

JLGWhiz

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
 
J

JLGWhiz

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.
 
R

Rick S.

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
 
R

Rick S.

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
 
R

Rick S.

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
 
R

ron

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
 
R

Rick S.

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
 
J

JLGWhiz

I guess I did not understand what you wanted.

Rick S. said:
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
Click to expand...
 
M

moonhk

None the less, your involvment is appreciated!
--
Regards

Rick
XP Pro
Office 2007






- $Bp}<(Ho0zMQJ8;z(B -
Click to expand...

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Paragraph numbering in Word 1
Where am I going wrong? Cells reference to a range appears to havestopped working! 5
Checkbox linked to calcs 0
Checkbox linked to calcs 1
Section and Page numbering 0
Adding sequence numbers to paragraphs 0
macro to copy rows that contain a cell with a certain number 3
random range of cells via cell names 3

Top