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

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

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
36,981
Office Version
  1. 2016
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!:)
 

Watch MrExcel Video

Forum statistics

Threads
1,130,307
Messages
5,641,439
Members
417,209
Latest member
Agbarker

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