VB code help please

[Get_Involved]

How do I restructure this part of the code, to put todays year, month, and date, before this part of the code?

Private Sub Workbook_Open()
 Dim Ans As String
 Ans = MsgBox("Update Invoice Number (???)", vbYesNo + vbInformation)
 If Ans = vbYes Then

Example: 2018121773 , year/month/date,2018/12/17+ 27 the updated number on the end.
This will create a Authorization number.

With Sheets("Expense Report").Range("H4")
 .Value = .Value + 1

In the complete code I have to manually input the numbers the first time and it increases the end of it.

Private Sub Workbook_Open()
 Dim Ans As String
 Ans = MsgBox("Update Invoice Number (???)", vbYesNo + vbInformation)
 If Ans = vbYes Then
  With Sheets("Expense Report").Range("H4")
 .Value = .Value + 1

Thanks for any help.

 

[My Aswer Is This]

I'm not sure what your wanting here.

Tell me in words what your wanting to do.

I see three pieces of code but do not know what you want the end result to look like.
And where do you want the end result entered.

 

[Michael M]

you mean someting like this ??

Private Sub Workbook_Open()
 Dim Ans As String
 Ans = MsgBox("Update Invoice Number (???)", vbYesNo + vbInformation)
 If Ans = vbYes Then
  Range("H4").Value = Left(Range("H5"), 4) & Mid(Range("H5"), 6, 2) & Mid(Range("H5"), 9, 2) + 27 'change locations to suit
    With Sheets("Expense Report").Range("H4")
 .Value = .Value + 1
 End With
 End If
 End Sub

 

[Get_Involved]

I am trying to get the current year, month, date, as the first part of the number. +
starting with 1, as the last part of the whole number.

EXample:
20181217+ 1 will read whole number 20181217-1

The code I am using I have to manually change every year.
Thanks for the reply.

 

[Michael M]

another option

Private Sub Workbook_Open()
 Dim Ans As String
 Ans = MsgBox("Update Invoice Number (???)", vbYesNo + vbInformation)
 If Ans = vbYes Then
  Range("H4").Value = WorksheetFunction.Text(Date, "yyyymmdd") + 27 'change locations to suit
    With Sheets("Expense Report").Range("H4")
 .Value = .Value + 1
 End With
 End If
 End Sub

 

[Get_Involved]

Michael M:
What does this part do ? + 27 'change locations to suit

the front part is right 20181246, the 46 is not changing to 47 when I update.
and can we get it to read this number 20181218 then the updated number, could there be a - the date and the number?
Thank You

 

[Michael M]

Maybe this way then

Private Sub Workbook_Open()
 Dim Ans As String
Range("H4").Value = WorksheetFunction.Text(Date, "yyyymmdd") + 27 'change locations to suit
Ans = MsgBox("Update Invoice Number (???)", vbYesNo + vbInformation)
 If Ans = vbYes Then
With Sheets("Expense Report").Range("H4")
    .Value = .Value + 1
 End With
 End If
 End Sub

I don't understand this comment though... maybe a before and after sample might help !!

could there be a - the date and the number?

 

[Logit]

.
With this version you will need to use I4 or some other cell as a helper cell.

Sub InvNum()
Dim Ans As String
Range("I4").Value = Range("I4").Value + 1
 Ans = MsgBox("Update Invoice Number (???)", vbYesNo + vbInformation)
 If Ans = vbYes Then
  Range("H4").Value = WorksheetFunction.Text(Date, "yyyymmdd")
    With Sheets("Expense Report").Range("H4")
 .Value = .Value & "-" & Range("I4").Value
 End With
 End If
End Sub

 

[Get_Involved]

this is your Answer, with before. 20181246, the 46 is not changing to 47.
This how I would like it to read after, 20181218-47.
After updating.

 

[Get_Involved]

Logit:
Curious why do I need a helper cell?
Thanks for the reply.