遍历20个左右的sheets统计数据问题,excel vba 有代码。

applee0721 applee0721 墨衍会员 · AI 创作全网分发 墨衍智能分发 通过墨衍一键同步至各平台 可同步平台 CSDN微信公众号微博知乎掘金百家号博客园抖音小红书 你的文章也可以这样分发 墨衍会员 ¥399起/年,写一次发全网 我也要 + 加入社区
具体要求是这样的:遍历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
...全文
评论 3
收藏 已收藏
分享
返回顶部
applee0721
Office开发/ VBA 5,174 成员 17,906 内容