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

¦p¦ó¨C¤é§ó·s¤£¦P¤é´ÁªºÀɮסA¶¶«K­pºâÀx¦s®æ¼Æ??

¦p¦ó¨C¤é§ó·s¤£¦P¤é´ÁªºÀɮסA¶¶«K­pºâÀx¦s®æ¼Æ??

[ª©¥DºÞ²z¯d¨¥]
  • GBKEE(2011/8/27 15:48): µo¤å¤Å¥Î¡A«Ü«æ, ±Ï©R... µ¥¦r²´

¥»©«³Ì«á¥Ñ zaq370 ©ó 2011-8-28 00:28 ½s¿è

¦p¦ó¨C¤é§ó·s¤£¦P¤é´ÁªºÀɮסA¶¶«K­pºâÀx¦s®æ¼Æ??(¦pªþÀÉ)

§Ú¤½¥q¦³¤@­Ó¨C¤é§ó·sªºÀÉ®×
½d¨ÒÄÒ¤ºªº  a,b,c,d,e ¬Oª¬ºA  1,2,3,4,5 ¬O¥h0827©Î0828Àɮתºª¬ºA¯Á¨ú¥X¸ê®Æ¨Ã­pºâ¨C­Óª¬ºA¦³¦h¤Ö­Ó??

§Ú°ÝÃD¬O¡A§Ú­n¦b¨C¤é§ó·sÀɮצA¶}±Ò®É¡A¥i¥H¦Û°Ê§ó·s¸Ì­±ª¬ºAªº¼Æ¾Ú(©Î¬O³]¤@­Ó«ö¶s§ó·s¤]¥i¥H)¡AÄ´¦p»¡¡A¤µ¤é¬O08/27´N§ì0827ÀɦWªº¸ê®Æ¡A©ú¤Ñ´N§ì0828ÀɦWªº¸ê®Æ¡C


Desktop.rar (9.37 KB)

¦^´_ 1# zaq370
§Ú¤w§xÂZ«Ü¤[
¥i¥H¬Ý¬Ý §A¹Á¸Õªºµ{¦¡½X¶Ü?

TOP

¦^´_ 1# zaq370
¸Õ¸Õ¬Ý
  1. Private Sub Workbook_Open()
  2. Set d = CreateObject("Scripting.Dictionary")
  3. wb = Format(Date, "mmdd")
  4. With Workbooks.Open(ThisWorkbook.Path & "\" & wb & ".xls")
  5. With .Sheets(1)
  6. For Each a In .Range(.[I2], .[I2].End(xlDown))
  7.   d(a.Value) = d(a.Value) + 1
  8. Next
  9. End With
  10. .Close
  11. End With
  12. With Sheets(1)
  13. For Each a In .Range(.[A2], .[A2].End(xlDown))
  14. a.Offset(, 1) = d(a.Value)
  15. Next
  16. End With
  17. End Sub
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 3# Hsieh


    ·PÁ¤j¤j!!

¥i¥H¨Ï¥Î!!¤£¦n·N«ä¡A§Ú¬O·s¤â¡I

¦³¤@¥yµ{¦¡½X¤£¤ÓÀ´"a.Offset(, 1) = d(a.Value)"
³o¥yªº·N«ä¬O«ü???
VBAªºµ{¦¡½X¦³ÂIÃøÀ´!!>"<

TOP

¦^´_ 4# zaq370


    ¯à²z¸Ña¥Nªí¬Æ»ò¶Ü?
¤F¸Ñaªº·N¸q¡A¦A¥h¬Ýoffset¨ç¼Æªº»¡©ú
d¬O¤@­Ó¦r¨åª«¥óÅܼơA½Ð¥ýª¦¤å¤F¸ÑDictionaryª«¥óªº¥Îªk
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¥»©«³Ì«á¥Ñ zaq370 ©ó 2011-8-31 11:06 ½s¿è

¦^´_ 5# Hsieh

¤j­ô§A¦n¡A¦A«ô°U¤@¤UÅo¡A²{¦b¤S¦³¤@­Ó°ÝÃD¡A¤ñ¦p·Q¤l¦b¹j¾ÀÄæ¦ì§ì¨ú¤W­Ó¤ë¦P¤@¤éªº¸ê®Æ(­Y¹J©P¤»¤é»P°²¤é«h§ì¨ú«e¤@¤é)¡A³o­Ó»yªk­n¦p¦ó¨Ï¥Î

Ä´¦p»¡¡A¤µ¤Ñ¬O8/31¸¹¡A¹j¾ÀÄæ¦ì­n§ì¨ú7/31¤éªº¸ê®Æ¡A¦ý7/31¬O©P¤é¡A«h­n§ì¨ú7/29ªº¸ê®Æ

TOP

¦^´_ 5# Hsieh


Sub Last_month()
Dim A As Range
Dim TheDate As Date

Set d = CreateObject("Scripting.Dictionary")

TheDate = Date

diff = DatePart("w", TheDate, vbUseSystem)


rr = DateAdd("d", (diff - 35), TheDate)

wb = Format(rr, "mmdd")

tt = Format(rr, "mm")

With Workbooks.Open(ThisWorkbook.Path & "\100" + tt & "\" & "NOC" + wb & ".xls")

   With .Sheets(1)
      For Each A In .Range(.[I2], .[I2].End(xlDown))
         If IsEmpty(d(A.Value)) Then
         d(A.Value) = Array(1, A.Offset(, 7).Value)
         Else
         ar = d(A.Value)
         ar(0) = ar(0) + 1
         ar(1) = ar(1) + A.Offset(, 7).Value
         d(A.Value) = ar
         End If
      Next
   End With
   
   
      
   .Close
End With
With Sheet1
For i = 5 To .[B65536].End(xlUp).Row Step 2
Set A = .Cells(i, 2)
A.Offset(, 4).Resize(2, 1) = Application.Transpose(d(A.Value))
Next
.Range("f19").Formula = "=f5+f7+f9+f11+f13+f15+f17"
.Range("f20").Formula = "=f6+f8+f10+f12+f14+f16+f18"
.Range("f45").Formula = "=f21+f33+f35+f37+f39+f41+f43"
.Range("f46").Formula = "=f22+f34+f36+f38+f40+f42+f44"
.Range("f47").Formula = "=f19+f45+f23+f25+f27+f29+f31"
.Range("f48").Formula = "=f20+f46+f24+f26+f28+f30+f32"

End With
End Sub

----------------------------------------------

¤j­ô¡G

¥Ø«e¬O¥i¥H§ì¨ú¤W­Ó¤ë¦P¤@¤éªºÀɮסA­Y¹J°²¤é©Î¤»¤é­n©¹«e±À«e¤@­Ó¤u§@¤é¡A­n¦p¦ó§ï¡H¨Ò¦p:7/31¬O¬P´Á¤é¡A­n§ì7/29¡A©Î¬O9/12¤¤¬î¸`¡A«h­n§ì9/9Àɮ׸ê®Æ¡A«ô°U!!

TOP

¦^´_ 7# zaq370

±qÀɮפ¤µLªk¸ò§Aªº±Ô­z°t¦X¡A½Ð¤W¶ÇÀÉ®×»¡©ú
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 8# Hsieh


    ¤£¦n·N«ä!!§Ñ°O¥[ÀÉ®×!!

Book222.rar (992.16 KB)

TOP

¦^´_ 9# zaq370

À³¬OÀɦW°ÝÃD§a
myday = DateAdd("m", -1, Date) - (Weekday(DateAdd("m", -1, Date), vbMonday) - 5)
wb = Format(myday, "mmdd")
tt = Format(myday, "mm")
With Workbooks.Open(ThisWorkbook.Path & "\100" & tt & "\" & "NOC" + wb & ".xls")
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

        ÀR«ä¦Û¦b : ¦n¨Æ­n´£±o°_¡A¬O«D­n©ñ±o¤U¡A¦¨´N§O¤H§Y¬O¦¨´N¦Û¤v¡C
ªð¦^¦Cªí ¤W¤@¥DÃD