|
本帖最后由 暴风雨一级 于 2022-5-31 16:39 编辑
“分解“工作簿里有一表格,o列是村组,现要按照o列里面内容按村按组分解出来,我已分别按村按组建立建立工作簿和工作表,写代码的主要思路是通过数组、字典创建村、组,再通过嵌套循环分解到村到组,现在的主要问题是循环问题,主要卡在当第一村正常分解以后,到下一个村时,因第一次循环后,变量m已经是最大数据行了,不知道循环到下一村时怎样让变量又从1开始循环,尝试重设初始值,未达目的,特请行家指点.上传有附件
For n = 0 To UBound(arrcm)
q = Application.WorksheetFunction.CountIf((ThisWorkbook.Sheets("面积明细表").Range("o5:o" & ThisWorkbook.Sheets("面积明细表").[a65536].End(3).Row)), arrcm(n) & "*")
ReDim crr(1 To q, 1 To 15)
For m = 1 To UBound(arr)
If Left(arr(m, 15), Len(arr(m, 15)) - 3) = arrcm(n) Then
u = u + 1
For y = 1 To UBound(arr, 2)
crr(u, y) = arr(m, y)
Next
End If
Next
For o = 1 To UBound(crr)
b(Right(crr(o, 15), 2)) = ""
Next
arrzm = b.keys
b.RemoveAll
For l = 0 To UBound(arrzm)
For r = 1 To UBound(crr)
If Right(crr(r, 15), 2) = arrzm(l) Then
k = k + 1
For p = 1 To UBound(crr, 2)
zrr(k, p) = crr(r, p)
Next
End If
Next
For Each wb In Workbooks
For Each sht In Worksheets
If wb.Name = arrcm(n) & ".xls" And sht.Name = arrzm(l) Then
sht.Range("a5").Resize(k, 15) = zrr
sht.Range("c2") = arrcm(n) & "村" & arrzm(l)
End If
Next
Next
Erase arr
k = 0
Next
Erase crr
u = 0
m = 1
Next
本帖最后由 釜底抽薪 于 2022-5-31 20:59 编辑
Erase arr 这里有问题
你把第一个村循环完了后 就把数组给清空了 你代码最前写了个 On Error Resume Next 所以就把M重置了 也不行,因为数据是空的。红色的我是添加上去的
Sub 分解1()
Application.ScreenUpdating = False
On Error Resume Next
Dim i, n, m, u, l, k, r, p, o
Dim wb As Workbook
Dim sht As Worksheet
Dim crr(), zrr(1 To 300, 1 To 15)
Set d = CreateObject("scripting.dictionary") '提取村名
Set b = CreateObject("scripting.dictionary") '提取组名
arr = ThisWorkbook.Sheets("面积明细表").Range("a5:o" & ThisWorkbook.Sheets("面积明细表").[a65536].End(3).Row)
For i = 1 To UBound(arr)
d(Left(arr(i, 15), Len(arr(i, 15)) - 3)) = ""
Next
arrcm = d.keys
d.RemoveAll
For n = 0 To UBound(arrcm)
q = Application.WorksheetFunction.CountIf((ThisWorkbook.Sheets("面积明细表").Range("o5:o" & ThisWorkbook.Sheets("面积明细表").[a65536].End(3).Row)), arrcm(n) & "*")
ReDim crr(1 To q, 1 To 15)
For m = 1 To UBound(arr)
If Left(arr(m, 15), Len(arr(m, 15)) - 3) = arrcm(n) Then
u = u + 1
For y = 1 To UBound(arr, 2)
crr(u, y) = arr(m, y)
Next
End If
Next
For o = 1 To UBound(crr)
b(Right(crr(o, 15), 2)) = ""
Next
arrzm = b.keys
b.RemoveAll
For l = 0 To UBound(arrzm)
For r = 1 To UBound(crr)
If Right(crr(r, 15), 2) = arrzm(l) Then
k = k + 1
For p = 1 To UBound(crr, 2)
zrr(k, p) = crr(r, p)
Next
End If
Next
Dim mpath As String
mpath = ThisWorkbook.Path & "\"
Set wb = Workbooks.Open(mpath & arrcm(n) & ".xls")
Set sht = wb.Sheets(arrzm(l))
' For Each wb In Workbooks
' For Each sht In Worksheets
' If wb.Name = arrcm(n) & ".xls" And sht.Name = arrzm(l) Then
sht.Range("a5").Resize(k, 15) = zrr
sht.Range("c2") = arrcm(n) & "村" & arrzm(l)
wb.Save
wb.Close
' End If
'
' Next
' Next
' Erase arr
k = 0
Next
Erase crr
u = 0
Next
Application.ScreenUpdating = True
End Sub
|
|