Menu
  1. Accueil
  2. Tech Forum
  3. Office Software
  4. Excel

Auto generate serial number in excel based on input value [Solved]

Report
-
 Raj -
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 replies

Answer 1 / 6
Posts
2717
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
April 1, 2021
 458
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
Comment
2
Thank you
Thank you

Glad we were able to help! Love us? Write us a review! Rate CCM

CCM 2942 users have said thank you to us this month

Report

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
Answer 2 / 6
Posts
2717
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
April 1, 2021
 458
Awesome! Glad I could help.
Report

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
Report
Posts
2717
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
April 1, 2021
 458 > Raj
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
Report
>
Posts
2717
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
April 1, 2021
Thanks Trowa for your reply.

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

Thanks ????.

Regards,
Raj
Report
>
Posts
2717
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
April 1, 2021
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
Answer 3 / 6
Posts
2717
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
April 1, 2021
 458
Hi Raj,

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

Best regards,
Trowa
Answer 4 / 6
Posts
22
Registration date
Wednesday August 26, 2020
Status
Member
Last seen
January 18, 2021
Hi Trowa,

Thank you so much for your reply on this!

Ragards,
Raj
Answer 5 / 6
Posts
22
Registration date
Wednesday August 26, 2020
Status
Member
Last seen
January 18, 2021
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

Report
Posts
22
Registration date
Wednesday August 26, 2020
Status
Member
Last seen
January 18, 2021
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

Report
Posts
2717
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
April 1, 2021
 458 >
Posts
22
Registration date
Wednesday August 26, 2020
Status
Member
Last seen
January 18, 2021
Good that you created a separate thread for that request:
https://ccm.net/forum/affich-1119616-how-to-copy-the-column-value-into-rows
Report
>
Posts
2717
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
April 1, 2021
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
Answer 6 / 6
Posts
1
Registration date
Monday September 7, 2020
Status
Member
Last seen
September 17, 2020
Thanks for the clarification.
Reply Ask a question

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!