yen956

§Ú¤]¸Õ¸Õ¬Ý:

1. Sub TEST1()
   
2.     Dim dv As Object, d0 As Object, dx As Object, E
   
3.     Set dv = CreateObject("Scripting.Dictionary")
   
4.     Set d0 = CreateObject("Scripting.Dictionary")
   
5.     Set dx = CreateObject("Scripting.Dictionary")
   
6.     For Each E In Range([B2], [B65536].End(xlUp))
   
7.         If E.Offset(0, 1) = "V" Then dv.Item(E) = ""
   
8.         If E.Offset(0, 1) = "O" Then d0.Item(E) = ""
   
9.         If E.Offset(0, 1) = "X" Then dx.Item(E) = ""
   
10.     Next
    
11.     [E4].Resize(1, 40) = ""
    
12.     [E4].Resize(1, dv.Count) = dv.Keys
    
13.     [E4].Offset(0, dv.Count + 2).Resize(1, d0.Count) = d0.Keys
    
14.     [E4].Offset(0, dv.Count + 2 + d0.Count + 2).Resize(1, dx.Count) = dx.Keys
    
15. End Sub

½Æ»s¥N½X

yen956

¦^´_ 6# 074063
Sub test()
    Dim I As Integer, J As Integer
    For I = 5 To 33
        Cells(4, I).Resize(3, 1).Select
        With Selection
            .Merge
            For J = 1 To 4
                .Borders(J).LineStyle = xlNone
            Next
        End With
    Next
End Sub

yen956

¦^´_ 8# 074063
Book2 ªº¿é¥X½d³ò»PBook1 ªº¿é¥X½d³ò¤£¦P,
­ì VAB ­n®M¥Î¨ì Book2 ¤W, ½Ð±N¬ÛÃö¦ì§}§ï¤@§ï,
(¤£½×¬O¤½¦¡©Î¬OVBA§¡¦p¦¹)
¥H 5#F §ÚªºVBA¬°¨Ò, ¥u­n±N
[E4] §ï¬° [H13], §Y¥i¥¿±`

¤S, ¿é¥X¥Øªº¦aªº®æ¦¡©y«O«ù¤@­P, ¤¤¶¡¤S´¡¤J®É¶¡µ¥®æ¦¡,
·|³y¦¨¾ã­Óªí®æ¨S¦³¼u©Ê, ¤£¯à¼W¥[©Î´î¤Ö¦U²Õ¦W³æªº½Õ¾ã.
ªí®æ¶V²³æ¶V¦n³B²z

yen956

Sorry,²×©ó¤F¸Ñ§Aªº»Ý¨D.
¬O¤£¬O³o­Ó·N«ä?¸Õ¸Õ¬Ý:

1. ' ¥»VBA½Ð©ñ¦bSheet(1), ¤£­n©ñ¦b Module1
   
2. ' ½Ð¥ý¤â°Ê½Õ¾ã§A©Ò»Ý­nªº®æ¦¡, ¦A°õ¦æ¥»VBA
   
3. ' ©m¦W©ñ¦b [J21:J23](½Ð¥ý½Õ¦n©m¦W®æ¦¡, ¥B©m¦W½ÐªÅ¥Õ)
   
4. ' ®É¶¡©ñ¦b [H21:I23](½Ð¥ý½Õ¦n®É¶¡®æ¦¡, ¨Ã¶ñ¤J®É¶¡)
   
5. ' ­Y±N©m¦W¡B®É¶¡®æ¦¡§ï§O³B, ¤U¦C¬ÛÃö[¦ì§}]½Ð­×§ï
   
6. Sub TESTx()
   
7.     Dim dV As Object, d0 As Object, dX As Object, E
   
8.     Set dV = CreateObject("Scripting.Dictionary")
   
9.     Set d0 = CreateObject("Scripting.Dictionary")
   
10.     Set dX = CreateObject("Scripting.Dictionary")
    
11.    
    
12.     '1. §¹¥þ²M°£¿é¥X°Ï(¥]§t¤º®e¡B®æ¦¡µ¥)
    
13.     [H13:BE15].Clear
    
14.    
    
15.     '2. ÄæBªº©m¦W¤ÀÃþ©ñ¤JDictionary¤¤
    
16.        For Each E In Range("B2", "B" & [B65536].End(xlUp).Row)
    
17.         If E.Offset(0, 1) = "" Then GoTo Next1:
    
18.         If E.Offset(0, 1) = "V" Then dV.Item(E) = "": GoTo Next1:
    
19.         If E.Offset(0, 1) = "O" Then d0.Item(E) = "": GoTo Next1:
    
20.         If E.Offset(0, 1) = "X" Then dX.Item(E) = ""
    
21. Next1:
    
22.     Next
    
23.    
    
24.     '3. ½Æ»s©m¦W®æ¦¡(­««Ø©m¦W®æ¦¡)
    
25.     [J21:J23].Copy [H13].Resize(1, dV.Count)
    
26.     [J21:J23].Copy [H13].Offset(0, dV.Count + 2).Resize(1, d0.Count)
    
27.     [J21:J23].Copy [H13].Offset(0, dV.Count + d0.Count + 4).Resize(1, dX.Count)
    
28.    
    
29.     '4. ¶}©l¿é¥X©m¦W
    
30.     [H13].Resize(1, 40) = ""
    
31.     [H13].Resize(1, dV.Count) = dV.Keys
    
32.     [H13].Offset(0, dV.Count + 2).Resize(1, d0.Count) = d0.Keys
    
33.     [H13].Offset(0, dV.Count + 2 + d0.Count + 2).Resize(1, dX.Count) = dX.Keys
    
34.    
    
35.     '5. ½Æ»s®É¶¡
    
36.     [H21:I23].Copy [H13].Offset(0, dV.Count)
    
37.     [H21:I23].Copy [H13].Offset(0, dV.Count + 2 + d0.Count)
    
38. End Sub

½Æ»s¥N½X

¤U¸ü (145.26 KB)

2016-1-8 08:48

yen956

¦^´_ 14# 074063
°²³]¦p¤U¹Ï:

¤U¸ü (61.15 KB)

2016-1-10 13:43

«h
    '5. ½Æ»s®É¶¡
    [H21:I23].Copy [H13].Offset(0, dV.Count)   '®É¶¡¤Î®æ¦¡1 ªº¦ì§}
    [H25:I27].Copy [H13].Offset(0, dV.Count + 2 + d0.Count)   '®É¶¡¤Î®æ¦¡2 ªº¦ì§}

yen956

1. ' ¥»VBA½Ð©ñ¦bSheet(1), ¤£­n©ñ¦b Module1
   
2. ' ¤U¦C¨â¦C ******** ¤§¶¡½Ð¥ý½Õ½Õ¦n, ¦A°õ¦æ¥»VBA
   
3. Sub TEST3()
   
4.     Dim I As Integer, J As Integer, Col As Integer
   
5.     Dim arST, arET, arKind
   
6.     ''***********************
   
7.     Dim ndx(10) As Integer, cnt(10) As Integer          '¦h¼g¤@ÂI³Æ¥Î, ¨S¥Î¨ì¤]¨SÃö«Y
   
8.     arKind = Array("X", "O", "V", "¡·", "¡¯")                  '¥i¼W´î, ¨S¥Î¨ì¤]¨SÃö«Y
   
9.     '²Å¸¹±Æ¦C¶¶§Ç, »P±N¨Óªº¿é¥X¶¶¦³Ãö
   
10.     arST = Array("17:20", "17:21", "17:22", "17:23")    '°_©l®É¶¡, ³Ì¦h¥u¯à¤ñ"V,O,X,¡·,¡¯"¤Ö1
    
11.     arET = Array("19:20", "19:21", "19:22", "19:23")    'µ²§ô®É¶¡, ³Ì¦h¥u¯à¤ñ"V,O,X,¡·,¡¯"¤Ö1
    
12.     ''***********************
    
13.     Col = 8      'H=8, ©m¦W¿é¥X¦ì¸m¦b [H13]
    
14.    
    
15.     '1. §¹¥þ²M°£¿é¥X°Ï(¥]§t¤º®e¡B®æ¦¡µ¥)
    
16.     [H12:IV15].Clear
    
17.    
    
18.     '2. ­««Ø®É¶¡
    
19.     For I = 0 To UBound(arKind) - 1
    
20.         cnt(I) = Application.CountIf(Range("C2", "C" & [C65536].End(xlUp).Row), arKind(I))
    
21.         If cnt(I) > 0 Then
    
22.             ndx(I) = Col
    
23.             Col = Col + cnt(I)
    
24.             If I <> UBound(arKind) - 2 Then
    
25.                 For J = 13 To 15
    
26.                     Cells(J, Col).Resize(1, 2).Merge   '®É¶¡®æ¦X¨Ö
    
27.                     Cells(J, Col).HorizontalAlignment = xlCenter
    
28.                 Next
    
29.                 Cells(13, Col) = arST(I)            '°_©l®É¶¡¦b²Ä13¦C
    
30.                 Cells(14, Col) = "~"                '"~" ¸¹¦b²Ä14¦C
    
31.                 Cells(14, Col).Orientation = -90    '¤å¦r¤è¦V¡÷¥kÂà90«×(¿ý¨Óªº)
    
32.                 Cells(15, Col) = arET(I)            'µ²§ô®É¶¡¦b²Ä15¦C
    
33.                 '¦p»Ý¨ä¥L®æ¦¡, ½Ð¦Û¦æ¿ý»s¦A¿ï¥Î¶K¤W(µL¶·¥þ³¡·Ó§Û)
    
34.             End If
    
35.             Col = Col + 2
    
36.         End If
    
37.     Next
    
38.    
    
39.     '3. ¶}©l¿é¥X©m¦W
    
40.     For Each E In Range("B2", "B" & [B65536].End(xlUp).Row)
    
41.         If E.Offset(0, 1) = "" Then GoTo Next1:
    
42.         For I = 0 To UBound(arKind) - 1
    
43.             If E.Offset(0, 1) = arKind(I) Then
    
44.                 Cells(12, ndx(I)) = arKind(I)    'Åã¥Ü¼Ð°O(¦]¦³¨Ç²Å¸¹§A¤£·Q¥Î, ¬G¥[µù¤~·|²M·¡), ¥iµù¸Ñ±¼
    
45.                 Cells(13, ndx(I)) = E
    
46.                 Cells(13, ndx(I)).Resize(3, 1).Merge  '©m¦W®æ¦X¨Ö
    
47.                 Cells(13, ndx(I)).Orientation = xlVertical   '¤å¦r¤è¦V¡÷««ª½±Æ¦C
    
48.                 ndx(I) = ndx(I) + 1
    
49.                 GoTo Next1:
    
50.             End If
    
51.         Next
    
52. Next1:
    
53.     Next
    
54. End Sub

½Æ»s¥N½X

¦^´_ 17# 074063