1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
| Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range
Dim cell As Range
Dim maxNum As Integer
Dim num As Integer
Set KeyCells = Range("C1:C" & Cells(Rows.Count, "C").End(xlUp).Row)
If Not Application.Intersect(KeyCells, Range(Target.Address)) _
Is Nothing Then
If Left(Target.Value, 1) = "R" Or Left(Target.Value, 1) = "D" Then
maxNum = 0
Application.EnableEvents = False
For Each cell In KeyCells
If cell.Value Like Left(Target.Value, 1) & "*" Then
If IsNumeric(Mid(cell.Value, 2, 3)) Then
num = CInt(Mid(cell.Value, 2, 3))
If num > maxNum Then maxNum = num
End If
End If
Next cell
Target.Value = Left(Target.Value, 1) & Format(maxNum + 1, "000")
Application.EnableEvents = True
End If
End If
End Sub |
