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
 

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

Misca

Well-known Member
Joined
Aug 12, 2009
Messages
1,588
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,588
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.
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,298
Messages
5,510,467
Members
408,791
Latest member
bwirth

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top