How to create an array with x numbers that increments from 1 to x, then use it in formula [VBA]?

[gravanoc]

My current need is for a flexible array as mentioned in the title, so I can insert it into a formula with VBA.

This is how it always starts:

=SMALL(myRange, {1, 2, 3, 4, 5, 6, 7, 8, 9, 10})

Next I would like for it to be the same except dropping the 10.

=SMALL(myRange, {1, 2, 3, 4, 5, 6, 7, 8, 9})

This continues until the iteration is using 1 by itself.

My current solution would be to use Select Case to determine which to go with; however, the other problem is that array variables aren't working when I insert them via otherRange.Formula2 = "...".
Specifically, I've tried setting a variable named Arr = Array(1, 2, 3,4...., 10), then putting it in:

otherRange.Formula2 = "=SMALL(" & myRange.Address & ", {" & Arr & "})"

Other variations are also not working.

Thanks.

 

[Eric W]

Since you have Excel 365, try:

otherRange.Formula2 = "=SMALL(" & myRange.Address & ", SEQUENCE(," & n & "))"

where n is the upper bound.

 

[Fluff]

Not entirely sure what your asking for, but maybe

OtherRange.Formula2 = "=SMALL(" & myrange.Address & ", sequence(" & myrange.Rows.Count & "))"

 

[gravanoc]

Since you have Excel 365, try:

otherRange.Formula2 = "=SMALL(" & myRange.Address & ", SEQUENCE(," & n & "))"

where n is the upper bound.

Thanks @Eric W and @Fluff - that works.

 

[Eric W]

Glad we could help.