|
发表于 2012-12-12 22:56
|
显示全部楼层
本楼为最佳答案
本帖最后由 suye1010 于 2012-12-12 22:59 编辑
- Private Sub Worksheet_Change(ByVal Target As Range)
- If Target.Count = 1 And Target.Column = 2 And IsDate(Target) Then
- Select Case Target.Offset(-1, -1)
- Case "编码"
- If Left(Right(Sheets("记录表").Cells(65536, 1).End(xlUp), 5), 2) <> Format(Target, "MM") Then
- Target.Offset(0, -1) = Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 4) & Format(Target, "YYMM") & Format(1, "000")
- Else
- Target.Offset(0, -1) = Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 4) & Format(Target, "YYMM") & Format(1 + Val(Right(Sheets("记录表").Cells(65536, 1).End(xlUp), 3)), "000")
- End If
- Case Else
- If Left(Right(Target.Offset(-1, -1), 5), 2) <> Format(Target, "MM") Then
- Target.Offset(0, -1) = Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 4) & Format(Target, "YYMM") & Format(1, "000")
- Else
- Target.Offset(0, -1) = Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 4) & Format(Target, "YYMM") & Format(1 + Val(Right(Target.Offset(-1, -1), 3)), "000")
- End If
- End Select
- End If
- End Sub
复制代码 |
|