CODEWITHSUNDEEP
excelvbaexcel-formula
Smith O.
Smith O.

Reputation: 217

How do I fill a series in VBA from a higher number to another?

I am trying to fill a series from a higher number in cell G2 (e.g "512") down to a lower number in Cell H2 (e.g "500"). I need the VBA code to run in Column J, producing the following this series as an example 512, 511, 510, 509, 508 ... down the column.

Here's a screenshot that describes what I need enter image description here

Most examples I've found seems to be built for an increasing series (512, 513, 514....)

Any help to get this right will be helpful

Upvotes: 1

Views: 148

Answers (3)

Bryan A. Pena-Cruz
Bryan A. Pena-Cruz

Reputation: 1

You can use the following code, it lets you add a a values to specify the steps you want to have.

https://i.sstatic.net/vSan5.jpg

Sub createNumList()
Dim i As Integer
Dim count As Integer
Dim cellValue As Integer

count = (ActiveSheet.Cells(2, 1).Value - ActiveSheet.Cells(2, 2).Value) / ActiveSheet.Cells(2, 3).Value

For i = 1 To count + 1
    If i = 1 Then
        ActiveSheet.Cells(i + 2, 5).Value = ActiveSheet.Cells(2, 1).Value
    Else
        ActiveSheet.Cells(i + 2, 5).Value = ActiveSheet.Cells(i + 1, 5).Value - ActiveSheet.Cells(2, 3).Value
    End If
Next i

End Sub

Upvotes: 0

chris neilsen
chris neilsen

Reputation: 53126

For a VBA general solution, try this

Sub CreateSequence(StartValue As Long, EndValue As Long, OutputStart As Range, Optional ByVal StepBy As Long = 1)
    Dim NumValues As Long
    Dim dat As Variant
    Dim i As Long
    
    StepBy = Abs(StepBy)
    If StepBy <= 0 Then Exit Sub
    NumValues = Abs(StartValue - EndValue) \ StepBy + 1
    ReDim dat(1 To NumValues, 1 To 1)
    
    For i = 0 To NumValues - 1
        dat(i + 1, 1) = StartValue + i * IIf(StartValue > EndValue, -StepBy, StepBy)
    Next
    
    OutputStart.Resize(UBound(dat, 1), 1).Value = dat
End Sub

Use it like this

Sub Demo()
    CreateSequence Range("G2").Value, Range("H2").Value, Range("K2")
End Sub

A formula solution (Excel version 365)

=SEQUENCE(G2-H2+1,1,G2,-1)

Upvotes: 1

Smith O.
Smith O.

Reputation: 217

hlRange = Sheets(2).Cells(2, 7).Value - Sheets(2).Cells(2, 8).Value + 1

' Fill series from max to min value
For j = 1 To hlRange
    Sheets(2).Cells(j + 1, 10).Value = Sheets(2).Cells(2, 7).Value - j + 1
Next j

Upvotes: 0

Related Questions