Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = True
If ToggleButton3.Value = True Then
r = Columns("C").Find("小計:", lookat:=xlWhole).Row
If Target.Address Like "$A$*" And Target.Row >= 16 And Target.Row < r Then
Application.EnableEvents = False
For Each A In Target
Set RNG = Sheets("參照表").Columns("A").Find(A, lookat:=xlWhole)
If RNG Is Nothing Then MsgBox "索引不存在": Application.EnableEvents = True: Exit Sub
A.Offset(, 2) = RNG.Offset(, 1): A.Offset(, 3) = RNG.Offset(, 7)
A.Offset(, 7) = RNG.Offset(, 3): A.Offset(, 8) = RNG.Offset(, 4)
A.Offset(, 9) = RNG.Offset(, 6): A.Offset(, 10) = RNG.Offset(, 5)
A.Offset(, 12) = RNG.Offset(, 2)
Next
ToggleButton1.Caption = "名稱+規格"
ToggleButton2.Caption = "廠牌+型號"
Application.EnableEvents = True
End If
Else
r = Columns("C").Find("小計:", lookat:=xlWhole).Row
If Target.Address Like "$A$*" And Target.Row >= 16 And Target.Row < r Then
Application.EnableEvents = False
For Each A In Target
Set RNG = Sheets("參照表").Columns("A").Find(A, lookat:=xlWhole)
If RNG Is Nothing Then MsgBox "索引不存在": Application.EnableEvents = True: Exit Sub
'A.Offset(, 3) = RNG.Offset(, 7)
A.Offset(, 7) = RNG.Offset(, 3): A.Offset(, 8) = RNG.Offset(, 4)
A.Offset(, 9) = RNG.Offset(, 6): A.Offset(, 10) = RNG.Offset(, 5)
A.Offset(, 12) = RNG.Offset(, 2)
Next
ToggleButton1.Caption = "名稱+規格"
ToggleButton2.Caption = "廠牌+型號"
Application.EnableEvents = True
End If
End If
End Sub作者: GBKEE 時間: 2012-4-28 21:06
