Sub ANfailed()
Dim i%, h%
Dim afax As String
Dim myws As Worksheet
Application.ScreenUpdating = False
For i = Sheets.Count To 1 Step -1
Application.DisplayAlerts = False
If Sheets(i).Name = "ANfail" Then
Sheets(i).Delete
End If
Application.DisplayAlerts = True
Next
Set myws = Sheets.Add(Count:=1)
myws.Name = "ANfail"
i = Sheets("Page1_1").[C666666].End(3).ROW
Sheets("Page1_1").Activate
Range("A13:R" & i).Copy
Sheets("ANfail").Activate
[a1].Select
ActiveSheet.Paste
[s1] = "Total Count"
[t1] = "Carrier"
[u1] = "Fax"
[v1] = "Fax V"
[W1] = "Total S"
i = Sheets("ANfail").[C666666].End(3).ROW
With Range("I1:I" & i)
.AutoFilter Field:=1, Criteria1:="GN"
.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
.AutoFilter
'.Clear
End With
i = Sheets("ANfail").[C666666].End(3).ROW
For h = 2 To i
Cells(h, 19) = Application.WorksheetFunction.CountIfs(Range("B1:B" & i), Cells(h, 2), Range("K1:K" & i), "<>")
Cells(h, 20) = Application.WorksheetFunction.CountIfs(Range("B1:B" & i), Cells(h, 2), Range("K1:K" & i), "Carrier Website")
If InStr(UCase(Cells(h, 11)), "@FAX") > 0 Then Cells(h, 21) = "Y"
Cells(h, 22) = Application.WorksheetFunction.CountIfs(Range("B1:B" & i), Cells(h, 2), Range("U1:U" & i), "Y")
If Range("s" & h) - Range("t" & h) - Range("v" & h) > 0 Then Cells(h, 23) = "D"
Next
With Range("W1:W" & i)
.AutoFilter Field:=1, Criteria1:="D"
.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
.AutoFilter
'.Clear
End With
i = Sheets("ANfail").[C666666].End(3).ROW
With Range("A1:W" & i)
.Borders.LineStyle = xlContinuous
.Font.Name = "Arial"
.Font.Size = 10
End With
Columns("A:W").AutoFit
[a1].Select
MsgBox "Done"
Application.ScreenUpdating = True
End Sub
07-19
“相关推荐”对你有帮助么?
-
非常没帮助
-
没帮助
-
一般
-
有帮助
-
非常有帮助
提交