- ©«¤l
- 2035
- ¥DÃD
- 24
- ºëµØ
- 0
- ¿n¤À
- 2031
- ÂI¦W
- 0
- §@·~¨t²Î
- Win7
- ³nÅ骩¥»
- Office2010
- ¾\ŪÅv
- 100
- ©Ê§O
- ¨k
- µù¥U®É¶¡
- 2012-3-22
- ³Ì«áµn¿ý
- 2024-2-1
|
¦^´_ 1# sunnyso
¸Õ¸Õ³oÓµ{¦¡½X¡A¯Ó®É 0.921875- Sub Ex_VBA_Array() ' VBA Code Array
- Dim RowsCnt As Long, m As Long, SubTotalAr() As Double
- Dim t1 As Variant, t2 As Variant, AllType As Variant
- Dim DataArea As Variant
- Dim i%, j%
-
- t1 = Timer
- AllType = Array("AÃþ", "BÃþ", "CÃþ", "DÃþ", "EÃþ", "FÃþ", "GÃþ", "HÃþ", "IÃþ", "JÃþ")
- ReDim SubTotalAr(0 To UBound(AllType), 0 To 11)
- Application.ScreenUpdating = False
-
- ' ²M²z¼ƾÚ
- ' Sheets("Á`ªí").Activate
- Sheets("Á`ªí").Range("A3").CurrentRegion.Offset(1, 1).Clear
-
- With Sheets("ì©l¸ê®Æ")
- RowsCnt = .Range("A1").CurrentRegion.Rows.Count
- DataArea = .Range("A2").Resize(RowsCnt, 3)
-
- For m = 1 To UBound(DataArea)
- For i = 0 To UBound(AllType) ' AÃþ To JÃþ
- If DataArea(m, 1) = AllType(i) Then ' Jan to Dec
- SubTotalAr(i, Month(DataArea(m, 2)) - 1) = SubTotalAr(i, Month(DataArea(m, 2)) - 1) + DataArea(m, 3)
- End If
- Next i
- Next m
- End With
-
- With Sheets("Á`ªí")
- .Range("B4").Resize(UBound(AllType) + 1, 12) = SubTotalAr
- .Range("N4:N13").FormulaR1C1 = "=SUM(RC[-12]:RC[-1])"
- For i = 0 To 3
- ' .Range(Chr(79 + i) & 4 & ":" & Chr(79 + i) & 13).FormulaR1C1 = "=SUM(RC[-" & (13 - i * 2) & "]:RC[-" & (11 - i * 2) & "])"
- .Range(Chr(79 + i) & 4).Resize(UBound(AllType) + 1).FormulaR1C1 = "=SUM(RC[-" & (13 - i * 2) & "]:RC[-" & (11 - i * 2) & "])"
- .Range(Chr(79 + i) & 4).Resize(UBound(AllType) + 1) = .Range(Chr(79 + i) & 4).Resize(UBound(AllType) + 1).Value
- Next i
- .Range("B14:R14").FormulaR1C1 = "=SUM(R[-10]C:R[-1]C)"
- End With
- Application.ScreenUpdating = True
- t2 = Timer
- MsgBox "¯Ó®É" & t2 - t1
- ' Sheets("ì©l¸ê®Æ").[F3] = "¯Ó®É¡G " & (t2 - t1)
- End Sub
½Æ»s¥N½X
|
|