Next in the sequence

[Guest]

Tom

Unfortunatley the code maynot be at the bottom because we have a number of
differenct code make-ups. Is there anyway of doing the below if the code sit
in the middle of the spreadsheet i.e using an if statement...

Thanks for trying

Marcus

Assuming the last populated cell would contain that number

Dim rng as Range, nextNumber as Long
set rng = Cells(rows.count,"A").End(xlup)
nextNumber = "SAT" & clng(Right(rng,len(rng)-3)) + 1
msgbox nextNumber

Was this post helpful to you

 

[Tom Ogilvy]

Dim rng as Range, nextNumber as Long
Dim i as Long, inum as Long, maxnum as long
maxnum = 0
set rng = Cells(rows.count,"A").End(xlup)
for i = rng.row to 2 step -1
inum = clng(right(cells(i,1),len(cells(i,1)-3)))
if inum > maxnum then maxnum = inum
Next
nextNumber = "SAT" & maxnum + 1
msgbox nextNumber