Sub addgrp展開_sheet別2()
Dim Map As Scripting.Dictionary
Set Map = New Scripting.Dictionary
' Dim info(10, 10) As String
Dim name As String
Dim outarr(100, 2) As String
出力i = 0
'-------------------------------------------------------
row_policy_start = 4
row_policy_end = 9
row_address_start = 4
row_address_end = 13
row_addrgrp_start = 3
row_addrgrp_end = 4
colum_policy = 4
colum_addgrp = 1
colum_addgrp_member = 2
colum_addr_vlookup_start = 2
colum_addr_vlookup_end = 3
'colum_addr_ipaddは範囲の中の何番目かを示す
colum_addr_ipadd = 2
'--------------------------------------------------------
Dim ws_pol As Worksheet
Dim ws_add As Worksheet
Dim ws_adg As Worksheet
Set ws_pol = Sheets("policy")
Set ws_add = Sheets("Address")
Set ws_adg = Sheets("addrgrp")
For row_policy = row_policy_start To row_policy_end
pol_fullstr = ws_pol.Cells(row_policy, colum_policy).Value
pol_arr = Split(pol_fullstr, vbLf)
num_pol_arr = UBound(pol_arr, 1)
'1セル内の項目数
For pol_i = 0 To num_pol_arr
'GRP有無検索
For grp_i = row_addrgrp_start To row_addrgrp_end
If pol_arr(pol_i) = ws_adg.Cells(grp_i, colum_addgrp).Value Then
outarr(出力i, 0) = pol_arr(pol_i)
f_higrp = 1
member_fullstr = ws_adg.Cells(grp_i, colum_addgrp_member).Value
member_arr = Split(member_fullstr, vbLf)
num_member_arr = UBound(member_arr, 1)
'MsgBox num_member_arr
For member_i = 0 To num_member_arr
On Error Resume Next
outarr(出力i, 0) = pol_arr(pol_i)
outarr(出力i, 1) = member_arr(member_i)
outarr(出力i, 2) = Application.WorksheetFunction.VLookup(member_arr(member_i), Range(ws_add.Cells(row_address_start, colum_addr_vlookup_start), ws_add.Cells(row_address_end, colum_addr_vlookup_end)), colum_addr_ipadd, False)
出力i = 出力i + 1
Next member_i
End If
Next grp_i
If f_higrp = 0 Then
'add検索
On Error Resume Next
addrip = WorksheetFunction.VLookup(pol_arr(pol_i), Range(ws_add.Cells(row_address_start, colum_addr_vlookup_start), ws_add.Cells(row_address_end, colum_addr_vlookup_end)), colum_addr_ipadd, False)
outarr(出力i, 0) = pol_arr(pol_i)
outarr(出力i, 1) = pol_arr(pol_i)
outarr(出力i, 2) = addrip
' MsgBox outarr(出力i, 1)
出力i = 出力i + 1
End If
f_higrp = 0
Next pol_i
outarr_conc_1 = ""
For k = 0 To UBound(outarr, 1)
outarr_conc_1 = outarr_conc_1 & vbCrLf & outarr(k, 0)
outarr_conc_2 = outarr_conc_2 & vbCrLf & outarr(k, 1)
outarr_conc_3 = outarr_conc_3 & vbCrLf & outarr(k, 2)
Next k
ws_pol.Cells(row_policy, 21).Value = outarr_conc_1
ws_pol.Cells(row_policy, 22).Value = outarr_conc_2
ws_pol.Cells(row_policy, 23).Value = outarr_conc_3
outarr_conc_1 = ""
outarr_conc_2 = ""
outarr_conc_3 = ""
出力i = 0
For k = 0 To UBound(outarr, 1)
outarr(k, 0) = ""
outarr(k, 1) = ""
outarr(k, 2) = ""
Next k
Next row_policy
End Sub