Solved: Concatenate and Autonumbering

[coliervile]

I have a form on a worksheet and I'm having trouble autonumbering the form in the format that I need it in. I have a macro, in module1, that will number the form, but it's not what I need. The format that I need the Form Number is "FNT-07-001"; FNT equals facility identifier, 07 equals current year and 001 is the first sequencial number of the form.:help

I came across this VB,but can't figure out how to use it the way that I need to;

Sub vvv()
x = Cells(Rows.Count, 1).End(xlUp).Row
For a = x To x + 10
Cells(a, 1) = "CTOL-" & x + 1 & "-07"
Next a
End Sub
When you run this it will generate numbers in col A from the last entry and add 10 numbers (x+10: you can change this)
07 (year is hard coded). if necessary a formula can used to add year too.

It uses concatenate which seems like what I need to accomplish the autonumbering in the format I want. :dunno

Some how I need the macro to refer to the last number in the database used, Column "A", and then add "1" to that number. I would appreciate any assistance and suggestions.

Best regards,

Charlie

[mdmackillop]

Hi Charlie

Sub GoNewForm()
Dim LastNo As Long
LastNo = Split(Sheets("Database").Cells(Rows.Count, 1).End(xlUp), "-")(2)
Sheets("Input").Range("G1") = "FNT-" & Format(Date, "yy") & _
Format(LastNo + 1, "-000")
End Sub

[coliervile]

"MD" you never cease to amaze me. Looking at who was logged on the site, if I were a betting man, my money was on you to come up with a solution.

Cheers to you sir :beerchug:

Best regards,

Charlie