yen956

¥i¥H¥ÎVBA¶Ü?
¸Õ¸Õ¬Ý:

1. Public arW, arL
   
2. '¨ú±o寛«×»Pªø«×¬É­­ªº°}¦C, ¨ÑMatch¥Î
   
3. Sub init()
   
4.     ReDim arW(3) As Integer
   
5.     ReDim arL(3, 3) As Integer
   
6.     Dim W1 As Integer, L1 As Integer
   
7.     arW(0) = Split(Cells(3, 3), "~")(1)    '寛«×ªº¤W­­
   
8.     For W1 = 0 To 2
   
9.         arW(W1 + 1) = Split(Cells(W1 * 3 + 3, 3), "~")(0) '寛«×«ö­°¾­±Æ
   
10.         For L1 = 0 To 2
    
11.             arL(W1, L1) = Split(Cells(W1 * 3 + L1 + 3, 5), "~")(0)   'ªø«×«ö¤É¾­±Æ
    
12.         Next
    
13.         arL(W1, L1) = Split(Cells(W1 * 3 + L1 + 2, 5), "~")(1)     'ªø«×ªº¤W­­
    
14.     Next
    
15. End Sub
    
16. 
    
17. Private Sub CommandButton1_Click()
    
18.     Dim I As Integer, J As Integer, arL2(3) As Integer
    
19.     Dim MHW, MHL, IDW As String, IDL As String
    
20.     init
    
21.     For I = 4 To [G4].End(xlDown).Row
    
22.         MHW = Application.Match(Cells(I, 8), arW, -1)
    
23.         IDW = Application.Index([B1:B11], MHW * 3, 1)
    
24.         For J = 0 To 3
    
25.             arL2(J) = arL(MHW - 1, J)    '±N2ºû°}¦CÂର1ºû°}¦C
    
26.         Next
    
27.         MHL = Application.Match(Cells(I, 9), arL2, 1)
    
28.         'ªø«×¥N¸¹¤À[D3:D5,D6:D9,D10:D12]¤T°Ï
    
29.         IDL = Application.Index([D3:D5,D6:D8,D9:D11], MHL, 1, MHW)
    
30.         Cells(I, 10) = IDW & "_" & IDL
    
31.     Next
    
32. End Sub

½Æ»s¥N½X

¤U¸ü (27.51 KB)

2015-12-6 13:03