CODEWITHSUNDEEP
excelvba
mjhol
mjhol

Reputation: 61

Generate 1:N Sequence Array

I am currently trying to generate an array of sequential numbers (1:N) in order to populate a horizontal range ($C$6:N). When I use application.transpose my entire range is populated with 1, and when I don't use it the entire range is blank. I have attached my code below. pn is the range I want to populate and nop is the count of it. Thank you!

Best, M

pn.Value = Array(Application.WorksheetFunction.Sequence(1, nop.Value))

Upvotes: 0

Views: 546

Answers (2)

Gary's Student
Gary's Student

Reputation: 96771

To use SEQUENCE() within VBA to fill from B9 to M9:

Sub FillUsingSequence()
    Dim rng As Range
    Set rng = Range("B9:M9")
    rng.Value = Application.WorksheetFunction.Sequence(1, rng.Count, 1, 1)
End Sub

enter image description here

Upvotes: 1

jblack
jblack

Reputation: 576

I've put 2 options below,

Number one: (this seems to be what you want)

Sub generateSequence_MethodOne()

'Start at a cell and generate till a number

Dim pn As Range
Dim nop As Long

    Set pn = Range("C6")    'starting cell
    nop = 250   'number of entries

    With pn
        .value = 1
        .AutoFill Destination:=pn.Resize(, nop), Type:=xlFillSeries
    End With

'    'if you want to loop instead, then use this instead of the above
'    For i = 1 To nop
'        pn.Offset(, i - 1).value = i
'    Next

End Sub

and number two, use this if you have known range that you want to fill, not knowing until what number

Sub generateSequence_MethodTwo()

'set a range and fill it with a sequence

Dim cell As Range
Dim n As Long

    n = 1

    For Each cell In Range("C6:Z6").Cells   'known range to fill
        cell.value = n
        n = n + 1
    Next

End Sub

Upvotes: 2

Related Questions