Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Monroeville, Pennsylvania, USA
    Posts
    152
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I need to be able to increment a number in a cell by 1. Assume cell E7 has a number and txt that says EN 001. The next time I enter cell E8 to insert a name, I would like cell e7 to change to EN 002.
    Any help would be appreciated.

  2. #2
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    EN ### (any three digit number) needs to be in the E7 cell before the first running of the code shown below
    Right click on the tab you want the incrementing and choose View Code...
    paste the following.


    Code:
    Option Explicit
    Dim dClick
    
    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Intersect(Target, Range("e8")) Is Nothing Then
    Exit Sub
    Else
        Range("e7") = "EN " & Format(Right(Range("e7"), 3) + 1, "000")
        dClick = True
    End If
    End Sub
    
    Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("e8")) Is Nothing Then
        Exit Sub
    Else
    If dClick = True Then
        dClick = False
        Exit Sub
    Else
        Range("e7") = "EN " & Format(Right(Range("e7"), 3) + 1, "000")
    End If
    End If
    End Sub

  3. #3
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    mbarron,

    I couldn't get your code to run as posted.
    I made the following changes to get it to run.
    Code:
    Option Explicit
    Dim dClick
    
    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Intersect(Target, Range("e8")) Is Nothing Then
    Exit Sub
    Else
        Range("e7").Value = "EN " & Format(Val(Right(Range("e7").Text, 3) + 1), "000")
        dClick = True
    End If
    End Sub
    
    Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("e8")) Is Nothing Then
        Exit Sub
    Else
    If dClick = True Then
        dClick = False
        Exit Sub
    Else
        Range("e7").Value = "EN " & Format(Val(Right(Range("e7").Text, 3) + 1), "000")
    End If
    End If
    End Sub
    EDIT: Actually I went back and checked and the .Text isn't need, but doesn't hurt, but the .Value is.

    RG
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  4. #4
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Monroeville, Pennsylvania, USA
    Posts
    152
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks much to both. Works just fine.

  5. #5
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Monroeville, Pennsylvania, USA
    Posts
    152
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks to both. Works just fine

Posting Permissions

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