+ Reply to Thread
Results 1 to 2 of 2

Next in the sequence

  1. #1
    Mr M Walker
    Guest

    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



  2. #2
    Tom Ogilvy
    Guest

    Re: 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" <[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
    >
    >




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1