Previous Topic Next Topic

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
Related:

6 responses

Answer 1 / 6
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
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
Raj
Updated on Aug 31, 2020 at 12:24 PM
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

Similar discussions

Excel - To generate unique ID
wliang -
ferg -

4 responses
How to generate a STATIC unique identifier for each Excel row
Musicar -
ac3mark -

4 responses
Auto generate serial number in excel based on Column
Raj_1562 -
TrowaD -

4 responses
Autogenerate alphanumeric ID numbers in Excel?
NoTechKnowledge64 -
vcoolio -

3 responses
Excel if range of cell contain a text put a cell value in
diegomagum -
TrowaD -

11 responses
If a cell contains any text then add a value
cbrizz -
Pharmanumbers -

20 responses