Menu
Ask a question Report

Auto incrementing alphanumeric cell by 1 [Solved/Closed]

- Apr 15, 2011 at 12:23 PM - Latest reply:  suryam - Apr 29, 2011 at 12:43 AM
Office Software Ask a question Report
Hello,
I have an alpha numeric cell in excel sheet. for example IND/APR/6734/INV
I am using it to give number to the invoce that I generate everyday. once I finish the printing and saving the invoice , new invoice will come. I am manually incrementing the number in the third portion of the field. for example next number 6735 etc..Is there any method to auto increment it so that I can get IND/APR/6735/INV ... when I use the invoice template the next time. please help me . thanks suryam


See more 

4 replies

Best answer
Report Respond to venkat1926
Posts
1865
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
 Apr 15, 2011 at 10:31 PM
2
Thank you
suppose this invoice number is in G1 in sheet1
in sheet 2 in G1 type this formula

=LEFT(Sheet1!G1,8)&MID(Sheet1!G1,9,4)+1&RIGHT(Sheet1!G1,4)

you will get

IND/APR/6735/INV

do something like this .

remember in May you have to change APR to MAY

Thank you, venkat1926 2

Something to say? Add comment

CCM has helped 1699 users this month

Best answer
Report Respond to venkat1926
Posts
1865
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
 Apr 27, 2011 at 06:16 AM
2
Thank you
you try this.

suppose you want to enter the correct invoice number in sheet 2
make sheet2 as active sheet
suppose the value of the invoice in all the sheets in G1

then try this macro
KEEP THE MSGBOX SO THAT YOU WILL BE SURE THAT YOU ARE DEALING WITH CORRECT SHEETS. 

Sub test() 
Dim ws1 As Worksheet, ws2 As Worksheet, r As Range, j As Integer 
Set ws2 = ActiveSheet 
j = ws2.Index 
MsgBox j 
Set ws1 = Worksheets("sheet" & j - 1) 
MsgBox ws1.Name 
ws2.Range("G1") = Left(ws1.Range("G1"), 8) & Mid(ws1.Range("G1"), 9, 4) + 1 & Right(ws1.Range("G1"), 4) 
End Sub

Thank you, venkat1926 2

Something to say? Add comment

CCM has helped 1699 users this month

Report Respond to suryam
Apr 27, 2011 at 05:19 AM
0
Thank you
Hi , your code is nice.

=LEFT(Sheet1!G1,8)&MID(Sheet1!G1,9,4)+1&RIGHT(Sheet1!G1,4)

How can I use it in a macro?
I have a macro ("copy range of cells" ) on my invoice sheet to save the cell range B6:G57 into another sheet. after that some specified cell contents will be cleared...I mean they will be empty to create next invoice. Now by the time I get new invoice, I must be able to see that the invoice number is also incremented by 1.
my invoice template is in the range of B6:G57 and my invoice number is in the cell G19.
thanks suryam
Report Respond to suryam
Apr 29, 2011 at 12:43 AM
0
Thank you
Hi venkat, I really appreciate your mind power. my problem was solved. thanks for your great help. keep smiling and sharing
thanks again
suryam
Ask a question