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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.