Hsieh

¦^´_ 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

Hsieh

¦^´_ 4# zaq370


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

Hsieh

¦^´_ 7# zaq370

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

Hsieh

¦^´_ 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")

Hsieh

¦^´_ 11# zaq370

myday = Application.WorkDay(DateAdd("m", -1, Date), IIf(Weekday(DateAdd("m", -1, Date), vbMonday) > 5, -1, 0))'«e¤@­Ó¤ë
wb = Format(myday, "mmdd")
tt = Format(myday, "mm")
With Workbooks.Open(ThisWorkbook.Path & "\100" & tt & "\" & "NOC" + wb & ".xls")
¤£¹L³o¼Ëªº±¡§Î¦pªG¤j¤p¤ë¤£¦P®É¤£ª¾¹D§A­n«ç»ò§ì¸ê®Æ