Make a sequence between two numbers

[Robert Davidson]

Does anyone know how to make a sequence of number as follows:

Start between 1 & 15, increment either in 5 or 10 but always ending at 100?
For example Start at 5 and increment in 10s: 5,15,25,35,45,55,65,75,85,95,100
For some sequences it's easy starting at 5 and incrementing in 5s as it can always stop at 100 but how do I deal with the above situation where the balance between 95 and 100 is not the same as the increment?

 

[Legacy 143009]

Hi,

It's easy Assuming you have your first value in A1,
A2 formula will be:

=IF(A1+10>100,100,A1+10)

Drag down the formula.

 

[mumps]

Try this macro on a blank sheet.

Sub CreateSequence()
    Application.ScreenUpdating = False
    Dim x As Long, start As String, incr As String, lRow As Long
    start = InputBox("Enter the starting number.")
    If start = "" Then Exit Sub
    incr = InputBox("Enter the increment.")
    If incr = "" Then Exit Sub
    For x = start To 100 Step incr
        Cells(Rows.Count, "A").End(xlUp).Offset(1) = x
    Next x
    lRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    If Range("A" & lRow) <> "100" Then
        Cells(Rows.Count, "A").End(xlUp).Offset(1) = "100"
    End If
    Application.ScreenUpdating = True
End Sub

 

[Fluff]

How about

Fluff.xlsm
	A	B	C
1
2	Start	5	5
3	Increment	10	15
4			25
5			35
6			45
7			55
8			65
9			75
10			85
11			95
12			100
13
Sheet8

Cell Formulas
Range	Formula
C2:C12	C2	=LET(s,SEQUENCE(CEILING((101-B2)/B3,1),,B2,B3),IF(MAX(s)=100,s,VSTACK(s,100)))
Dynamic array formulas.

 

[Micron]

Wow, I need to refresh these pages sooner. Was going to say that nothing was mentioned about the output requirements but if OP wanted code, here's one that builds a string of numbers. Might as well post it. The key (for me) was to use a Do While loop so that the test could be made at the start of the loop. I didn't try to think about how to deal with the fact that in a For Next loop, the To value is static, which can make it not really useful in comparing sums.

Function Sequences(intStart As Integer, intIncrement As Integer) As String
Dim i As Integer

i = intStart
Sequences = intStart
Do While i + intIncrement < 100
    Sequences = Sequences & "," & i + intIncrement
    Debug.Print Sequences
    i = i + intIncrement
Loop
If Right(Sequences, 3) <> "100" Then
    Sequences = Sequences & ",100"
End If
Debug.Print Sequences
End Function

 

[Peter_SSs]

Another possible formula option.

24 01 09.xlsm
	A	B	C
1
2	Start	5	5
3	Increment	10	15
4			25
5			35
6			45
7			55
8			65
9			75
10			85
11			95
12			100
13
Sequence

Cell Formulas
Range	Formula
C2:C12	C2	=LET(s,SEQUENCE(20,,B2,B3),VSTACK(FILTER(s,s<100),100))
Dynamic array formulas.

 

[Robert Davidson]

Another possible formula option.

24 01 09.xlsm
	A	B	C
1
2	Start	5	5
3	Increment	10	15
4			25
5			35
6			45
7			55
8			65
9			75
10			85
11			95
12			100
13
Sequence

Cell Formulas
Range	Formula
C2:C12	C2	=LET(s,SEQUENCE(20,,B2,B3),VSTACK(FILTER(s,s<100),100))
Dynamic array formulas.

Thank you Peter, This one is my preferred solution. Robert

 

[Peter_SSs]

You're welcome. Glad we could help. Thanks for the follow-up.