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" <[email protected]> wrote in message
> news:[email protected]...
> > 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

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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
>
>