CODEWITHSUNDEEP
excelvba
Vasu Mistry
Vasu Mistry

Reputation: 841

Excel: Auto populate cell in incremental sequence till a given value

So I select an integer value from a dropdown, e.g 5. I want to populate the sequence 1,2,3,4,5 from cells C1:C5 automatically. So if I change the value from dropdown to 2, only cells C1 C2 should be populated with values 1 and 2

Here's the VBA function I wrote:

Function POPULATE_VALUE(GIVEN_VALUE, MAX_VALUE)
   If GIVEN_VALUE <= MAX_VALUE Then
        POPULATE_VALUE = GIVEN_VALUE
    Else
        POPULATE_VALUE = ""
   End If
End Function

Assuming cell B3 contains my dropdown selected value, I use it as:

For cell C1: =POPULATE_VALUE(1,B3), C2: =POPULATE_VALUE(2,B3)

Is there a better way to do this? I found the SEQUENCE function but it is only available in Office 365 for now.

Upvotes: 1

Views: 216

Answers (2)

pykam
pykam

Reputation: 1481

You can use the subroutine below.

Sub fill_numbers()
    ''clearing column C
    ThisWorkbook.Sheets("Sheet1").Columns(3).ClearContents
    
    max_value = ThisWorkbook.Sheets("Sheet1").Range("B3").Value
    For i = 1 To max_value
      ThisWorkbook.Sheets("Sheet1").Range("C" & i).Value = i
    Next i

End Sub

It just loops from 1 to the value of the dropdown and keeps filling the numbers in column C.

You can set the trigger to change everytime there is a change in the value of the cell that has the dropdown.

Upvotes: 2

Harun24hr
Harun24hr

Reputation: 36870

You can use below formula. Put the formula to C3 cell then drag and down till you need.

=IF(ROW($A1)>$B$3,"",ROW($A1))

enter image description here

Upvotes: 2

Related Questions