Formula for number sequence

NoSparks · #4 03-18-2018, 04:31 PM

Knowing you'll be using a button simplifies things.
Completely delete that previous event code from the sheet module and paste this into the button assignment.

Code:

Sub Button1_Click()

Dim i As Integer, str As String

'clear anything existing in N
Cells(4, "N").ClearContents
'make sure both cells have numbers
If Not IsNumeric(Cells(4, "L")) Or Not IsNumeric(Cells(4, "M")) Then Exit Sub
'make sure L cell is smaller than M cell
If Cells(4, "L") >= Cells(4, "M") Then Exit Sub
'put together what's to go into N
    For i = Cells(4, "L").Value To Cells(4, "M").Value
        str = str & ";" & i
    Next i
'write the string to N, remove the leading ;
Cells(4, "N").Value = Mid(str, 2)

End Sub

Macros that are Private do not show in the macro dialogue.
Neither do any with parameters within the brackets.
This is good to know because with no sheet names used code always uses the active sheet.
No problem when run from the button on the sheet but you probably don't want somebody accidentally running it from the macro dialogue when a different sheet is active.

Hope this helps.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Create a sequence of numbers using a formula	14spar15	Excel Programming	9	03-19-2018 01:15 AM
Formula Assistance for restricting the number of for analysis	gspikesr	Excel	3	05-02-2016 11:28 PM
Need an excel formula to remove dashes in a number sequence	jyfuller	Excel Programming	3	02-27-2013 10:32 PM
Formula to add number of pallets shipped	piglovindillard	Excel	2	08-13-2012 06:40 AM
Excel sheet Sequence Number	belloffice	Excel	1	02-25-2010 02:41 PM