Changing Invoice Number with serial number

[adam]

Hi Anyone,

I'm using the following code to write my invoice & receipt number in the cell referenced in the code.

I need help to modify the code so that when I write a number in cell A1 of the sheet. The cell referenced in the code changes to the number that I write in cell A1 with the format in the code.

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim oCell As Range
  If Not Intersect(Target, Range("C8:C9")) Is Nothing Then
    Application.EnableEvents = False
    For Each oCell In Intersect(Target, Range("C8:C9"))
      If IsNumeric(oCell) And Not oCell = "" Then
        oCell = Format(oCell, "0000") & " / " & Format(Now(), "yy")
      End If
    Next oCell
    Application.EnableEvents = True
  End If
End Sub

I want only the number in C9 to change a I write a number in A1

Lets say If I write 0001 in cell A1 I want the number to appear as 0001/10 on C9 where 10 is the year.
If I write 0002 in cell A1 I want it to appear as 0002/10 on C9

Any help would be kindly appreciated.

Thanks in advance.

[HansV]

You don't need code for that. Enter the following formula in C9:

=TEXT(A1,"0000")&" / "&TEXT(TODAY(),"yy")

[Rudi]

Give this a try...

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Range("A1")) Is Nothing Then
    Application.EnableEvents = False
      If IsNumeric(Target) And Not Target = "" Then
        Range("C9") = Format(Target, "0000") & " / " & Format(Now(), "yy")
      End If
    Application.EnableEvents = True
  End If
End Sub

[Rudi]

You don't need code for that.

Quite right!!!

[adam]

Thanks for the help Hans & Rudi. Like Hans said the formula has done the job