Auto generate serial number in excel based on input value

Solved/Closed
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?

Thanks
Raj

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

Do
    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,
Trowa
2
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!

Regards,
Raj
0