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

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

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

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

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

TOP

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

TOP

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

TOP

¦^´_ 4# pcwh3

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

TOP

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

TOP

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

TOP

¦^´_ 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
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

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

TOP

        ÀR«ä¦Û¦b : ¦¨¥\¬OÀuÂIªºµo´§¡A¥¢±Ñ¬O¯ÊÂIªº²Ö¿n¡C
ªð¦^¦Cªí ¤W¤@¥DÃD