Generate PO Number [VBA]

[vijay.vizzu]

Dear all,


I am working on a project, but somewhere i can't able to solve the problem. So i need your expertise in VBA.


There is a shape namely "Finalize", i want that whenever this will click, it creates a invoice number for me like "IYM-PROTO-001", and to save this number in a table namely "table1" in sheet name "index". After some filling of data, if i will click it again, it should create "IYM-PROTO-002" and so on.


I hope you understood my problem

 

[bobhc]

Good day vijay.vizzu


These links may help


http://blogs.office.com/b/microsoft-excel/archive/2012/01/12/generate-invoice-numbers-in-excel.aspx


http://www.mrexcel.com/forum/microsoft-access/670747-access-visual-basic-applications-code-needed-input-invoice-number-starting-then.html

 

[shrivallabha]

Hi Vijay,


You can try following routine.


I have assumed Invoice Number is Column 1 in Table 1.

[pre]

Public Sub InsertInvoiceNumber()
With Sheets("Index").Range("Table1")
If Len(.Cells(1, 1).Value) = 0 Then
.Cells(1, 1).Value = "IYM-PROTO-001"
Else
If (.Cells(1, 1).Offset(1, 0).Value) = 0 Then
.Cells(1, 1).Offset(1, 0).Value = "IYM-PROTO-002"
Else
.Cells(1, 1).End(xlDown).Offset(1, 0).Value = _
"IYM-PROTO-" & Format(CInt(Right(.Cells(1, 1).End(xlDown), 3) + 1), "000")
End If
End If
End With
End Sub

[/pre]

If it is as per your requirement then assign this macro to the shape you desire.

 

[vijay.vizzu]

Thank you shrivallabha, it working like charm, i am changing this as per my requirement


Thanks once again for your prompt reply


Vijay