[µo°Ý] ½Ð°Ý¦³µL¯«¤H¥iÀ°¸Ñ¨M³o®æ¦¡¤Æ±ø¥óªº°ÝÃD

hank10459

¦U¦ì EXCEL¯«¤H±z¦n¡A·Q½Ð°Ý§Ú²{¦b°µ³o­Ó¹Ï¡A¦pªG¤À¬°2­Ó¬¡­¶(03BAY»P04(05)BAY)¡A·í04BAY¦³¸ê®Æ®É¡A03BAYªº¹ïÀ³¦ì¸mÅܦ¨¥´¤@­Ó¤j¤e¤e¡A¤£ª¾¦³µL°ª¤â¦³µL¿ìªk¥i¥Î©O? ¦pªG¦³¬Ý¤£À´±o¤p§Ì·|¦A¦h°µ»¡©ú¡C ÁÂÁÂ
¦p¹Ï¬°§Ú·QÅܦ¨ªº³o¼Ë¡C

seer_et

¦^´_ 1# hank10459

¨â­Ó¤èªk¥i¯à°µ¨ì§A­nªº:

1.¨Ï¥Î¦WºÙ

2.¨Ï¥Îindirect¨Ó³B²z§A­n®æ¦¡¤Æªº±ø¥ó°Ñ·Ó¡C

½Ð°Ñ¦Ò³o¸Ì:
http://tw.knowledge.yahoo.com/question/question?qid=1512071307819

hank10459

¦^´_ 2# seer_et
±z¦nS¤j¡A¥Ø«e§Úªº®æ¬O¤]¨S¿ìªk¥Î¨ì¨ººØX¹Ï¥Ü»¡!½Ð°Ý¦³¿ìªk¥i¥Hµ¹¥L¥´¤W¤j¤e¤e¶Ü?

sunnyso

¦^´_ 1# hank10459

¤W­Óªþ¥ó, ±a¦³§A¤½¦¡µ¥. §Ú¨Ó¸Ñ¨MXªº°ÝÃD

hank10459

¦^´_ 4# sunnyso
¦^¯«¤HS, §Ú¤w¸g¤W²î¹Ïªí^^³Â·Ð±zÅo Q1.rar (16.84 KB)

excel
¤U¸ü¦¸¼Æ: 10

2014-3-3 15:22

sunnyso

¤U¸ü (795.72 KB)

2014-3-4 13:56

Q1-Sonny.rar (20.9 KB)

¤U¸ü¦¸¼Æ: 11

2014-3-4 13:55

¦^´_ 5# hank10459

hank10459

¦^´_ 6# sunnyso
S¤j¡A±z¦n§Ú¦³¬Ý¨ì±zªº¦^ÂСA¦ý¦]¬°§ÚLEVEL¤£°÷...¥i§_E-MAILµ¹§Ú©O¡C§Úªº«H½c¬O[email protected]    ÁÂÁÂS¤j

sunnyso

¦^´_ 7# hank10459

here is the code.

1. Private Sub Worksheet_Activate()
   
2. 
   
3. With Sheets(2)
   
4.     blnk = .[R4] <> "" Or .[R5] <> "" Or .[R6] <> "" _
   
5.         Or .[S5] <> "" Or .[S6] <> "" _
   
6.         Or .[T5] <> "" Or .[T6] <> ""
   
7. End With
   
8. 
   
9. 
   
10. If blnk Then
    
11.         Range("R4:T6").Select
    
12.     With Selection
    
13.         .WrapText = False
    
14.         .Orientation = 0
    
15.         .AddIndent = False
    
16.         .ShrinkToFit = False
    
17.         .ReadingOrder = xlContext
    
18.         .MergeCells = True
    
19.     End With
    
20.     With Selection.Borders(xlDiagonalDown)
    
21.         .LineStyle = xlContinuous
    
22.         .ColorIndex = xlAutomatic
    
23.         .TintAndShade = 0
    
24.         .Weight = xlMedium
    
25.     End With
    
26.     With Selection.Borders(xlDiagonalUp)
    
27.         .LineStyle = xlContinuous
    
28.         .ColorIndex = xlAutomatic
    
29.         .TintAndShade = 0
    
30.         .Weight = xlMedium
    
31.     End With
    
32.     With Selection.Borders(xlEdgeLeft)
    
33.         .LineStyle = xlContinuous
    
34.         .ColorIndex = xlAutomatic
    
35.         .TintAndShade = 0
    
36.         .Weight = xlMedium
    
37.     End With
    
38.     With Selection.Borders(xlEdgeTop)
    
39.         .LineStyle = xlContinuous
    
40.         .ColorIndex = xlAutomatic
    
41.         .TintAndShade = 0
    
42.         .Weight = xlMedium
    
43.     End With
    
44.     With Selection.Borders(xlEdgeBottom)
    
45.         .LineStyle = xlContinuous
    
46.         .ColorIndex = xlAutomatic
    
47.         .TintAndShade = 0
    
48.         .Weight = xlMedium
    
49.     End With
    
50.     With Selection.Borders(xlEdgeRight)
    
51.         .LineStyle = xlContinuous
    
52.         .ColorIndex = xlAutomatic
    
53.         .TintAndShade = 0
    
54.         .Weight = xlMedium
    
55.     End With
    
56.     Selection.Borders(xlInsideVertical).LineStyle = xlNone
    
57.     Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    
58. Else
    
59.     Range("O4:Q6").Select
    
60.     ActiveWindow.SmallScroll Down:=-6
    
61.     Selection.Copy
    
62.     Range("R4").Select
    
63.     Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
    
64.         SkipBlanks:=False, Transpose:=False
    
65.     Application.CutCopyMode = False
    
66.     Range("F4:H6").Select
    
67.     Selection.Copy
    
68.     Range("U4:W6").Select
    
69.     Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
    
70.         SkipBlanks:=False, Transpose:=False
    
71.     Application.CutCopyMode = False
    
72. 
    
73. End If
    
74. 
    
75. 
    
76. End Sub

½Æ»s¥N½X

hank10459

¦^´_ 8# sunnyso


    ¦¬¨ì¤FS¤j¡CÁÂÁ±zªº´£¨Ñ¡C

Hsieh

¦^´_ 5# hank10459
®æ¦¡¤Æ±ø¥ó¬OµLªk±±¨î±×½uªº®æ½u
¬JµM¥ÎVBA»²§U¡A¥i¥H²³æ¤@ÂI
03BAY¤u§@ªí¼Ò²Õµ{¦¡½X

1. Private Sub Worksheet_Activate()
   
2. Dim r&, k&, i%
   
3. With Sheets("(04)05BAY")
   
4. For k = 3 To 33 Step 3 'CÄæ¨ìAGÄæ¡A¶¡¹j3Äæ´`Àô
   
5.    For r = 4 To 26 Step 3 '²Ä4¦C¨ì26¦C¡A¶¡¹j3¦C´`Àô
   
6.       If Application.CountA(.Cells(r, k).Resize(3, 3)) = 7 Then '¦pªG¥þ³¡¶ñº¡
   
7.          With Cells(r, k).Resize(3, 3)
   
8.             .Merge '¦X¨ÖÀx¦s®æ
   
9.             For i = 5 To 6
   
10.                With .Borders(i) 'µe±×½u
    
11.                   .LineStyle = xlContinuous
    
12.                End With
    
13.             Next
    
14.           End With
    
15.           Else '§_«h°õ¦æ½Æ»s¶K¤W¡B²M°£¤º®e
    
16.           .Cells(r, k).Resize(3, 3).Copy Cells(r, k): Cells(r, k).Resize(3, 3) = ""
    
17.        End If
    
18.     Next
    
19. Next
    
20. End With
    
21. End Sub

½Æ»s¥N½X

¤U¸ü (66.19 KB)

2014-3-5 14:52