Next in the sequence
 

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

"Tom Ogilvy" wrote:

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

--
Regards,
Tom Ogilvy


"Mr M Walker" wrote in message
...
VBA Novice

Using VBA I want to know what Account code was last iputted (used) in a
given sequence within a column so the user know the next number to use
(Users
are inputting via userforms so cannot see the spreadsheet.

Therefore if column A as codes SAT1, SAT2, SAT3 etc I want to able via a
msgbx to notify the users that SAT3 was the last Account code to be used
and
therefore user SAT4 should be used next.

Obviously this will only be used when the user hits a command button
titled
next availble account.

Many thanks

M



Was this post helpful to you

Next in the sequence
 

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

--
Regards,
Tom Ogilvy


"Mr M Walker" wrote in message
...
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

"Tom Ogilvy" wrote:

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

--
Regards,
Tom Ogilvy


"Mr M Walker" wrote in message
...
VBA Novice

Using VBA I want to know what Account code was last iputted (used) in
a
given sequence within a column so the user know the next number to use
(Users
are inputting via userforms so cannot see the spreadsheet.

Therefore if column A as codes SAT1, SAT2, SAT3 etc I want to able via
a
msgbx to notify the users that SAT3 was the last Account code to be
used
and
therefore user SAT4 should be used next.

Obviously this will only be used when the user hits a command button
titled
next availble account.

Many thanks

M



Was this post helpful to you