[µo°Ý] ½Ð°Ý³o­Ó¥¨¶°¦p¦ó§ï¶i FIND

pcwh3

°ÝÃD¦pªþ¥ó,½Ð¦U¤j¤jÀ°¦£¤@¤U.
FIND TEST.zip (17.64 KB)

¤U¸ü¦¸¼Æ: 14

2012-3-2 13:05

pcwh3

¦^´_ 8# Hsieh
¦hÁÂhsieh¥S. ¤T­Óª©¥»¬Û¤ñ¸û, hsieh¥Sªºµ{¦¡µy¬°Ãø©ú¤@ÂI, ­n®É¶¡¥h¾Ç²ß²z¸Ñ.
¦ý¨¬¥HÃÒ©ú, ±ø±ø¤j¸ô³qù°¨.

Hsieh

¦^´_ 7# pcwh3

1. Sub WHREPORT()
   
2. Dim Rng As Range, A As Range, i%, k%
   
3. With ActiveSheet
   
4. st = .[A6]
   
5. d = Day(DateAdd("M", 1, st) - 1)
   
6. Set Rng = .Range("A6", .[A65536].End(xlUp)).SpecialCells(xlCellTypeBlanks)
   
7. Rng.Areas(2)(1).Offset(, 1).Resize(2, 16) = ""
   
8. Rng.Areas(1)(1).Offset(, 1).Resize(, 15).FormulaArray = "=COLUMN(A1:O1)"
   
9. Rng.Areas(1)(1).Offset(1, 1).Resize(, 15).FormulaR1C1 = "=UPPER(TEXT(TEXT(R6C1,""yyyy/mm/"")&R[-1]C,""ddd""))"
   
10. Rng.Areas(1)(1).Offset(, 1).Resize(2, 15) = Rng.Areas(1)(1).Offset(, 1).Resize(2, 15).Value
    
11. Rng.Areas(2)(1).Offset(1, 1).Resize(, d - 15).FormulaR1C1 = "=UPPER(TEXT(TEXT(R6C1,""yyyy/mm/"")&R[-1]C,""ddd""))"
    
12. Rng.Areas(2)(1).Offset(, 1).Resize(, d - 15).FormulaArray = "=TRANSPOSE(ROW(A16:A" & d & "))"
    
13. Rng.Areas(2)(1).Offset(, 1).Resize(2, d - 15) = Rng.Areas(2)(1).Offset(, 1).Resize(2, d - 15).Value
    
14. k = Rng.Areas(2).Row - Rng.Areas(1).Row
    
15. For i = 1 To 2
    
16. For Each A In Rng.Areas(i)(1).Offset(1, 1).Resize(, 15)
    
17.    If A = "MON" Then
    
18.    A.Offset(-1, 0).Resize(k, 1).Interior.ColorIndex = 6
    
19.    Else
    
20.    A.Offset(-1, 0).Resize(k, 1).Interior.ColorIndex = -4142
    
21.    End If
    
22. Next
    
23. Next
    
24. End With
    
25. End Sub

½Æ»s¥N½X

pcwh3

¦^´_ 5# register313
¦hÁ¦U¦ì. ¨â²Õµ{¦¡§Ú³£·|«ö§Úªº¹ê»Ú±¡ªp¨Ï¥Î¡A¦A¥h¤ñ¸û.
²{®É§Ú¬O¥ý¥Î±zªºµ{§Ç, ¦A¦Û¦æ¥[¤WGOSUB¥\¯à¥h¤@¦¸©Ê§¹¦¨¥þ³¡¤u§@ªí.

pcwh3

¦^´_ 3# GBKEE
¦hÁ«ü¾É.¤S¦h¾Ç¤@ÂI¤F.
§Ú¥Î¤F2¼Ó¤j¤jªºµ{¦¡, ¶i¦æ§ï¶i, ¦A¥ÎGOSUB¥\¯à§¹¦¨ª½±µ±N¦U¤u§@ªí¸ê®Æ¤@¦¸©Ê§ó·s.

register313

¦^´_ 4# pcwh3

§Ú¥u¦³³B²z²Ä¤@»P²Ä¤G¤u§@ªí
GBKEEª©¤jªº§@ªk¤~¯à³B²z¤T­Ó¤u§@ªí

pcwh3

¦^´_ 2# register313
¦hÁÂÀ°¦£.
­n§V¤O¾Ç¤@¤UUNION¤ÎRESIZEªº¥\¯à.

GBKEE

¦^´_ 1# pcwh3

1. Sub Ex()
   
2.     Dim Rng(4) As Range, xi As Integer, E As Range
   
3.     Set Rng(0) = Sheets("ABC").Range("A6")                              '¤é´Á
   
4.     Set Rng(1) = Sheets("ABC").Range("B7:P7,B16:Q16")           ' ¦U¤u§@ªí ¤é´Á ½d³ò
   
5.     Set Rng(2) = Sheets("XYZ").Range("B7:P7,B16:Q16")
   
6.     Set Rng(3) = Sheets("123").Range("B7:P7,B15:Q15")
   
7.     For xi = 1 To 3
   
8.         For Each E In Rng(xi)                                           '³B²z¨C¤@¤é´Á½d³ò
   
9.             If Month(Rng(0) + E.Value - 1) = Month(Rng(0)) Then     'Àˬd ¤ë¥÷
   
10.                 E.Offset(1) = UCase(Format(Rng(0) + E - 1, "ddd"))       '°Ñ¼Æ "ddd" ¶Ç¦^ ¬P´Á¦r¥ÀÁY¼g
    
11.                 E.Resize(IIf(xi = 3, 8, 9)).Interior.ColorIndex = IIf(E.Offset(1) = "MON", 36, xlNone)
    
12.             Else                                                                                             
    
13.                 E.Offset(1).Resize(IIf(xi = 3, 8, 9)) = ""
    
14.                 E.Resize(IIf(xi = 3, 8, 9)).Interior.ColorIndex = xlNone
    
15.             End If
    
16.        Next
    
17.     Next
    
18. End Sub

½Æ»s¥N½X

register313

¦^´_ 1# pcwh3

1. Sub XX()
   
2. Set Rng = Union(Range("B7:Q7"), Range("B16:Q16"))
   
3. For Each R In Rng
   
4.   If R = "" Then
   
5.      R.Offset(1, 0) = ""
   
6.   Else
   
7.      D = Weekday([A6] + R - 1, 2)
   
8.      R.Offset(1, 0) = Switch(D = 1, "MON", D = 2, "THU", D = 3, "WED", D = 4, "THU", D = 5, "FRI", D = 6, "SAT", D = 7, "SUN")
   
9.      If R.Offset(1, 0) = "MON" Then
   
10.         R.Resize(9, 1).Interior.ColorIndex = 36
    
11.      Else
    
12.         R.Resize(9, 1).Interior.ColorIndex = xlNone
    
13.      End If
    
14.   End If
    
15. Next
    
16. End Sub

½Æ»s¥N½X