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 replies

Answer 1 / 6
TrowaD Posts 2900 Registration date Sunday September 12, 2010 Status Moderator Last seen September 12, 2022 523
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
Answer 2 / 6
TrowaD Posts 2900 Registration date Sunday September 12, 2010 Status Moderator Last seen September 12, 2022 523
Sep 1, 2020 at 11:16 AM
Awesome! Glad I could help.
1
Raj
Sep 3, 2020 at 05:55 AM
Hi TrowaD,

I have a concern!

When I enter the No.of Record(G3). It takes a while to update the value in the No.of Record(G3).

Kindly help me to reduce the time.because i'm waiting for that time to update the next cell value in excel sheet.

Regards,
Raj
0
TrowaD Posts 2900 Registration date Sunday September 12, 2010 Status Moderator Last seen September 12, 2022 523 > Raj
Sep 3, 2020 at 11:48 AM
Hi Raj,

I entered a value of 32000 in G3 and the code takes about a second to run. I don't think you would consider that slow, right? So there must be something else going on in your workbook that slows the process down. I can't tell you what that is without having a look.

Consider uploading your workbook to a free filesharing site and then post back the download link here. Always be carefull with sensitive data.

Best regards,
Trowa
0
Raj > TrowaD Posts 2900 Registration date Sunday September 12, 2010 Status Moderator Last seen September 12, 2022
Sep 3, 2020 at 12:12 PM
Thanks Trowa for your reply.

I'll check in empty file the logic and let you know you.

Thanks ????.

Regards,
Raj
0
Raj > TrowaD Posts 2900 Registration date Sunday September 12, 2010 Status Moderator Last seen September 12, 2022
Sep 4, 2020 at 10:15 AM
Hi Trowa,

Hope you are doing good ????!

The issue in my system.after clear the temp and cookies from the system.the value getting update quickly.

Sorry for the inconveniences!

Regards,
Raj
0
Answer 3 / 6
TrowaD Posts 2900 Registration date Sunday September 12, 2010 Status Moderator Last seen September 12, 2022 523
Aug 27, 2020 at 11:49 AM
Hi Raj,

I find it difficult to understand you. Can you give an example to clarify?

Best regards,
Trowa
0
Answer 4 / 6
Raj_1562 Posts 31 Registration date Wednesday August 26, 2020 Status Member Last seen August 29, 2022
Updated on Aug 27, 2020 at 12:20 PM
Hi Trowa,

Thank you so much for your reply on this!

Ragards,
Raj
0

Didn't find the answer you are looking for?

Ask a question
Answer 5 / 6
Raj_1562 Posts 31 Registration date Wednesday August 26, 2020 Status Member Last seen August 29, 2022
Updated on Aug 27, 2020 at 12:20 PM
Hi Trowa,

Thank you so much for your reply on this!

Sure! I have attached the screenshot of the request.I would like to display the 3 combination in one ( I differencient the 3 combination for your understanding).

Hope it will help you to understand my expectation.

Thanks in advance!

Ragards,
Raj

0
Raj_1562 Posts 31 Registration date Wednesday August 26, 2020 Status Member Last seen August 29, 2022
Updated on Sep 4, 2020 at 10:29 PM
Hi Trowa,

I need one more help from you!

I want to add one more condition from the existing.

attached the screenshot for your understanding (I would like to display the 3 combination in one)

I would like to copy the teams name in teams column.

Thanks in advance!

Regards,
Raj

0
TrowaD Posts 2900 Registration date Sunday September 12, 2010 Status Moderator Last seen September 12, 2022 523 > Raj_1562 Posts 31 Registration date Wednesday August 26, 2020 Status Member Last seen August 29, 2022
Sep 7, 2020 at 11:27 AM
Good that you created a separate thread for that request:
https://ccm.net/forum/affich-1119616-how-to-copy-the-column-value-into-rows
0
Raj > TrowaD Posts 2900 Registration date Sunday September 12, 2010 Status Moderator Last seen September 12, 2022
Sep 7, 2020 at 11:33 AM
Yes TrowaD,

Because the old thread is solved. So, I have created a new. Can you please help me on that.because you know very well ????.

Regards,
Raj
0
Answer 6 / 6
dhanushx012 Posts 1 Registration date Monday September 7, 2020 Status Member Last seen September 17, 2020
Sep 7, 2020 at 04:07 AM
Thanks for the clarification.
0