代码有时会提示下标越界，但有时不会！ 请各位大神帮忙看看！

sparkwx

Sub WWWall()
Dim icol%, irow%, r%, c%, brr()
With Sheets("数据汇总窗口")
    irow = .[a65536].End(xlUp).Row
    icol = .[iv1].End(xlToLeft).Column
    ReDim brr(1 To irow, 1 To 4)
    For r = 3 To irow
        For c = 3 To icol
            If .Cells(r, c).Value = "" Then
                n = n + 1
                brr(n, 1) = .Cells(r, 1): brr(n, 2) = .Cells(r, 2): brr(n, 3) = .Cells(1, c): brr(n, 4) = .Cells(2, c):
            End If
        Next
    Next
End With
Sheets("缺交查询窗口").Range("A2:g1000").ClearContents
Range("A1") = "序号"
Range("b1") = "姓名"
Range("c1") = "日期"
Range("d1") = "内容"
Sheets("缺交查询窗口").[A2].Resize(UBound(brr), 4) = brr
Range("A2:B1000", "D2:D1000").Select
Selection.NumberFormatLocal = "G/通用格式"
Range("c2:c1000").Select
Selection.NumberFormatLocal = "yyyy-mm-dd"
Range("j1").Select
End Sub

不知道为什么

又不是第一次发帖，上传excel附件不可以？

sparkwx

我手脚有点慢慢，感谢帮忙！

sparkwx

顶一下，请各位帮忙看看

sparkwx

请各位帮忙看看，人工置顶

sparkwx

人工置顶，请各路大神帮忙

lsc900707

1. Sub 缺作业统计all()
   
2. Dim icol%, irow%, r%, c%, brr(1 To 999, 1 To 4)
   
3. With Sheets("数据汇总窗口")
   
4.     irow = .[a65530].End(xlUp).Row
   
5.     icol = .[iv1].End(xlToLeft).Column
   
6.     For r = 3 To irow
   
7.         For c = 3 To icol
   
8.             If .Cells(r, c).Value <> "" Then
   
9.                 n = n + 1
   
10.                 brr(n, 1) = .Cells(r, 1): brr(n, 2) = .Cells(r, 2): brr(n, 3) = .Cells(1, c): brr(n, 4) = .Cells(2, c)
    
11.             End If
    
12.         Next
    
13.     Next
    
14. End With
    
15. Range("A2:g1000").ClearContents
    
16. Range("A1:d1") = Array("序号", "姓名", "日期", "内容")
    
17. [A2].Resize(UBound(brr), 4) = brr
    
18. Range("A2:B1000", "D2:D1000").NumberFormatLocal = "G/通用格式"
    
19. Range("c2:c1000").NumberFormatLocal = "yyyy-mm-dd"
    
20. End Sub

复制代码

sparkwx

谢谢 看了版主  才知道代码应当如何写!

sparkwx

lsc900707 发表于 2018-9-28 19:02

谢谢版主,学到了很多的方法