遍历20个左右的sheets统计数据问题,excel vba 有代码。
具体要求是这样的:遍历sheet1到最后一个sheet,如果第F列的值是“PRC”,即中国。则统计32个省、直辖市A、B、C、D各种不同类型公司的数据;如果F列的值是其他,则统计其他A、B、C、D各种不同类型公司的数据。其中省、直辖市的代码在H列,公司类型代码A、B、C、D在J列。是分别统计各个sheet的数据,而不是记各种工作表A、B、C、D的数据。主要是遍历各个sheet不太会。跪求高人指点。分只有这么多了。谢谢大家了。代码如下所示:
Option Explicit
Type dir
entityName As String
district As String
shareType As String
periodic As Integer
End Type
Sub subTest()
Dim dirArr() As dir
Dim dirTest As dir
Dim sh As Worksheet
Dim rowNum As Integer
Dim index As Integer
Dim index1 As Integer
Dim i As Integer
Dim shiti As Variant
Dim entity As String
Dim dis As String
Dim share As String
Dim count As Integer
Dim addFlag As Boolean
ReDim dirArr(0)
Set sh = ThisWorkbook.Worksheets("sheet1")
For Each sh In ThisWorkbook.Worksheets
For i = 4 To ThisWorkbook.Worksheets.count
rowNum = ThisWorkbook.Worksheets(i).Range("D4").End(xlDown).Row
For index = 5 To rowNum
entity = sh.Range("D4").Value
share = sh.Range("J" & index).Value
If sh.Range("F" & index).Value = "PRC" Then
dis = sh.Range("H" & index).Value
Else
dis = sh.Range("F" & index).Value
End If
addFlag = True
For index1 = 0 To UBound(dirArr)
If dirArr(index1).entityName = entity Then
If dirArr(index1).district = dis Then
If dirArr(index1).shareType = share Then
dirArr(index1).periodic = dirArr(index1).periodic + 1
addFlag = False
End If
End If
End If
Next
If addFlag Then
With dirTest
.entityName = entity
.district = dis
.shareType = share
.periodic = 1
End With
dirArr(UBound(dirArr)) = dirTest
ReDim Preserve dirArr(UBound(dirArr) + 1)
End If
Next
Next
Next
ReDim Preserve dirArr(UBound(dirArr) - 1)
For index = 0 To UBound(dirArr)
ThisWorkbook.Worksheets("SIS_DATA2").Range("A" & index ).Value = "2011"
ThisWorkbook.Worksheets("SIS_DATA2").Range("B" & index ).Value = "Act"
ThisWorkbook.Worksheets("SIS_DATA2").Range("C" & index ).Value = "N/A"
ThisWorkbook.Worksheets("SIS_DATA2").Range("D" & index ).Value = dirArr(index).entityName
ThisWorkbook.Worksheets("SIS_DATA2").Range("E" & index ).Value = "Q3"
ThisWorkbook.Worksheets("SIS_DATA2").Range("F" & index ).Value = "T2_CRSI010001"
ThisWorkbook.Worksheets("SIS_DATA2").Range("G" & index ).Value = dirArr(index).district
ThisWorkbook.Worksheets("SIS_DATA2").Range("H" & index ).Value = dirArr(index).shareType
ThisWorkbook.Worksheets("SIS_DATA2").Range("I" & index ).Value = dirArr(index).periodic
ThisWorkbook.Worksheets("SIS_DATA2").Range("J" & index ).Value = "N/A"
Next
End Sub