ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

[µo°Ý] ½Ð°Ý¤é»ù®æ«ç»òÂàÅܦ¨¶g»ù®æ©O?

[µo°Ý] ½Ð°Ý¤é»ù®æ«ç»òÂàÅܦ¨¶g»ù®æ©O?

¤£ª¾¹D³o¯à¥Î¤½¦¡¸Ñ¨M¶Ü?ÁÙ¬O­n¼gµ{¦¡½X¤~¯à§¹¦¨©O?

Book.rar (9.16 KB)

¦^´_ 1# jovi0801
¸Õ¸Õ¬Ý
  1. Sub Ex() 'DatePart("WW", .Rows(xi).Cells(1))  ¶Ç¦^²Ä´X¶g
  2.     Dim Rng As Range, xi As Integer
  3.     With Sheets("¤é»ù®æ").Range("A1").CurrentRegion.Rows
  4.         Set Rng = .Rows(1)
  5.         xi = 2
  6.         Do
  7.             If DatePart("WW", .Rows(xi).Cells(1)) <> DatePart("WW", .Rows(xi + 1).Cells(1)) Then Set Rng = Union(Rng, .Rows(xi))
  8.             xi = xi + 1
  9.            If xi = .Rows.Count Then Set Rng = Union(Rng, .Rows(xi))
  10.         Loop While xi < .Rows.Count
  11.     End With
  12.     With Sheets("©P»ù®æ")
  13.         .UsedRange.Clear
  14.         Rng.Copy .[a1]
  15.     End With
  16. End Sub
½Æ»s¥N½X

TOP

¦^´_ 2# GBKEE
·PÁª©¥Dªº¸Ñµª,³z¹Lª©¥Dªºµ{¦¡½X,¥u­n§â"WW"Åܦ¨"m"´N·|Åܦ¨¤ë»ù®æÅo!

TOP

¦^´_ 2# GBKEE


    ¤£¦n·N«ä,§Ú¥J²Ó¤@¬Ý,³oµ{¦¡½X,¬O§â¨C¶g¤­ªº¶}§C¦¬°ª¨q¥X¨Ó,¨º§Ú·Q§â¶}½L»ù³]¦b¶g¤@,³Ì°ª³Ì§C¬O³o¦b¶g©Ò¥X²{ªº¼Æ­È,¦¬½L·íµM¶g¤­³Ì«áªº»ù®æ,§Ú¦³¬Ý¨ì¹ï©¤¼gªºµ{¦¡½X,¦ý§Ú¤£ª¾¹D­n±q¦Ó§ï°_,½Ðª©¥D¬Ý¬Ý
Sub bwwweek()
¡¥«Ø¥ß·sªº¤u§@ªí, ¦s©ñ©P数Õu
ActiveWorkbook.Sheets.Add after:=Worksheets(1)
Worksheets(1).Activate

¡¥§â²Ä¤@¦æ数Õu¦s¨ì变¶q¡Ahand为¦æ号¡Ah为³Ì°ª¡Al为³Ì§C¡Ac为¦¬盘¡Ad为¤é´Á¡Avol为¦¨¥æªÑ数¡Aamt为¦¨¥æª÷额

Dim hand As Integer, h As Double, l As Double, _
c As Double, d As Date, o As Double, _
vol As Long, amt As Long
hand = 1
d = Worksheets(1).Cells(hand, 1).Value

¡¥¥æ©ö²Ä¤@¤ÑÖÃ开¬P´Á¤­¦³¤L¤Ñ,n5计数¾¹¡Afri储¦s¬P´Á¤­ªº¤é´Á
Dim n5 As Integer, md5 As Date
For n5 = 0 To 4
fri = d + n5
If Weekday(fri) ¡V 1 = 5 Then
Exit For
End If
Next n5

¡¥²Ä¤@©Pªº¦æ数,row1为¥»©P²Ä¤@¥æ©ö¤é¦æ数¡Arow5为¥»©P³Ì¦Z¥æ©ö¤é¦æ数, frim暂储¦s¤@©P内¥æ©ö¤éªº数Õu, ¥Î¤_©M这©Pfri¤é´Á¤ñ较,¤j¤_fri§Y计ºâ¥»©P³Ì¦Z¥æ©ö¤é¦æ数
Dim row1 As Integer, row5 As Integer, frim As Date
row1 = 1

Dim n As Integer
For n = 1 To 6
If frim > fri Then
row5 = n ¡V 1
Exit For
End If
frim = Worksheets(1).Cells((hand + n), 1).Value
Next n

¡¥§â总¦@ªÑ²¼¤éK线­ì©l数Õuªº¦æ数储¦s¦brng变¶q
Dim rng As Integer
rng = Range(¡§A65536¡²).End(xlUp).Row

Dim whand As Integer, wdate As Date
whand = 1
¡¥==========================================================
While row1 <= rng
¡¥¡X¡X¡X¡X¡X¡X¡X¡X¡X¡X¡X¡X¡X¡X¡X¡X¡X¡X
If frim >= fri Or frim = 0 Then

¡¥§â¤@©P开盘,¦¬盘,¶qµ¥¦s¤J变¶q, ¥Î¤_¤U­±写¤J©Pªº·s¤u§@ªí
wopen = Worksheets(1).Cells(row1, 2).Value
whigh = Application.WorksheetFunction.Max(Range(Cells(row1, 3), Cells(row5, 3)))
wlow = Application.WorksheetFunction.Min(Range(Cells(row1, 4), Cells(row5, 4)))
wclose = Worksheets(1).Cells(row5, 5).Value
wdate = Worksheets(1).Cells(row1, 1).Value

hand = row5 + 1

Worksheets(2).Cells(whand, 1).Value = wdate
Worksheets(2).Cells(whand, 2).Value = wopen
Worksheets(2).Cells(whand, 3).Value = whigh
Worksheets(2).Cells(whand, 4).Value = wlow
Worksheets(2).Cells(whand, 5).Value = wclose
whand = whand + 1

End If
¡¥¡X¡X¡X¡X¡X¡X¡X¡X¡X¡X¡X¡X¡X¡X¡X¡X¡X¡X¡X¡X-
If hand <= rng And frim <> 0 Then

frim = Worksheets(1).Cells(hand, 1).Value
¡¥-  -  -  -  -  -  -
For n5 = 0 To 4
fri = frim + n5

If Weekday(fri) ¡V 1 = 5 Then
Exit For
End If
Next n5
¡¥-  -  -  -  -  -  -

row1 = row5 + 1
¡¥///////////////////////////////////////////////////
For n = 1 To 6
If frim > fri Or frim = 0 Then
row5 = row5 + n ¡V 1
Exit For
End If
frim = Worksheets(1).Cells((hand + n), 1).Value
Next n
¡¥//////////////////////////////////////////////////
Else
row1 = row5 + 1
End If
¡¥¡X¡X¡X¡X¡X¡X¡X¡X¡X¡X¡X¡X¡X¡X¡X¡X¡X¡X¡X¡V
Wend
¡¥===================================================
End Sub

TOP

¥»©«³Ì«á¥Ñ GBKEE ©ó 2012-7-10 16:02 ½s¿è

¦^´_ 4# jovi0801
  1. Sub Ex() 'DatePart("WW", .Rows(xi).Cells(1))  ¶Ç¦^²Ä´X¶g
  2.     Dim AR, xi As Integer, xAr As Integer, Rng As Range
  3.     With Sheets("¤é»ù®æ")
  4.         AR = Application.Transpose(.Range("A1").CurrentRegion.Rows(1).Value)         '¨ú±oÄæ¦ì
  5.         xAr = 2
  6.         xi = 2
  7.         ReDim Preserve AR(1 To 5, 1 To xAr)                             '·s¼W¤@ºûªÅ¥Õ°}¦C
  8.         Set Rng = .Cells(xi, "A")                                       '¤@¶gÀç·~ªº²Ä¤@¤Ñ¤é´Á¦ì¸m
  9.         Do While .Cells(xi, "A") <> ""
  10.             If DatePart("WW", .Cells(xi, "A")) <> DatePart("WW", .Cells(xi + 1, "A")) Then
  11.                 Set Rng = Range(Rng, .Cells(xi, "E"))                   '¤@¶gÀç·~ªº²Ä¤@¤Ñ¤é´Á¦ì¸m  ¨ì  ³Ì«á²Ä¤@¤Ñ¦¬½L»ù¦ì¸m
  12.                 AR(1, xAr) = Rng.Cells(1)                               '¤é´Á  Rng.Cells(1) ¤é´Á: ¶g¤@  ©Î .Cells(xi, "A") ¤é´Á: ¶g¤­(³Ì«á¤@¤Ñ)
  13.                 AR(2, xAr) = Rng.Cells(1, 2)                            '¶}½L»ù
  14.                 AR(3, xAr) = Application.Max(Rng.Columns(3))            '³Ì°ª»ù
  15.                 AR(4, xAr) = Application.Min(Rng.Columns(4))             '³Ì§C»ù
  16.                 AR(5, xAr) = .Cells(xi, "E")                            '¦¬½L»ù
  17.                 If .Cells(xi + 1, "A") <> "" Then
  18.                     Set Rng = .Cells(xi + 1, "a")
  19.                     xAr = xAr + 1
  20.                     ReDim Preserve AR(1 To 5, 1 To xAr)
  21.                 End If
  22.             End If
  23.             xi = xi + 1
  24.         Loop
  25.     End With
  26.     With Sheets("©P»ù®æ")
  27.         .UsedRange.Clear
  28.         .[A1].Resize(xAr, 5) = Application.Transpose(AR)
  29.     End With
  30. End Sub
½Æ»s¥N½X

TOP

¦^´_ 4# jovi0801
¦pªG³o¬O¥æ©ö¬ö¿ý¡A¤£À³¸Ó§PÂ_©P¤@»P©P¤­¬°°Ï¶¡
¦]¬°¦³¥i¯à¹J¨ì°²¤é¥ð¥«¡A¥H©P§O§P©w¤£ª¾¬O§_¤ñ¸û¥¿½T?
  1. Sub ex()
  2. Set d = CreateObject("Scripting.Dictionary")
  3. Set d1 = CreateObject("Scripting.Dictionary")
  4. With Sheet1
  5. d1(1) = Array(.[A1].Value, .[B1].Value, .[C1].Value, .[d1].Value, .[E1].Value)
  6. For Each a In .Range(.[A2], .[A2].End(xlDown))
  7. w = Year(a) & ":" & Application.WorksheetFunction.WeekNum(a)
  8. If IsEmpty(d(w & "¶}")) Then d(w & "¶}") = a.Offset(, 1).Value
  9. If a.Offset(, 2) > d(w & "°ª") Then d(w & "°ª") = a.Offset(, 2).Value
  10. If IsEmpty(d(w & "§C")) Then
  11. d(w & "§C") = a.Offset(, 3).Value
  12. ElseIf d(w & "§C") > a.Offset(, 3) Then
  13. d(w & "§C") = a.Offset(, 3).Value
  14. End If
  15. d(w & "¦¬") = a.Offset(, 4).Value
  16. d1(w) = Array(a.Value, d(w & "¶}"), d(w & "°ª"), d(w & "§C"), d(w & "¦¬"))
  17. Next
  18. End With
  19. With Sheet2
  20. .Cells = ""
  21. .[A1].Resize(d1.Count, 5) = Application.Transpose(Application.Transpose(d1.items))
  22. End With
  23. End Sub
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 5# GBKEE
¦A¦¸·PÁÂGBKEEª©¥Dªº¨ó§U,¸Ñ¨M§Úªº°ÝÃD

TOP

¦^´_ 6# Hsieh
ÁÂÁÂHsieh¶W¯Åª©¥Dªº´£¿ô,¦ý®M¥Î±zªºµ{¦¡½X,·|¥X²{ª«¥ó¤£¤ä´©¦¹ÄݩʩΤèªkªº°T®§,¦ÓGBKEEª©¥D©Ò¼gªºµ{¦¡½X,¦n¹³¯à¹F¨ì±z©Ò»¡ªº®ÄªG,¦pªþ¥ó Book.rar (10.13 KB)

TOP

¦^´_ 5# GBKEE
¦^´_ 8# jovi0801
§Ú¥[¤W¤F¤@Äæ "´Á¶¡" (scope) ¦p¦¹¥i¥H²M·¡¦a¬Ý¥X¥¦­ÌªºÂkÄÝ¡C
¯Â°Ñ¦Ò¡A§Æ±æ GBKEE ¤j¤j²ö¤¶·N¡C
  1. Sub Ex()                           ' DatePart("WW", .Rows(xi).Cells(1))  ¶Ç¦^²Ä´X¶g
  2.     Dim AR, xi As Integer, xAr As Integer, Rng As Range, scope As String
  3.    
  4.     With Sheets("¤é»ù®æ")
  5.         AR = Application.Transpose(.Range("A1").CurrentRegion.Rows(1).Value)     ' ¨ú±oÄæ¦ì
  6.         xAr = 2
  7.         xi = 2
  8.         ReDim Preserve AR(1 To 6, 1 To xAr)                      ' ·s¼W¤@ºûªÅ¥Õ°}¦C
  9.         
  10.         Set Rng = .Cells(xi, "A")                                ' ¤@¶gÀç·~ªº²Ä¤@¤Ñ¤é´Á¦ì¸m
  11.         scope = .Cells(xi, "A")
  12.         
  13.         Do While .Cells(xi, "A") <> ""
  14.             If DatePart("WW", .Cells(xi, "A")) <> DatePart("WW", .Cells(xi + 1, "A")) Then
  15.                 Set Rng = Range(Rng, .Cells(xi, "E"))            ' ¤@¶gÀç·~ªº²Ä¤@¤Ñ¤é´Á¦ì¸m  ¨ì  ³Ì«á²Ä¤@¤Ñ¦¬½L»ù¦ì¸m
  16.                 AR(1, xAr) = Rng.Cells(1)           ' ¤é´Á  Rng.Cells(1) ¤é´Á: ¶g¤@  ©Î .Cells(xi, "A") ¤é´Á: ¶g¤­(³Ì«á¤@¤Ñ)
  17.                 AR(2, xAr) = Rng.Cells(1, 2)                     ' ¶}½L»ù
  18.                 AR(3, xAr) = Application.Max(Rng.Columns(3))     ' ³Ì°ª»ù
  19.                 AR(4, xAr) = Application.Min(Rng.Columns(4))     ' ³Ì§C»ù
  20.                 AR(5, xAr) = .Cells(xi, "E")                     ' ¦¬½L»ù
  21.                 AR(6, xAr) = scope & "-" & .Cells(xi, "A")
  22.                
  23.                 If .Cells(xi + 1, "A") <> "" Then
  24.                     Set Rng = .Cells(xi + 1, "A")
  25.                     xAr = xAr + 1
  26.                     scope = .Cells(xi + 1, "A")
  27.                     ReDim Preserve AR(1 To 6, 1 To xAr)
  28.                 End If
  29.             End If
  30.             xi = xi + 1
  31.         Loop
  32.     End With
  33.    
  34.     With Sheets("©P»ù®æ")
  35.         .UsedRange.Clear
  36.         .[A1].Resize(xAr, 6) = Application.Transpose(AR)
  37.     End With
  38. End Sub
½Æ»s¥N½X
¤é»ù®æ«ç»òÂàÅܦ¨¶g»ù®æ.rar (9.85 KB)

TOP

¦^´_ 9# c_c_lai
¥[¤W´Á¶¡³o¤£¿ù°Ú ,¦ý¥i¤£»Ý·s¼WÅܼƨÓÅã¥Ü.   
AR(6, xAr) = scope & "-" & .Cells(xi, "A")   ->      AR(6, xAr) = Rng.Cells(1) & "-" & .Cells(xi, "A")

TOP

        ÀR«ä¦Û¦b : °ß¨ä´L­«¦Û¤vªº¤H¡A¤~§ó«i©óÁY¤p¦Û¤v¡C
ªð¦^¦Cªí ¤W¤@¥DÃD