Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Next Number in Sequence
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Next Number in Sequence
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Next Number in Sequence
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. 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Next Number in Sequence
See answer to later posting of this question.
-- Regards, Tom Ogilvy "Mr M Walker" wrote in message ... 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. 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Looking for next number in sequence | Excel Discussion (Misc queries) | |||
Row Number out of Sequence | Excel Discussion (Misc queries) | |||
How can I match a random number with closest number from sequence? | Excel Worksheet Functions | |||
Number sequence | Excel Discussion (Misc queries) | |||
number sequence gap | Excel Worksheet Functions |