Auto incrementing alphanumeric cell by 1
Solved/Closed
Related:
- Excel increment alphanumeric text
- Excel increment number - Best answers
- Want to increment a Alphanumeric value in a range of cells - Forum - Excel
- Excel increment cell value by 1 formula ✓ - Forum - Excel
- Excel if statement increment by 1 - Guide
- Excel macro auto increment number - Guide
- Generate unique alphanumeric id in excel ✓ - Forum - Excel
4 replies
venkat1926
Apr 15, 2011 at 10:31 PM
- Posts
- 1864
- Registration date
- Sunday June 14, 2009
- Status
- Contributor
- Last seen
- August 7, 2021
Apr 15, 2011 at 10:31 PM
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
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
venkat1926
Apr 27, 2011 at 06:16 AM
- Posts
- 1864
- Registration date
- Sunday June 14, 2009
- Status
- Contributor
- Last seen
- August 7, 2021
Apr 27, 2011 at 06:16 AM
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.
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
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
=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
