CODEWITHSUNDEEP
excelvba
Hjalti Sigtryggsson
Hjalti Sigtryggsson

Reputation: 35

Excel: Dynamic list of numbers based on input

I'm trying to make a list of numbers based on a hard coded input.

let say if I write "5" in A1, then I want a list of numbers 1,2,3,4,5 in a column below. If the input is 25 the list would increase to 1-25 and so on....

Can anyone help me with this ?

Best regards

Upvotes: 1

Views: 1483

Answers (3)

VBasic2008
VBasic2008

Reputation: 54838

Numbered

Features

  • When the value in Source Cell Range is changed by user input or via VBA, the program is triggered. It will not run if Source Cell Range contains a formula and the value has changed due to another cell changing. For that functionality you have to use the Worksheet Calculate event.
  • If the value is not a number, nothing will happen.
  • If the value is a decimal number (1.5) it will be rounded to the nearest integer (2).
  • If the value exceeds the number of rows in the worksheet, the worksheet will be filled to the bottom cell. The rest of the values will be ignored.
  • Change the Source Cell Range Address in Worksheet_Change (set to A1) to fit your needs e.g. C17. Only the cells below will be affected.

The Code

Copy the following code to a standard module (VBE >> Insert >> Module) e.g.

Module1 

Option Explicit

Sub Numbered(CellRange As Range)

    Dim vntT As Variant          ' Target Array/Value
    Dim srcVal As Variant        ' Value
    Dim srcMax As Long           ' Maximum Value
    Dim srcSgn As Long           ' Sign (+-)
    Dim srcAbs As Long           ' Absolute Value
    Dim i As Long                ' Target Array Row Counter

    ' In Cell Range
    With CellRange
        ' Write value of CellRange to Value.
        srcVal = .Value
        ' Calculate Maximum Value.
        srcMax = .Worksheet.Rows.Count - .Offset(1).Row + 1
    End With

    ' Check if Value is a number.
    If IsNumeric(srcVal) Then
        ' Convert Value to whole number.
        srcVal = CLng(srcVal)
        ' Write the sign of Value to Sign.
        srcSgn = Sgn(srcVal)
        ' Check if the absolute value of Value is greater than Maximum Value.
        If Abs(srcVal) > srcMax Then
            ' Write Maximum Value with (correct) Sign to Value.
            srcVal = srcSgn * srcMax
        End If
        ' Write the absolute value of Value to Absolute Value.
        srcAbs = Abs(srcVal)
        ' Check Absolute Value
        Select Case srcAbs
            Case Is > 1
                ' Resize Target Array to Absolute Value rows and one column.
                ReDim vntT(1 To srcAbs, 1 To 1)
                ' Loop through rows of Target Array.
                For i = 1 To srcAbs
                    ' Write to element at i-th row and 1st column
                    ' of Target Array.
                    vntT(i, 1) = srcSgn * i
                Next
            Case 1
                ' If Absolute Value is 1, vntT will not be an array, but a
                ' variant containing one value.
                vntT = srcSgn * 1
            Case 0
                ' If Absolute Value is 0, vntT will not be an array, but a
                ' variant containing one value.
                vntT = 0 ' or ""
        End Select
    End If

    ' In First Cell of Target Range (Cell Below Cell Range)
    With CellRange.Offset(1)
        ' Resize to bottom cell and clear contents.
        .Resize(srcMax).ClearContents
        ' Check if vntT is an array.
        If IsArray(vntT) Then   ' Multiple values
            ' Calculate Target Range: Resize First Cell of Target Range by
            ' Absolute Value.
            ' Copy Target Array to Target Range.
            .Resize(srcAbs) = vntT
          Else                  ' One value
            ' Write Target Value to First Cell of Target Range.
            .Value = vntT
        End If
    End With

End Sub

Copy the following code into any sheet module where you want to run the program e.g.

Sheet1

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

    Const cSrc As String = "A1"  ' Source Cell Range Address

    If Target.Address = Range(cSrc).Address Then
        Numbered Target
    End If

End Sub

Upvotes: 1

cybernetic.nomad
cybernetic.nomad

Reputation: 6408

Using formulas:

in A2 use the following formula:

=IFERROR(IF(A1="","",1),"")

In A3 use:

=IFERROR(IF(A2+1>A$1,"",A2+1),"")

And populate down

Upvotes: 2

dwirony
dwirony

Reputation: 5450

You'll need a Worksheet_Change() event:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Address = "$A$1" Then

        Range("A2:A1048576").ClearContents

        For i = 1 To Target.Value
            Cells(i + 1, 1).Value = i
        Next i

    End If

End Sub

Upvotes: 2

Related Questions