'''打开excel开始'''
Dim fs As Object
Set fs = CreateObject("scripting.filesystemobject")
ChDrive (Left(ThisWorkbook.Path, 1))
ChDir (fs.getfolder(ThisWorkbook.Path))
mypath = CurDir
Filename = Application.GetOpenFilename
If Filename = "False" Then
Exit Sub
End If
Workbooks.Open Filename
'''打开excel结束'''
If ActiveWorkbook.ActiveSheet.Cells(3, 1) <> "Err" Then
ActiveWorkbook.ActiveSheet.Columns(1).Insert
ActiveWorkbook.ActiveSheet.Cells(3, 1) = "Err"
End If
Call Remove_Hidden
Call Store_Number
Call Final_State1
Call Check_Time
Call Final_State2
Call First_State
Call Final_State_Check
Call Final_State_False
Call Age
Call CC
Call Time_Range
Call Final_Sign
Call remind
End Sub
''''''''''''''''''''''''''''''TEP1''''''''''''''''''''''''''''''
Sub Remove_Hidden()
max_ar = ActiveWorkbook.ActiveSheet.Range("B65535").End(xlUp).Row
max_ac = ActiveWorkbook.ActiveSheet.Range("IV3").End(xlToLeft).Column
i = 4
'''''删除隐藏行-start'''''
For n = 4 To max_ar
If Rows(i).EntireRow.Hidden = True Then
Rows(i).Delete
Else: i = i + 1
End If
Next n
'''''删除隐藏行-end'''''
i = 1
'''''删除隐藏列-start'''''
For m = 1 To max_ac
If Columns(i).EntireColumn.Hidden = True Then
Columns(i).Delete
Else: i = i + 1
End If
Next m
'''''删除隐藏列-end'''''
'''''删除空数据'''''
For col = 1 To max_ac
If ActiveWorkbook.ActiveSheet.Cells(3, col) = "大区" Then Exit For
Next col
For c = 4 To max_ar
If ActiveWorkbook.ActiveSheet.Cells(c, col) = "" Then
Rows(c1).Delete
Else: c1 = c + 1
End If
Next c
s = ActiveWorkbook.ActiveSheet.Range("B65535").End(xlUp).Row - 3
MsgBox ("总共" & s & "条数据")
End Sub
''''''''''''''''''''''''''''''TEP2''''''''''''''''''''''''''''''
Sub Store_Number()
max_ar = ActiveWorkbook.ActiveSheet.Range("B65535").End(xlUp).Row
max_ac = ActiveWorkbook.ActiveSheet.Range("IV3").End(xlToLeft).Column
For col = 1 To max_ac
If ActiveWorkbook.ActiveSheet.Cells(3, col) = "门店系统编号" Then Exit For
Next col
For x = 4 To max_ar
aaa = 0
num = ActiveWorkbook.ActiveSheet.Cells(x, col)
aaa = 0
For y = 4 To max_ar
If num = ActiveWorkbook.ActiveSheet.Cells(y, col) Then aaa = aaa + 1
Next y
If aaa > 1 Then ActiveWorkbook.ActiveSheet.Cells(x, 1) = ActiveWorkbook.ActiveSheet.Cells(x, 1) + "同一档期门店号重复."
Next x
End Sub
''''''''''''''''''''''''''''''TEP3''''''''''''''''''''''''''''''
Sub Final_State1()
max_ar = ActiveWorkbook.ActiveSheet.Range("B65535").End(xlUp).Row
max_ac = ActiveWorkbook.ActiveSheet.Range("IV3").End(xlToLeft).Column
For col0 = 1 To max_ac
If ActiveWorkbook.ActiveSheet.Cells(3, col0) = "最终状态" Then Exit For
Next col0
For col1 = 1 To max_ac
If ActiveWorkbook.ActiveSheet.Cells(3, col1) = "第1次拨打状态" Then Exit For
Next col1
For col2 = 1 To max_ac
If ActiveWorkbook.ActiveSheet.Cells(3, col2) = "第2次拨打状态" Then Exit For
Next col2
For col3 = 1 To max_ac
If ActiveWorkbook.ActiveSheet.Cells(3, col3) = "第3次拨打状态" Then Exit For
Next col3
For col4 = 1 To max_ac
If ActiveWorkbook.ActiveSheet.Cells(3, col4) = "第4次拨打状态" Then Exit For
Next col4
For col5 = 1 To max_ac
If ActiveWorkbook.ActiveSheet.Cells(3, col5) = "第5次拨打状态" Then Exit For
Next col5
For i = 4 To max_ar
If ActiveWorkbook.ActiveSheet.Cells(i, col0) = "" Then
If ActiveWorkbook.ActiveSheet.Cells(i, col1) = "" And ActiveWorkbook.ActiveSheet.Cells(i, col2) = "" And ActiveWorkbook.ActiveSheet.Cells(i, col3) = "" And ActiveWorkbook.ActiveSheet.Cells(i, col4) = "" And ActiveWorkbook.ActiveSheet.Cells(i, col5) = "" Then
ActiveWorkbook.ActiveSheet.Cells(i, 1) = ActiveWorkbook.ActiveSheet.Cells(i, 1) + "最终状态为空，且各次拨打均为空"
End If
End If
Next i
End Sub
''''''''''''''''''''''''''''''TEP4、TEP5''''''''''''''''''''''''''''''
Sub Check_Time()
max_ar = ActiveWorkbook.ActiveSheet.Range("B65535").End(xlUp).Row
max_ac = ActiveWorkbook.ActiveSheet.Range("IV3").End(xlToLeft).Column
For col_s = 1 To max_ac
If ActiveWorkbook.ActiveSheet.Cells(3, col_s) = "实际档期开始日期" Then Exit For
Next col_s
For col_e = 1 To max_ac
If ActiveWorkbook.ActiveSheet.Cells(3, col_e) = "实际档期结束日期" Then Exit For
Next col_e
For col_c = 1 To max_ac
If ActiveWorkbook.ActiveSheet.Cells(2, col_c) = "第一次抽查" And ActiveWorkbook.ActiveSheet.Cells(3, col_c) = "抽查日期" Then Exit For
Next col_c
For i = 4 To max_ar
time_start = ActiveWorkbook.ActiveSheet.Cells(i, col_s)
time_end = ActiveWorkbook.ActiveSheet.Cells(i, col_e)
time_check = ActiveWorkbook.ActiveSheet.Cells(i, col_c)
Time1 = time_end - time_start
Time2 = time_check - time_start
Time3 = time_end - time_check
If Time1 >= 0 Then
Else: ActiveWorkbook.ActiveSheet.Cells(i, 1) = ActiveWorkbook.ActiveSheet.Cells(i, 1) + "结束日期早于开始日期"
End If
If Time2 >= 0 Then
Else: ActiveWorkbook.ActiveSheet.Cells(i, 1) = ActiveWorkbook.ActiveSheet.Cells(i, 1) + "第一次调查日期早于开始日期"
End If
If Time3 >= 0 Then
Else: ActiveWorkbook.ActiveSheet.Cells(i, 1) = ActiveWorkbook.ActiveSheet.Cells(i, 1) + "第一次调查日期晚于结束日期"
End If
Next i
End Sub
''''''''''''''''''''''''''''''TEP6''''''''''''''''''''''''''''''
Sub Final_State2()
max_ar = ActiveWorkbook.ActiveSheet.Range("B65535").End(xlUp).Row
max_ac = ActiveWorkbook.ActiveSheet.Range("IV3").End(xlToLeft).Column
For col0 = 1 To max_ac
If ActiveWorkbook.ActiveSheet.Cells(3, col0) = "最终状态" Then Exit For
Next col0
For col1 = 1 To max_ac
If ActiveWorkbook.ActiveSheet.Cells(3, col1) = "拨打不成功原因" Then Exit For
Next col1
For i = 4 To max_ar
If ActiveWorkbook.ActiveSheet.Cells(i, col0) = "不成功" And ActiveWorkbook.ActiveSheet.Cells(i, col1) = "" Then
ActiveWorkbook.ActiveSheet.Cells(i, 1) = ActiveWorkbook.ActiveSheet.Cells(i, 1) + "最终状态为不成功，且不成功原因为空"
End If
Next i
End Sub
''''''''''''''''''''''''''''''TEP7、TEP8''''''''''''''''''''''''''''''
Sub First_State()
max_ar = ActiveWorkbook.ActiveSheet.Range("B65535").End(xlUp).Row
max_ac = ActiveWorkbook.ActiveSheet.Range("IV3").End(xlToLeft).Column
For col0 = 1 To max_ac
If ActiveWorkbook.ActiveSheet.Cells(3, col0) = "第1次拨打状态" Then Exit For
Next col0
For col1 = 1 To max_ac
If ActiveWorkbook.ActiveSheet.Cells(2, col1) = "第一次抽查" And ActiveWorkbook.ActiveSheet.Cells(3, col1) = "抽查日期" Then Exit For
Next col1
For col2 = 1 To max_ac
If ActiveWorkbook.ActiveSheet.Cells(2, col2) = "第二次抽查" And ActiveWorkbook.ActiveSheet.Cells(3, col2) = "抽查日期" Then Exit For
Next col2
For col3 = 1 To max_ac
If ActiveWorkbook.ActiveSheet.Cells(2, col3) = "第三次抽查" And ActiveWorkbook.ActiveSheet.Cells(3, col3) = "抽查日期" Then Exit For
Next col3
For col4 = 1 To max_ac
If ActiveWorkbook.ActiveSheet.Cells(2, col4) = "第四次抽查" And ActiveWorkbook.ActiveSheet.Cells(3, col4) = "抽查日期" Then Exit For
Next col4
For col5 = 1 To max_ac
If ActiveWorkbook.ActiveSheet.Cells(2, col5) = "第五次抽查" And ActiveWorkbook.ActiveSheet.Cells(3, col5) = "抽查日期" Then Exit For
Next col5
For i = 4 To max_ar
state0 = ActiveWorkbook.ActiveSheet.Cells(i, col0)
state1 = ActiveWorkbook.ActiveSheet.Cells(i, col1)
state2 = ActiveWorkbook.ActiveSheet.Cells(i, col2)
state3 = ActiveWorkbook.ActiveSheet.Cells(i, col3)
state4 = ActiveWorkbook.ActiveSheet.Cells(i, col4)
state5 = ActiveWorkbook.ActiveSheet.Cells(i, col5)
If state0 = "" Then
ActiveWorkbook.ActiveSheet.Cells(i, 1) = ActiveWorkbook.ActiveSheet.Cells(i, 1) + "第1次拨打状态为空"
ElseIf state1 = "" And state2 = "" And state3 = "" And state4 = "" And state5 = "" Then
ActiveWorkbook.ActiveSheet.Cells(i, 1) = ActiveWorkbook.ActiveSheet.Cells(i, 1) + "第1次拨打状态不为空，但抽查日期为空"
End If
Next i
End Sub
''''''''''''''''''''''''''''''TEP9、TEP10''''''''''''''''''''''''''''''
Sub Final_State_Check()
max_ar = ActiveWorkbook.ActiveSheet.Range("B65535").End(xlUp).Row
max_ac = ActiveWorkbook.ActiveSheet.Range("IV3").End(xlToLeft).Column
For col0 = 1 To max_ac
If ActiveWorkbook.ActiveSheet.Cells(3, col0) = "最终状态" Then Exit For
Next col0
For col1 = 1 To max_ac
If ActiveWorkbook.ActiveSheet.Cells(3, col1) = "拨打不成功原因" Then Exit For
Next col1
For col2 = 1 To max_ac
If ActiveWorkbook.ActiveSheet.Cells(3, col2) = "答题数量" Then Exit For
Next col2
For i = 4 To max_ar
state0 = ActiveWorkbook.ActiveSheet.Cells(i, col0)
state1 = ActiveWorkbook.ActiveSheet.Cells(i, col1)
state2 = ActiveWorkbook.ActiveSheet.Cells(i, col2)
If state0 = "成功" Or state0 = "回拨成功" Then
If state1 <> "" Then
ActiveWorkbook.ActiveSheet.Cells(i, 1) = ActiveWorkbook.ActiveSheet.Cells(i, 1) + "最终状态为成功，但存在不成功原因"
End If
If state2 = 0 Then
ActiveWorkbook.ActiveSheet.Cells(i, 1) = ActiveWorkbook.ActiveSheet.Cells(i, 1) + "最终状态为成功，但答题数为0"
End If
End If
Next i
End Sub
''''''''''''''''''''''''''''''TEP11、TEP12''''''''''''''''''''''''''''''
Sub Final_State_False()
max_ar = ActiveWorkbook.ActiveSheet.Range("B65535").End(xlUp).Row
max_ac = ActiveWorkbook.ActiveSheet.Range("IV3").End(xlToLeft).Column
For col0 = 1 To max_ac
If ActiveWorkbook.ActiveSheet.Cells(3, col0) = "最终状态" Then Exit For
Next col0
For col1 = 1 To max_ac
If ActiveWorkbook.ActiveSheet.Cells(3, col1) = "拨打不成功原因" Then Exit For
Next col1
For col2 = 1 To max_ac
If ActiveWorkbook.ActiveSheet.Cells(3, col2) = "S5-培训地点" Then Exit For
Next col2
For col3 = 1 To max_ac
If Mid(ActiveWorkbook.ActiveSheet.Cells(3, col3), 1, 3) = "S1-" Then Exit For
Next col3
For i = 4 To max_ar
state0 = ActiveWorkbook.ActiveSheet.Cells(i, col0)
state1 = ActiveWorkbook.ActiveSheet.Cells(i, col1)
state2 = ActiveWorkbook.ActiveSheet.Cells(i, col2)
state3 = ActiveWorkbook.ActiveSheet.Cells(i, col3)
If state1 = "未培训" Then
If state2 = "没有培训" Then
Else: ActiveWorkbook.ActiveSheet.Cells(i, 1) = ActiveWorkbook.ActiveSheet.Cells(i, 1) + "失败原因为未培训,S5不为没有培训"
End If
End If
If state2 = "没有培训" Then
If state1 = "未培训" Then
Else: ActiveWorkbook.ActiveSheet.Cells(i, 1) = ActiveWorkbook.ActiveSheet.Cells(i, 1) + "S5为没有培训，失败原因不为未培训"
End If
End If
If state1 = "离职" Then
If state3 = "C.已经离职" Then
Else: ActiveWorkbook.ActiveSheet.Cells(i, 1) = ActiveWorkbook.ActiveSheet.Cells(i, 1) + "失败原因为离职,S1不为已经离职"
End If
End If
If state3 = "C.已经离职" Then
If state1 = "离职" Then
Else: ActiveWorkbook.ActiveSheet.Cells(i, 1) = ActiveWorkbook.ActiveSheet.Cells(i, 1) + "S1为已经离职，失败原因不为离职"
End If
End If
Next i
End Sub
''''''''''''''''''''''''''''''TEP13、TEP14''''''''''''''''''''''''''''''
Sub Age()
max_ar = ActiveWorkbook.ActiveSheet.Range("B65535").End(xlUp).Row
max_ac = ActiveWorkbook.ActiveSheet.Range("IV3").End(xlToLeft).Column
For col0 = 1 To max_ac
If ActiveWorkbook.ActiveSheet.Cells(3, col0) = "所属年龄段" Then Exit For
Next col0
For col1 = 1 To max_ac
If ActiveWorkbook.ActiveSheet.Cells(3, col1) = "S4-1年龄请注明" Then Exit For
Next col1
For col2 = 1 To max_ac
If ActiveWorkbook.ActiveSheet.Cells(3, col2) = "S4-促销员年龄" Then Exit For
Next col2
For i = 4 To max_ar
state0 = ActiveWorkbook.ActiveSheet.Cells(i, col0)
state1 = ActiveWorkbook.ActiveSheet.Cells(i, col1)
state2 = ActiveWorkbook.ActiveSheet.Cells(i, col2)
If state0 = "" Then
If state1 <> "" Then
ActiveWorkbook.ActiveSheet.Cells(i, 1) = ActiveWorkbook.ActiveSheet.Cells(i, 1) + "所属年龄段为空时,S4-1年龄请注明为非空"
End If
Else
If state1 = "" Then
If state2 <> "拒答" Then ActiveWorkbook.ActiveSheet.Cells(i, 1) = ActiveWorkbook.ActiveSheet.Cells(i, 1) + "所属年龄段不为空时,S4-1年龄请注明为空,S4-促销员年龄不为拒答"
Else
If state2 = "拒答" Then ActiveWorkbook.ActiveSheet.Cells(i, 1) = ActiveWorkbook.ActiveSheet.Cells(i, 1) + "所属年龄段不为空时,S4-1年龄请注明不为空,S4-促销员年龄为拒答"
End If
End If
Next i
End Sub
''''''''''''''''''''''''''''''TEP15''''''''''''''''''''''''''''''
Sub CC()
max_ar = ActiveWorkbook.ActiveSheet.Range("B65535").End(xlUp).Row
max_ac = ActiveWorkbook.ActiveSheet.Range("IV3").End(xlToLeft).Column
For col0 = 1 To max_ac
If ActiveWorkbook.ActiveSheet.Cells(3, col0) = "WAVE" Then Exit For
Next col0
For col1 = 1 To max_ac
If ActiveWorkbook.ActiveSheet.Cells(3, col1) = "所属时间" Then Exit For
Next col1
For col2 = 1 To max_ac
If ActiveWorkbook.ActiveSheet.Cells(3, col2) = "促销类型" Then Exit For
Next col2
For col3 = 1 To max_ac
If ActiveWorkbook.ActiveSheet.Cells(3, col3) = "大区" Then Exit For
Next col3
For col4 = 1 To max_ac
If ActiveWorkbook.ActiveSheet.Cells(3, col4) = "执行公司" Then Exit For
Next col4
For i = 4 To max_ar
state0 = ActiveWorkbook.ActiveSheet.Cells(i, col0)
state1 = ActiveWorkbook.ActiveSheet.Cells(i, col1)
state2 = ActiveWorkbook.ActiveSheet.Cells(i, col2)
state3 = ActiveWorkbook.ActiveSheet.Cells(i, col3)
state4 = ActiveWorkbook.ActiveSheet.Cells(i, col4)
If state0 <> ActiveWorkbook.ActiveSheet.Cells(4, col0) Then ActiveWorkbook.ActiveSheet.Cells(i, 1) = ActiveWorkbook.ActiveSheet.Cells(i, 1) + "WAVE数据不一致"
If state1 <> ActiveWorkbook.ActiveSheet.Cells(4, col1) Then ActiveWorkbook.ActiveSheet.Cells(i, 1) = ActiveWorkbook.ActiveSheet.Cells(i, 1) + "所属时间数据不一致"
If state2 <> ActiveWorkbook.ActiveSheet.Cells(4, col2) Then ActiveWorkbook.ActiveSheet.Cells(i, 1) = ActiveWorkbook.ActiveSheet.Cells(i, 1) + "促销类型数据不一致"
If state3 <> "大华东区" And state3 <> "大华南区" And state3 <> "大华西区" And state3 <> "大华北区" Then ActiveWorkbook.ActiveSheet.Cells(i, 1) = ActiveWorkbook.ActiveSheet.Cells(i, 1) + "大区数据不为大华东南西北区"
If state4 <> "励维" And state4 <> "格魅青影" And state4 <> "奥维思" And state4 <> "电声" Then ActiveWorkbook.ActiveSheet.Cells(i, 1) = ActiveWorkbook.ActiveSheet.Cells(i, 1) + "执行公司数据不为格魅青影 电声 奥维思 励维"
Next i
End Sub
''''''''''''''''''''''''''''''TEP16''''''''''''''''''''''''''''''
Sub Time_Range()
max_ar = ActiveWorkbook.ActiveSheet.Range("B65535").End(xlUp).Row
max_ac = ActiveWorkbook.ActiveSheet.Range("IV3").End(xlToLeft).Column
For col1 = 1 To max_ac
If ActiveWorkbook.ActiveSheet.Cells(2, col1) = "第一次抽查" And ActiveWorkbook.ActiveSheet.Cells(3, col1) = "抽查日期" Then Exit For
Next col1
For col2 = 1 To max_ac
If ActiveWorkbook.ActiveSheet.Cells(2, col2) = "第二次抽查" And ActiveWorkbook.ActiveSheet.Cells(3, col2) = "抽查日期" Then Exit For
Next col2
For col3 = 1 To max_ac
If ActiveWorkbook.ActiveSheet.Cells(2, col3) = "第三次抽查" And ActiveWorkbook.ActiveSheet.Cells(3, col3) = "抽查日期" Then Exit For
Next col3
For col4 = 1 To max_ac
If ActiveWorkbook.ActiveSheet.Cells(2, col4) = "第四次抽查" And ActiveWorkbook.ActiveSheet.Cells(3, col4) = "抽查日期" Then Exit For
Next col4
For col5 = 1 To max_ac
If ActiveWorkbook.ActiveSheet.Cells(2, col5) = "第五次抽查" And ActiveWorkbook.ActiveSheet.Cells(3, col5) = "抽查日期" Then Exit For
Next col5
For i = 4 To max_ar
state1 = ActiveWorkbook.ActiveSheet.Cells(i, col1)
state2 = ActiveWorkbook.ActiveSheet.Cells(i, col2)
state3 = ActiveWorkbook.ActiveSheet.Cells(i, col3)
state4 = ActiveWorkbook.ActiveSheet.Cells(i, col4)
''''''''''''''''''''''''''''''第一次抽查''''''''''''''''''''''''''''''
If state1 = "" Then
a1 = 0
Else
If state2 = "" Then
a1 = 1
Else
If state1 <= state2 Then
a1 = 1
Else: a1 = 0
End If
End If
End If
If a1 = 0 Then ActiveWorkbook.ActiveSheet.Cells(i, 1) = ActiveWorkbook.ActiveSheet.Cells(i, 1) + "第一次抽查时间超出范围"
''''''''''''''''''''''''''''''第二次抽查''''''''''''''''''''''''''''''
If a1 = 0 Then
a2 = 0
Else
If state3 = "" Then
a2 = 1
Else
If state1 <= state3 And state2 <= state3 Then
a2 = 1
Else: a2 = 0
End If
End If
End If
If a1 = 1 And a2 = 0 Then ActiveWorkbook.ActiveSheet.Cells(i, 1) = ActiveWorkbook.ActiveSheet.Cells(i, 1) + "第二次抽查时间超出范围"
''''''''''''''''''''''''''''''第三次抽查''''''''''''''''''''''''''''''
If a2 = 0 Then
a3 = 0
Else
If state4 = "" Then
a3 = 1
Else
If state1 <= state4 And state2 <= state4 And state3 <= state4 Then
a3 = 1
Else: a3 = 0
End If
End If
End If
If a1 = 1 And a2 = 1 And a3 = 0 Then ActiveWorkbook.ActiveSheet.Cells(i, 1) = ActiveWorkbook.ActiveSheet.Cells(i, 1) + "第三次抽查时间超出范围"
''''''''''''''''''''''''''''''第四次抽查''''''''''''''''''''''''''''''
If a3 = 0 Then
a4 = 0
Else
If state5 = "" Then
a4 = 1
Else
If state1 <= state5 And state2 <= state5 And state3 <= state5 And state4 <= state5 Then
a4 = 1
Else: a4 = 0
End If
End If
End If
If a1 = 1 And a2 = 1 And a3 = 1 And a4 = 0 Then ActiveWorkbook.ActiveSheet.Cells(i, 1) = ActiveWorkbook.ActiveSheet.Cells(i, 1) + "第四次抽查时间超出范围"
Next i
End Sub
''''''''''''''''''''''''''''''TEP17''''''''''''''''''''''''''''''
Sub Final_Sign()
max_ar = ActiveWorkbook.ActiveSheet.Range("B65535").End(xlUp).Row
max_ac = ActiveWorkbook.ActiveSheet.Range("IV3").End(xlToLeft).Column
For col1 = 1 To max_ac
If ActiveWorkbook.ActiveSheet.Cells(3, col1) = "最终状态" Then Exit For
Next col1
For col2 = 1 To max_ac
If ActiveWorkbook.ActiveSheet.Cells(3, col2) = "最终拨打状态标记" Then Exit For
Next col2
For i = 4 To max_ar
state1 = ActiveWorkbook.ActiveSheet.Cells(i, col1)
If state1 = "成功" Or state1 = "回拨成功" Then
ActiveWorkbook.ActiveSheet.Cells(i, col2) = 1
ElseIf state1 = "不成功" Then
ActiveWorkbook.ActiveSheet.Cells(i, col2) = 0
End If
Next i
End Sub
Sub remind()
Dim i As Long
Dim x As Long
Dim y As Long
Dim col As Long
Dim max_ar As Long
Dim max_ac As Long
x = 0
y = 0
max_ar = ActiveWorkbook.ActiveSheet.Range("B65535").End(xlUp).Row
max_ac = ActiveWorkbook.ActiveSheet.Range("IV3").End(xlToLeft).Column
For col = 1 To max_ac
If ActiveWorkbook.ActiveSheet.Cells(3, col) = "最终拨打状态标记" Then Exit For
Next col
For i = 4 To max_ar
con = ActiveWorkbook.ActiveSheet.Cells(i, col)
If ActiveWorkbook.ActiveSheet.Cells(i, 1) <> "" Then x = x + 1
If con <> "" Then
Z = Z + 1
If con = 1 Then y = y + 1
End If
Next i
MsgBox ("存在" & x & "条错误数据！" & vbCrLf & "接通率为" & y * 100 / Z & "%")
End Sub
''''''''''''''''''''''''''''''TEP18''''''''''''''''''''''''''''''
Sub Update_Data()
Dim act_tt
Dim this_tt
Dim max_ac As Long
Dim max_ar As Long
Dim max_c As Long
Dim max_r As Long
Dim n As Long
actname = Application.ActiveWorkbook.Name
thisname = Application.ThisWorkbook.Name
max_ar = ActiveWorkbook.ActiveSheet.[B65536].End(xlUp).Row
max_ac = ActiveWorkbook.ActiveSheet.[IV1].End(xlToLeft).Column
max_r = ThisWorkbook.Worksheets("拨通明细").[B65536].End(xlUp).Row
max_c = ThisWorkbook.Worksheets("拨通明细").[IV1].End(xlToLeft).Column
'''清空内容-Start'''
If ThisWorkbook.Worksheets("拨通明细").Cells(3, 1) <> "Err" Then
For n = 4 To max_r
If ThisWorkbook.Worksheets("拨通明细").Range("B" & n) <> "" Then
ThisWorkbook.Worksheets("拨通明细").Rows("4:" & max_r).EntireRow.Delete shift:=xlUp
End If
Next n
max_r = ThisWorkbook.Worksheets("拨通明细").[B65536].End(xlUp).Row
ThisWorkbook.Worksheets("拨通明细").Columns(1).Insert
ThisWorkbook.Worksheets("拨通明细").Cells(3, 1) = "Err"
End If
'''清空内容-End'''
For i1 = 1 To max_ac
'''检索列名-Start'''
'''检索ThisWorkbook列-Start'''
If ActiveWorkbook.ActiveSheet.Cells(1, i1).MergeCells Then
act_cc = ActiveWorkbook.ActiveSheet.Cells(1, i1).MergeArea.Columns
act_r1 = Replace(Trim(act_cc(1, 1)), " ", "")
Else
act_r1 = ActiveWorkbook.ActiveSheet.Cells(1, i1)
End If
If ActiveWorkbook.ActiveSheet.Cells(2, i1).MergeCells Then
act_tt = ActiveWorkbook.ActiveSheet.Cells(2, i1).MergeArea.Columns
act_r2 = Replace(Trim(act_tt(1, 1)), " ", "")
Else
act_r2 = ActiveWorkbook.ActiveSheet.Cells(2, i1)
End If
act_r3 = Replace(Trim(ActiveWorkbook.ActiveSheet.Cells(3, i1)), " ", "")
'''检索ThisWorkbook列-End'''
'''检索ActiveWorkbook列-Start'''
For i2 = 1 To max_c
If ThisWorkbook.Worksheets("拨通明细").Cells(1, i2).MergeCells Then
this_cc = ThisWorkbook.Worksheets("拨通明细").Cells(1, i2).MergeArea.Columns
this_r1 = Replace(Trim(this_cc(1, 1)), " ", "")
Else
this_r1 = ThisWorkbook.Worksheets("拨通明细").Cells(1, i2)
End If
If ThisWorkbook.Worksheets("拨通明细").Cells(2, i2).MergeCells Then
this_tt = ThisWorkbook.Worksheets("拨通明细").Cells(2, i2).MergeArea.Columns
this_r2 = Replace(Trim(this_tt(1, 1)), " ", "")
Else
this_r2 = ThisWorkbook.Worksheets("拨通明细").Cells(2, i2)
End If
this_r3 = Replace(Trim(ThisWorkbook.Worksheets("拨通明细").Cells(3, i2)), " ", "")
If act_r2 = this_r2 And act_r3 = this_r3 Then Exit For
'''检索ActiveWorkbook列-End'''
Next i2
'''检索列名-End'''
'''Copy Content Start'''
If this_r1 = "抽查情况" Or this_r1 = "备注" Then
ActiveWorkbook.ActiveSheet.Range(ActiveWorkbook.ActiveSheet.Cells(4, i1), ActiveWorkbook.ActiveSheet.Cells(max_ar, i1)).Copy
Windows(thisname).Activate
ThisWorkbook.Worksheets("拨通明细").Cells(max_r + 1, i2).Select
ThisWorkbook.Worksheets("拨通明细").Paste
Windows(actname).Activate
Else
ActiveWorkbook.ActiveSheet.Range(ActiveWorkbook.ActiveSheet.Cells(4, i1), ActiveWorkbook.ActiveSheet.Cells(max_ar, i1)).Copy
Windows(thisname).Activate
ThisWorkbook.Worksheets("拨通明细").Cells(max_r + 1, i2).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows(actname).Activate
End If
'''Copy Content End'''
Next i1
End Sub
''''''''''''''''''''''''''''''TEP19''''''''''''''''''''''''''''''
Sub clean_up()
Dim max_c As Long
Dim max_r As Long
Dim n As Long
max_r = ThisWorkbook.Worksheets("拨通明细").[B65536].End(xlUp).Row
max_c = ThisWorkbook.Worksheets("拨通明细").[IV1].End(xlToLeft).Column
For n = 4 To max_r
If ThisWorkbook.Worksheets("拨通明细").Cells(n, 1) <> "" Then
ThisWorkbook.Worksheets("拨通明细").Rows(n).EntireRow.Delete
n = n - 1
End If
Next n
Columns(1).Delete
Columns(max_c).Delete
End Sub
''''''''''''''''''''''''''''''TEP20''''''''''''''''''''''''''''''
Sub Refresh_Data_Source()
Dim r As Long
Dim c As Long
Dim str
r = Sheets("拨通明细").Range("B65535").End(xlUp).Row
c = Sheets("拨通明细").Range("IV3").End(xlToLeft).Column - 1
str = Application.ThisWorkbook.Path & "\[" & Application.ThisWorkbook.Name & "]" & "拨通明细!R3C1:R" & r & "C" & c
ActiveSheet.PivotTables("PivotTable1").ChangePivotCache ThisWorkbook. _
PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
str _
, Version:=xlPivotTableVersion14)
End Sub