finding next number in a sequence

[Bobbo]

I am looking to create a macro that will allow me to open a spreadsheet and
search column A for the last entry. The entries in the column are like
CE000001, CE000002, CE000003 and on. I need to be able to find the last
number that was used. After getting the number I will need to insert a new
number in sequence to the next row. Could you point me to some examples that
might help.

Thanks
Bob

 

[Mike H]

Hi,

You didn't say which sheet so this uses sheet1, change to suit.

You can either use this as a workbook open macro (as posted) or put it in
macro of your own

Private Sub Workbook_Open()
Sheets("Sheet1").Select
lastrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
Cells(lastrow, 1).AutoFill Destination:=Range("A" & lastrow & ":A" & lastrow
+ 1)
End Sub

Mike

 

[Rick Rothstein]

Assuming you want this "number" put in the last cell is because you are
going to add data on that row. If that is true, and if there is one column
that will always have data in it when a new rows worth of data is entered
(such as a Name column), then you can make the numbering automatic without
using VB code. Assuming Column A is the column where your "number" is placed
and assuming Column B is the column that will always have data when the row
has data in it, put this in the first cell where you want CE000001 to be
placed and copy it down as far as you think you need to (you can always copy
it down further late on if needed)...

=IF(B1<>"",TEXT(ROW(A1),"C\E000000"),"")

Now, when you enter something in Column B, then next sequential number will
be placed in Column A automatically.

 

[Patrick Molloy]

Sub nextnum()
With Cells(Rows.Count, 1).End(xlUp)
.Offset(1) = "CE" & Format(Mid(.Value, 3) + 1, "000000")
End With
End Sub