|
楼主 |
发表于 2012-5-1 11:19
|
显示全部楼层
本帖最后由 金子 于 2012-5-1 11:36 编辑
数组写单元格的问题2.rar
(16.36 KB, 下载次数: 20)
'快的代码
- Sub WhetherExpire_1()
- Dim arr, FinalRow As Long, t As Single, i As Long
-
- t = Timer
- AutoFillExample
- FinalRow = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
- Sheets("Sheet1").Range("E2:E" & FinalRow).ClearContents
- arr = Sheet1.Range("A2", "D" & FinalRow)
-
- For i = 1 To UBound(arr)
- If arr(i, 4) - Date <= 0 Then
- ' Debug.Print i
- '数据量大的情况下,下面一行代码每次都写单元格影响速度,如何将它记在另一数组中,然后一次性回写单元格
- Cells(i + 1, 5) = arr(i, 1) & " 借款已于 " & arr(i, 4) & " 到期"
- End If
- Next
-
- Cells(1, 7) = Timer - t
- End Sub
复制代码 为何下面的代码反到更慢?
- Sub WhetherExpire_3()
- Dim arr, brr(), FinalRow As Long, t As Single, i As Long
- t = Timer
- AutoFillExample
- With Sheets("Sheet1")
- FinalRow = .Range("A" & Rows.Count).End(xlUp).Row
- .Range("E2:E" & FinalRow).ClearContents
- arr = .Range("A2", "D" & FinalRow)
- For i = 1 To UBound(arr)
- ReDim Preserve brr(1 To i)
- If arr(i, 4) - Date <= 0 Then
- brr(i) = arr(i, 1) & " 借款已于 " & arr(i, 4) & " 到期"
- End If
- Next
- .[E2].Resize(UBound(brr), 1) = Application.Transpose(brr)
- .Cells(1, 6) = Timer - t
- End With
- End Sub
复制代码 |
|