Increment numbers

Topic

New Reply

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.

Reply | Quote

Replies

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

Reply | Quote

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!
Computer Specs

Reply | Quote

Thanks much to both. Works just fine.

Reply | Quote

Thanks to both. Works just fine

Reply | Quote