Revision of tmp.vb

1 file changed, 55 insertions

tmp.vb(file created)

		@@ -0,0 +1,55 @@
1	+	Private Sub Worksheet_Change(ByVal Target As Range)
2	+	On Error Resume Next
3	+	irow = Target.Row
4	+	icol = Target.Column
5	+	Application.EnableEvents = False
6	+	Sheet1.Unprotect Password:="123"
7	+	i = Sheet1.Range("a999999").End(xlUp).Row
8	+	If icol = 1 And Sheet1.Range("b" & i) = "" Then
9	+
10	+	j = Sheet2.Range("a999999").End(xlUp).Row
11	+	m = Sheet1.Range("a" & i)
12	+
13	+	For n = 2 To j
14	+	If Sheet2.Range("A" & n) = m Then
15	+	Z = i
16	+	xm = n
17	+	Do While Sheet2.Range("a" & xm) = Sheet2.Range("A" & n)
18	+	'添加判断条件，跳过"代理O"的行
19	+	If Sheet2.Range("E" & xm) <> "代理O" Then
20	+	Sheet1.Range("a" & Z) = Sheet2.Range("A" & xm)
21	+	Sheet2.Range("b" & xm & ":g" & xm).Copy Sheet1.Range("b" & Z)
22	+
23	+	Sheet1.Range("b" & Z).Copy
24	+	Sheet1.Range("a" & Z).PasteSpecial Paste:=xlPasteFormats, Operation:=xlPasteSpecialOperationNone, SkipBlanks:=False, Transpose:=False
25	+	Application.CutCopyMode = False
26	+	Z = Z + 1
27	+	End If
28	+	xm = xm + 1
29	+	Loop
30	+
31	+	Exit For
32	+	End If
33	+	Next
34	+
35	+	End If
36	+	Sheet1.Protect Password:="123"
37	+	Application.EnableEvents = True
38	+	End Sub
39	+
40	+	Sub 打开()
41	+	Application.EnableEvents = True
42	+	End Sub
43	+
44	+	Sub 清空()
45	+	On Error Resume Next
46	+	Sheet1.Unprotect Password:="123"
47	+	i = MsgBox("是否清空数据？", vbOKCancel)
48	+	If i = vbOK Then
49	+	i = Sheet1.Range("a9999").End(xlUp).Row
50	+	If i >= 3 Then
51	+	Sheet1.Range("a3:g" & i).Delete Shift:=xlShiftUp
52	+	End If
53	+	End If
54	+	Sheet1.Protect Password:="123"
55	+	End Sub

Newer Older