|
发表于 2013-10-25 07:42
|
显示全部楼层
本楼为最佳答案
- Option Explicit
- Private Sub Worksheet_Change(ByVal Target As Range)
- If Target.Row = 1 Then Exit Sub
- Dim i As Long, c As Object, Rng As String, j As Long, s As String
- Select Case Target.Column
- Case 4
- Target.Offset(, 1).Resize(, 2).ClearContents
- Set c = Sheet2.Columns(1).Find(Target, LookIn:=xlValues, lookat:=xlWhole)
- If Not c Is Nothing Then
- For i = c.Row To Sheet2.[B65536].End(xlUp).Row
- If Sheet2.Cells(i, 1) = "" Or Sheet2.Cells(i, 1) = c Then
- Rng = Rng & "," & Sheet2.Cells(i, 2)
- Else
- Exit For
- End If
- Next i
- Rng = Mid(Rng, 2)
- With Target.Offset(, 1).Validation
- .Delete
- .Add 3, 1, 1, Rng
- End With
- End If
- Target.Offset(, 5) = Target.Offset(, 2) * Target.Offset(, 3) * Target.Offset(0, 4)
- Case 5
- Target.Offset(, 1).NumberFormatLocal = "@"
- Set c = Sheet2.Columns(2).Find(Target, LookIn:=xlValues, lookat:=xlWhole)
- If Not c Is Nothing Then
- Target.Offset(, 1) = c.Offset(, 1)
- Else
- s = 1
- For j = 1 To Len(Target)
- If Asc(Mid(Target, j, 1)) > 0 Then
- s = s * Mid(Target, j, 1)
- End If
- Next j
- Target.Offset(, 1) = s
- Target.Offset(, 4) = Target.Offset(, 1) * Target.Offset(, 2) * Target.Offset(0, 3)
- End If
- Case 6, 7, 8
- Cells(Target.Row, 9) = Cells(Target.Row, 6) * Cells(Target.Row, 7) * Cells(Target.Row, 8)
- End Select
- End Sub
复制代码 |
|