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.

Unfortunatley the code maynot be at the bottom or top because we have a
number of differenct code make-ups.

Many thanks fro trying

M

Did Tom's suggestion not help? If not, in what way?

--

HTH

RP
(remove nothere from the email address if mailing direct)


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

Unfortunatley the code maynot be at the bottom or top because we have a
number of differenct code make-ups.

Many thanks fro trying

M

Mr M Walker,
In case you missed it, here it is again:

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



"Bob Phillips" wrote in message
...
Did Tom's suggestion not help? If not, in what way?

--

HTH

RP
(remove nothere from the email address if mailing direct)


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

Unfortunatley the code maynot be at the bottom or top because we have a
number of differenct code make-ups.

Many thanks fro trying

M