Results 1 to 8 of 8
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts

    Increment last value in column (2003)

    Here I am again w/a VB question. Given a column (e.g., E) that has both blanks as well as sequential numbers...I would like a macro that finds the largest number, adds 1 to it and places that resulting number in the next unused cell in the E column. Trivial in my mind, but not with VB brain cells missing.

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Increment last value in column (2003)

    How about:

    <code>
    ActiveSheet.Range("E65536").End(xlUp).Offset(1, 0).Value = Application.WorksheetFunction.Max(Range("E:E")) + 1
    </code>
    Legare Coleman

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Increment last value in column (2003)

    Try this:

    Sub IncrementE()
    Dim m As Double
    Dim r As Long
    m = Application.WorksheetFunction.Max(Range("E:E"))
    r = Range("E65536").End(xlUp).Row
    Range("E" & (r + 1)) = m + 1
    End Sub

  4. #4
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts

    Re: Increment last value in column (2003)

    What was the problem here? Where the two of you on a break? Your two responses, both of which are just what I need -- of course -- took you less than 10 minutes to reply. Phew!
    What a pleasure. Thanks!!

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Increment last value in column (2003)

    Well, you didn't say you wanted untested code! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Legare Coleman

  6. #6
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts

    Re: Increment last value in column (2003)

    Minor change...would you modify this to place the incremented value in the ACTIVE cell rather than the first avail in E? TIA

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Increment last value in column (2003)

    Sub IncrementE()
    Dim m As Double
    m = Application.WorksheetFunction.Max(Range("E:E"))
    ActiveCell = m + 1
    End Sub

  8. #8
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Increment last value in column (2003)

    Like this:

    <code>
    ActiveCell.Value = Application.WorksheetFunction.Max(Range("E:E")) + 1
    </code>
    Legare Coleman

Posting Permissions

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