UDF for sequence function

dave2018

Board Regular
Joined
Mar 20, 2018
Messages
223
can anyone come up with a replica for the sequence function for Excel 2016?

not the type which spills over, but rather one you can use in 1 cell.


Lets say MyCustomNumber = 4

for example, instead of doing =ROWS(INDIRECT("A1:A"&MyCustomNumber)) to generate ={1;2;3;4} when evaluating [F9] in edit mode, i can use =SEQUENCE(MyCustomNumber,1) -i think that's the syntax- to generate ={1,2,3,4}.

I hope i'm clear enough :).
thx
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
I think this UDF (named ArrSeq) will do what you asked for. It has the same syntax as Excel's SEQUENCE function...

=ArrSeq(Rows, Columns, Start, Step)

It will recreate an array of rows and columns starting with the indicated number and incrementing by the Step value. Note the last three arguments are optional and each one defaults to 1.
Code:
[table="width: 500"]
[tr]
	[td]Function ArrSeq(lRows As Long, Optional lCols As Long = 1, Optional lStart As Long = 1, Optional lStep As Long = 1) As Variant
  Dim R As Long, C As Long, Nums As Long
  Nums = lStart
  For R = 1 To lRows
    For C = 1 To lCols
      ArrSeq = ArrSeq & "," & Nums
      Nums = Nums + lStep
    Next
    ArrSeq = ArrSeq & ";"
  Next
  ArrSeq = Evaluate("{" & Replace(Mid(Left(ArrSeq, Len(ArrSeq) - 1), 2), ";,", ";") & "}")
End Function[/td]
[/tr]
[/table]
 
Upvote 0
but it looks like the most it can do is 87? if i put 88 in the first argument, it returns #VALUE .

any idea? maybe a constraining limit in the Variant?
 
Upvote 0
but it looks like the most it can do is 87? if i put 88 in the first argument, it returns #VALUE .

any idea? maybe a constraining limit in the Variant?
The constraining limit was in the Evaluate function (its text argument can only be a maximum of 255 characters). Here is a replacement that will handle a larger range of values; however, Excel has a limit for the length of its formulas of 8192 characters total which, as far as I know, cannot be worked around, so you will have to watch out for that if you start to specify large numbers of rows and columns.
Code:
[table="width: 500"]
[tr]
	[td]Function ArrSeq(lRows As Long, Optional lCols As Long = 1, Optional lStart As Long = 1, Optional lStep As Long = 1) As Variant
  Dim R As Long, C As Long, SeqNum As Long, Nums As Variant
  ReDim Nums(1 To lRows, 1 To lCols)
  SeqNum = lStart
  For R = 1 To lRows
    For C = 1 To lCols
      Nums(R, C) = SeqNum
      SeqNum = SeqNum + lStep
    Next
  Next
  ArrSeq = Nums
End Function[/td]
[/tr]
[/table]
.
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,324
Members
452,635
Latest member
laura12345

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top