Sequence Function

[MrDB4Excel]

I am not sure this forum is the right place to ask about this.
I use Excel 2013 and have been looking for a downloadable add-in of the sequence function.
Is there such an add-in?
If so where can it be found?

 

[Scott Huish]

I believe this UDF will duplicate that function:

Public Function Sequence(lngrows, Optional lngcolumns = 1, Optional start = 1, Optional Seqstep = 1) As Variant
Dim x, y, lngInc As Long, lngNumbers() As Long
ReDim lngNumbers(1 To lngrows, 1 To lngcolumns)
lngInc = start - 1
For x = 1 To lngrows
    For y = 1 To lngcolumns
        lngInc = lngInc + Seqstep
        lngNumbers(x, y) = lngInc
    Next
Next
Sequence = lngNumbers
End Functio

n

 

[MrDB4Excel]

I believe this UDF will duplicate that function:

Public Function Sequence(lngrows, Optional lngcolumns = 1, Optional start = 1, Optional Seqstep = 1) As Variant
Dim x, y, lngInc As Long, lngNumbers() As Long
ReDim lngNumbers(1 To lngrows, 1 To lngcolumns)
lngInc = start - 1
For x = 1 To lngrows
    For y = 1 To lngcolumns
        lngInc = lngInc + Seqstep
        lngNumbers(x, y) = lngInc
    Next
Next
Sequence = lngNumbers
End Functio

n

Not real sure how to get this to work.
On another note, once I saw a Youtube about the Xlookup function and discovered after a search that the Xlookup function is not available for Excel 2013's out of the box setup but is available as an add-in. This is why I asked in the first place if the sequence function might be available as an add-in.
Surely there must be such somewhere.

 

[Scott Huish]

Sorry the n was outside my Code block:

Public Function Sequence(lngrows, Optional lngcolumns = 1, Optional start = 1, Optional Seqstep = 1) As Variant
Dim x, y, lngInc As Long, lngNumbers() As Long
ReDim lngNumbers(1 To lngrows, 1 To lngcolumns)
lngInc = start - 1
For x = 1 To lngrows
    For y = 1 To lngcolumns
        lngInc = lngInc + Seqstep
        lngNumbers(x, y) = lngInc
    Next
Next
Sequence = lngNumbers
End Function

In a blank workbook, right click on the tab name and click on View Code.
This will take you to the Visual Basic Editor (VBE)
Copy the code and paste into white area.
Hit Alt-Q
Save workbook as type .xlam to save as an add-in
Then you can load this as an add-in.

 

[MrDB4Excel]

Sorry the n was outside my Code block:

Public Function Sequence(lngrows, Optional lngcolumns = 1, Optional start = 1, Optional Seqstep = 1) As Variant
Dim x, y, lngInc As Long, lngNumbers() As Long
ReDim lngNumbers(1 To lngrows, 1 To lngcolumns)
lngInc = start - 1
For x = 1 To lngrows
    For y = 1 To lngcolumns
        lngInc = lngInc + Seqstep
        lngNumbers(x, y) = lngInc
    Next
Next
Sequence = lngNumbers
End Function

In a blank workbook, right click on the tab name and click on View Code.
This will take you to the Visual Basic Editor (VBE)
Copy the code and paste into white area.
Hit Alt-Q
Save workbook as type .xlam to save as an add-in
Then you can load this as an add-in.

Having trouble getting this macro do what I need it to do. I caught on to this sequence function via this Youtube: https://www.youtube.com/watch?v=jPYVC29kNO8
When I get to the place in the video that shows this function: =sequence(7,7,B4,1) then it does not do what the video is showing.

 

[Scott Huish]

I wasn't actually familiar with it. I looked it up and this is what Microsoft said the function did and that's what I tried to replicate.

https://support.office.com/en-us/article/SEQUENCE-function-57467a98-57e0-4817-9f14-2eb78519ca90

 

[Scott Huish]

I didn't go through the whole video, but select your range.
Put the formula in the formula bar and hit CTRL-SHIFT-ENTER, this represents the sheet from the first part of the video.

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K
1	Date	7/5/2019			Sun	Mon	Tue	Wed	Thu	Fri	Sat
2	First of Month	7/1/2019			-7	-6	-5	-4	-3	-2	-1
3	Weekday	2			0	1	2	3	4	5	6
4	Offset	-7			7	8	9	10	11	12	13
5					14	15	16	17	18	19	20
6					21	22	23	24	25	26	27
7					28	29	30	31	32	33	34
8					35	36	37	38	39	40	41
Sheet1

Cell Formulas
Range	Formula
B2	=EOMONTH(B1,-1)+1
B3	=WEEKDAY(B2)
B4	=-(B3+5)
E2:K8	{=sequence(7,7,B4,1)}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[MrDB4Excel]

I wasn't actually familiar with it. I looked it up and this is what Microsoft said the function did and that's what I tried to replicate.

https://support.office.com/en-us/article/SEQUENCE-function-57467a98-57e0-4817-9f14-2eb78519ca90

Thanks Scott for all your help. Unfortunately I think the sequence function is unavailable for Excel 2013.

 

[Scott Huish]

That's why I wrote my own version of it. See above for instructions. I ran this in Excel 2010 with the UDF above.
As well as instructions on how you can make it an add-in.