Auto generate serial number in excel based on input value

Raj - Updated on Aug 26, 2020 at 11:26 AM
 Raj - Sep 7, 2020 at 11:33 AM
Dear Expert,

I want to auto generate the S.No in column A5 based on input value. I have to enter the No. of record in column C3(Ex: 3).The serial Number will copy auto column A5 (1),A6(2) and A6(3).

The No.Of record is user Input value.

Can you please on this via Excel formula or Vba?


6 responses

TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen January 16, 2023 549
Aug 31, 2020 at 11:56 AM
Hi Raj,

This confuses me: 'I would like to display the 3 combination in one'.

I interpreted this as you want the solution to work for all 3 scenarios.

The code below will fire once you make a change in cell G3:
- Column A, starting at row 6 will be cleared.
- Column A, starting at row 6 will be numbered to the amount entered in cell G3.
- Spacing between the numbers will be determined by how many cells in range C3:E3 contain data.

Here is the code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim mStep, NoR, x, y As Integer

If Intersect(Target, Range("G3")) Is Nothing Then Exit Sub

mStep = WorksheetFunction.CountA(Range("C3:E3"))
NoR = Target.Value
x = 6

Range("A6:A" & Rows.Count).ClearContents

    y = y + 1
    Cells(x, "A").Value = y
    x = x + mStep
    NoR = NoR - 1
Loop Until NoR = 0

End Sub

Using the code is done by right-clicking your sheets tab and select "View code". Paste the code in the big white field that pops up.

Best regards,
Hi Trowa,

There is no words to say congrats in this world to you!. because you are amazing! Keep Rocking!
Many congrats!

Note: This is exactly what I was expecting!