Increment Numbers seperated by '-'

[Ziptoo]

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.

 

[tweedle]

What is in G6?

A formula?

 

[Ziptoo]

Hi, No formula - just 09035-0001

 

[Ziptoo]

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.

 

[Rick Rothstein]

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...

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

 

[djreiswig]

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]

Rick! godbless you cotton socks! Thank you so much

 

[Ziptoo]

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!