|
楼主 |
发表于 2016-3-23 16:32
|
显示全部楼层
完整代码:
Sub CollectData()
'Workbooks.Open Filename:="C:\1_Cisco Folder\NYS\PPM Reports\PID and TAN Data from PPM-v12.xlsx"
'Workbooks.Open Filename:="C:\1_Cisco Folder\NYS\PPM Reports\PPM NPI Project Report_v42.xlsx"
'Workbooks.Open Filename:="C:\1_Cisco Folder\NPI FCS_GAC Field Performance Study\Copy of GIMs Data_29_Feb_2016.xlsx"
'Workbooks.Open Filename:="C:\1_Cisco Folder\NPI FCS_GAC Field Performance Study\IR Status Details_0229.xlsx"
'Put PID under NPI program
ProgramName = Application.InputBox("Enter the NPI Program Name")
Dim x As Integer
Dim y As Integer
y = 5
For x = 1 To Workbooks("PID and TAN Data from PPM-v12").Sheets("PID and TAN Export").UsedRange.Rows.Count
If Workbooks("PID and TAN Data from PPM-v12").Sheets("PID and TAN Export").Cells(x, 1) = ProgramName And Workbooks("PID and TAN Data from PPM-v12").Sheets("PID and TAN Export").Cells(x, 11) = "PID" Then
Cells(y, 8) = Workbooks("PID and TAN Data from PPM-v12").Sheets("PID and TAN Export").Cells(x, 10)
y = y + 1
End If
Next
'Statistics
LScount = 0
LAcount = 0
For gimsrow = 2 To Workbooks("Copy of GIMs Data_29_Feb_2016").Sheets("Analysis").UsedRange.Rows.Count
Dim rowfind As Integer
Dim PIDrow As Integer
rowfind = 0
For PIDrow = 5 To y
If InStr(1, Workbooks("Copy of GIMs Data_29_Feb_2016").Sheets("Analysis").Cells(16, 14).Text, ActiveSheet.Cells(PIDrow, 8).Text, 1) <> 0 Then
rowfind = rowfind + 1
End If
Next
If rowfind > 0 And InStr(Workbooks("Copy of GIMs Data_29_Feb_2016").Sheets("Analysis").Cells(gimsrow, 27).Value, "LS") <> 0 Then
LScount = LScount + 1
End If
'
If rowfind > 0 And InStr(Workbooks("Copy of GIMs Data_29_Feb_2016").Sheets("Analysis").Cells(gimsrow, 27).Value, "LA") <> 0 Then
LAcount = LAcount + 1
End If
Next
Cells(4, 12) = LScount
Cells(4, 13) = LAcount
End Sub
|
|