Increment Numbers seperated by '-'

Ziptoo

New Member
Joined
Jan 7, 2014
Messages
11
Good Morning,
Please help! I have been searching the web for ages for a solution - have tried a few things, but am still searching for a solution............
I am very new to VBA, so I am still trying to wrap my head around some of the stuff.
I have a purchase requisition form that Auto Increments on Startup and then also Auto Saves the form on close. The problem is that the Purchase Requisition number '09035-0001' increments to '09036-0001' and I want it to increment to '09035-0002' as the first portion refers to my project number. The current code I am using is (in ThisWorkbook):


Private Sub Workbook_BeforeClose(Cancel As Boolean)
Saveworkbook
End Sub
_____________________________________________________
Private Sub Workbook_Open()
With Sheets("Sheet1").Range("G6")
.Value = .Value + 1
End With
End Sub

The workbook saves automatically as it closes and for that I am using (in module1):

Sub Saveworkbook()
Application.DisplayAlerts = False
Dim dName$, vName$
dName = Range("G6")
vName = ActiveWorkbook.FullName
ActiveWorkbook.SaveAs "Y:\New folder\" & dName
ActiveWorkbook.SaveAs vName
ActiveWorkbook.Close
Application.DisplayAlerts = True
End Sub

Thank you for any assistance you can afford me.
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

Ziptoo

New Member
Joined
Jan 7, 2014
Messages
11
Apologies the G6 is '09035.0001'
I would prefer the numbers to read 09035-0001 but when I put that in, the code as I have it runs an error, so I changed the numbering to 09035.0001, but then it increments the 09035 portion and not the 0001 portion. Hope that makes sense.
 
Last edited:

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,940
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Saveworkbook
End Sub
_____________________________________________________
Private Sub Workbook_Open()
With Sheets("Sheet1").Range("G6")
.Value = .Value + 1
End With
End Sub
You can perform the increment you want by replacing the red highlighted code line above with this...

Code:
.Value = Left(.Value, 6) & Format(Right(.Value, 4) + 1, "0000")
 

djreiswig

Well-known Member
Joined
Mar 13, 2010
Messages
523
Instead of adding 1 add .0001. You will also need to set the cell format to 00000.0000 in order to keep from dropping the zeros on the left or right.
 

Ziptoo

New Member
Joined
Jan 7, 2014
Messages
11
Thank you - I had previously tried adding .0001 but it didn't work - I know understand that was because I had not set the cell format as described above. Thanks so much for your input!:)
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,167,698
Messages
5,855,230
Members
431,713
Latest member
DaveMy1978

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top