# Sequential Number in Excel within Specified Range

I have been looking for the macro since last week but with no luck till yet. Would appreicate the efforts if you please get me out of the problem that i am facing, I have created excel template (used it for coding list).

What i am looking for is that:

In Column B we need to put serial numbers (sequential numbers) in activecell based on the value specified in a two cells say (A1 = start serial from : 5) & (A2 = End serial number: 25) What i need is in column B wherever the cell is active and start and end range is provided by me (user) and I click the macro button its generate the serial number with provided start and end range from activecell to downwards.

Hope you got my point.

-
Struggling with this and others as well...Just modified the code shared by chuff and commented the line ElseIf WorksheetFunction.CountA(Range("B:B")) <> 0 Then MsgBox "Please delete the existing values in column B." Exit Sub and added a line of code to unprotect sheet at the start of the code and at the end code ActiveWorksheets.Protect Password:= "12345" to protect sheet. – Syed Chowdhury Jul 23 '13 at 2:25

If I understand what you want, this macro should work for you. It checks to make sure that:

• the user has selected a cell in column B for the new series
• the end value for the series that the user has entered is greater than the start value
• existing values in column B have been cleared before the new series is inserted.

If any of those conditions are not met, the macro warns the user and exits without adding the new series to the worksheet.

Sub NewSeries2()
Dim seriesRng As Range
Dim stepSize As Double
Dim stopValue As Double

If Intersect(Selection, Range("B:B")) Is Nothing Then
MsgBox "Please select a cell in column B to start the series."
Exit Sub
ElseIf Range("A2").Value <= Range("A1").Value Then
MsgBox "Ending value of the series cell A2 must be greater then the starting value in cell A1."
Exit Sub
ElseIf WorksheetFunction.CountA(Range("B:B")) <> 0 Then
MsgBox "Please delete the existing values in column B."
Exit Sub
Else
stepSize = 1
stopValue = Range("A2").Value
Selection.Value = Range("A1").Value
Selection.DataSeries Rowcol:=xlColumns, Type:=xlLinear, Date:=xlDay, _
Step:=stepSize, Stop:=stopValue, Trend:=False
End If
End Sub
-
It is overwriting the existing serial number.if the serial is already in column then it should pop up message to inform user to delete the existing serial number first in order to generate new serial number. Secondly this macro is not validated if the start range is greater than end range. – Syed Chowdhury Jul 21 '13 at 15:10
Please see revised code, @Syad. – chuff Jul 21 '13 at 16:44
Syad, I corrected the problem with the message about column B. Please take another look if you'd like. – chuff Jul 21 '13 at 17:51
Sorry Cheff, The macro is not completely serves my purpose,it requires complete column B to be empty,Though i just want to ensure that from my activecell and its below rows there are no serial numbers which is being overwritten.If no serial is overwriting below then macro should run otherwise message box pop ups.Hope I am able to make you understand my point. – Syed Chowdhury Jul 21 '13 at 23:28
Furthermore the macro is not working on protected worksheet though I kept the entire column B unlocked in order to perform serialization but it is not allowing me – Syed Chowdhury Jul 22 '13 at 5:06