Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
finding next number in a sequence
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
finding next number in a sequence
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 "Bobbo" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
finding next number in a sequence
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. -- Rick (MVP - Excel) "Bobbo" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
finding next number in a sequence
Sub nextnum()
With Cells(Rows.Count, 1).End(xlUp) .Offset(1) = "CE" & Format(Mid(.Value, 3) + 1, "000000") End With End Sub "Bobbo" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Finding missing numbers in a sorted sequence | Excel Discussion (Misc queries) | |||
Finding the longest sequence of 0's in a row | Excel Worksheet Functions | |||
How can I match a random number with closest number from sequence? | Excel Worksheet Functions | |||
Excel formula for finding next number in a given sequence? | Excel Programming | |||
Finding numbers missing from a sequence | Excel Discussion (Misc queries) |