MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Sequencing Numbers

Posted by John Stackhouse on September 26, 2001 6:39 AM

I am trying to get my excel work book to recognize the next number in a sequence (only in one collum). I am building a database of companies that come to my business. In the workbook I have made a sheet for every letter in the alfabet, in each sheet, in the B collumn I need a number. This number needs to be the next number in the sequence each time for each row in the same collum. With 27 sheets in the workbook, is it possable to make a macro or function to look at all the sheets (within the B collumn)and determine the next number in a sequence?

I hope this isn't to confusing.

Posted by Barrie Davidson on September 26, 2001 7:31 AM

John, this code will determine your next number.

Sub Determine_Next_Number()
' Written by Barrie Davidson
Dim nextNumber, count, counter
nextNumber = 0
count = ActiveWorkbook.Sheets.count
For counter = 1 To count
If Application.WorksheetFunction.Max(Range("B:B")) > nextNumber Then nextNumber = Application.WorksheetFunction.Max(Range("B:B"))
Next counter
nextNumber = nextNumber + 1
End Sub

BarrieBarrie Davidson