Generating Automatic Serial Numbers

karmensoo

New Member
Joined
Jul 7, 2008
Messages
11
I'm using MS excel template to generate an internal memo form. To help me keep track of my memo forms easier, I want excel template to generate automatic serial number everytime I fill-in a new memo form. For eg.

Memo to John - Excel to auto generate "Memo Serial # 001"
Memo to Karen - ...."Memo Serial # 002"
Memo to Wayne - ...."Memo Serial # 003"
etc...etc..


TQ
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

Misca

Well-known Member
Joined
Aug 12, 2009
Messages
1,697
Here's a macro that should do what you're after:
Code:
Sub MemoSerial()

Dim Serial As Long
Dim Memo As String
Dim NM As Name

On Error Resume Next

'Uses the name "Serial" to house the serial values
Set NM = ThisWorkbook.Names("Serial")

If Not NM Is Nothing Then
'Name exists:
    Serial = Val(Mid(NM, 2, Len(NM))) + 1
Else
    Serial = 1
End If

'Changes the name "Serial" to match the new Serial:
ThisWorkbook.Names.Add Name:="Serial", RefersTo:="=" & Serial

If Serial < 100 Then
    Memo = Right("00" & Serial, 3)
Else
    Memo = Serial
End If

Memo = "Memo Serial # " & Memo

MsgBox Memo

End Sub
 

karmensoo

New Member
Joined
Jul 7, 2008
Messages
11
Misca, thanks 4d reply n code. Ermm..problem is I'm a newbie and do not know how to go about putting those codes in place :oops:

If you dont mind, can you guide me please :confused:

Tkns in advance.
 

Misca

Well-known Member
Joined
Aug 12, 2009
Messages
1,697
What is your MemoTemplate like now? What does it do / how do you use it (= open the template document from an icon? Have the template workbook open and press a button that generates a new document?)?

And what do you want to do with the MemoSerials? My guess is you want to store the memos in a workbook and use it as a database but if my guess is correct, what it is exactly that you want to store? And how do you want to store it?

If you want to get a fully functional macro you need to give all the possible info.
 

Watch MrExcel Video

Forum statistics

Threads
1,133,325
Messages
5,658,166
Members
418,430
Latest member
Chlwls808

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