{"id":403,"date":"2012-04-14T05:57:23","date_gmt":"2012-04-13T20:57:23","guid":{"rendered":"http:\/\/fujiitoshiki.com\/improvesociety\/?p=403"},"modified":"2017-04-27T11:49:46","modified_gmt":"2017-04-27T02:49:46","slug":"post-403","status":"publish","type":"post","link":"https:\/\/www.fujiitoshiki.com\/improvesociety\/?p=403","title":{"rendered":"\u65e5\u672c\u98df\u54c1\u6a19\u6e96\u6210\u5206\u88682010\u306e\u98df\u54c1\u756a\u53f7\u3092\u30ab\u30c6\u30b4\u30ea\u30fc\u5206\u985e\u3059\u308b\u3000\u305d\u306e1"},"content":{"rendered":"

\u65e5\u672c\u98df\u54c1\u6a19\u6e96\u6210\u5206\u88682010\u306e\u98df\u54c1\u756a\u53f7\u3092\u5206\u985e\u3059\u308b<\/a>\u306e\u8a18\u4e8b\u3067\u98df\u54c1\u756a\u53f7\u3092\u5206\u985e\u3059\u308b\u8a18\u4e8b\u3092\u63b2\u8f09\u3057\u307e\u3057\u305f\u304c\uff0c\u4e0d\u5341\u5206\u306a\u5206\u985e\u3057\u304b\u51fa\u6765\u3066\u304a\u308a\u307e\u305b\u3093\u3067\u3057\u305f\uff0e\u4eca\u56de\u306f\u65e2\u306b\u65e5\u672c\u8a9e\u306e\u5b8c\u6210\u3057\u305f\u5206\u985e\u3092\u898b\u3064\u3051\u307e\u3057\u305f\u306e\u3067\uff0c\u305d\u308c\u3092\u5143\u306b\u82f1\u8a9e\u3082\u3064\u3051\u3066\u5206\u985e\u3057\u307e\u3057\u305f\uff0e\u53c2\u8003\u306b\u3057\u305f\u306e\u306f\u4ee5\u4e0b\u306e\u30d5\u30a1\u30a4\u30eb\u3067\u3059\uff0e<\/p>\n

\u8cc7\u6e90\u8abf\u67fb\u5206\u79d1\u4f1a\u5831\u544a\u300c\u65e5\u672c\u98df\u54c1\u6a19\u6e96\u6210\u5206\u88682010\u300d\u306b\u3064\u3044\u3066<\/a><\/p>\n

\u65b0\u3057\u3044\u30d6\u30c3\u30af\u3092\u7528\u610f\u3057\u307e\u3059\uff0e”1299012_1.pdf”\u304b\u3089”1299012_18.pdf”\u307e\u3067\u306ePDF\u306e\u5168\u30c6\u30ad\u30b9\u30c8\u3092Sheet1\u306b\u30aa\u30d7\u30b7\u30e7\u30f3\u3067\u30da\u30fc\u30b9\u30c8\u3057\u307e\u3059\uff0e\u305d\u306e\u969b\uff0c\u4e0b\u306e\u884c\u65b9\u5411\u306b\u884c\u306e\u9593\u9694\u3092\u7a7a\u3051\u305a\u306b\u8cbc\u308a\u4ed8\u3051\u307e\u3059\uff0e\u30c6\u30ad\u30b9\u30c8\u30d5\u30a1\u30a4\u30eb\u30a6\u30a3\u30b6\u30fc\u30c9\u3067\u6700\u521d\u306e\u30ab\u30e9\u30e0\u306e\u30c7\u30fc\u30bf\u5f62\u5f0f\u3092\u300e\u6587\u5b57\u5217\u300f\u306b\u5909\u66f4\u3057\u307e\u3059\uff0e\u539f\u6750\u6599\u7684\u98df\u54c1\u306e\u3082\u3068\u3068\u306a\u308b\u751f\u7269\u306e\u5b66\u540d<\/a>\u3067\u30c0\u30a6\u30f3\u30ed\u30fc\u30c9\u3057\u305fPDF\u306e\u5168\u30c6\u30ad\u30b9\u30c8\u3092\u9078\u629e\u3057\uff0cSheet2\u306b\u30aa\u30d7\u30b7\u30e7\u30f3\u3067\u30da\u30fc\u30b9\u30c8\u3057\u307e\u3059\uff0e\u30c6\u30ad\u30b9\u30c8\u30d5\u30a1\u30a4\u30eb\u30a6\u30a3\u30b6\u30fc\u30c9\u304c\u958b\u304f\u306e\u3067\uff0c1\/3\u3067\u306f\u5143\u306e\u30c7\u30fc\u30bf\u5f62\u5f0f\u3067\u300e\u30ab\u30f3\u30de\u3084\u30bf\u30d6\u306a\u3069\u306e\u533a\u5207\u308a\u6587\u5b57\u306b\u3088\u3063\u3066\u30d5\u30a3\u30fc\u30eb\u30c9\u3054\u3068\u306b\u533a\u5207\u3089\u308c\u305f\u30c7\u30fc\u30bf\u300f\u3092\u9078\u629e\u3057\u307e\u3059\uff0e\u30c6\u30ad\u30b9\u30c8\u30d5\u30a1\u30a4\u30eb\u30a6\u30a3\u30b6\u30fc\u30c92\/3\u3067\u306f\u300e\u9023\u7d9a\u3057\u305f\u533a\u5207\u308a\u6587\u5b57\u306f1\u6587\u5b57\u3068\u307f\u306a\u3059\u300f\u306e\u30c1\u30a7\u30c3\u30af\u3092\u5916\u3057\u3066\u6b21\u306b\u9032\u307f\u307e\u3059\uff0e\u30c6\u30ad\u30b9\u30c8\u30d5\u30a1\u30a4\u30eb\u30a6\u30a3\u30b6\u30fc\u30c93\/3\u3067\u306f\u6700\u521d\u306e\u5217\u306e\u30c7\u30fc\u30bf\u5f62\u5f0f\u3092\u300e\u6587\u5b57\u5217\u300f\u306b\u5909\u66f4\u3057\u3066\u5b8c\u4e86\u3092\u30af\u30ea\u30c3\u30af\u3057\u307e\u3059\uff0e\u3053\u306eEXCEL\u30d6\u30c3\u30af\u306b”Sample.xlsm”\u3068\u540d\u524d\u3092\u4ed8\u3051\u3066\u4fdd\u5b58\u3057\u307e\u3059\uff0e<\/p>\n

“Sample.xlsm”\u30d6\u30c3\u30af\u3092\u958b\u304d\uff0cAlt+F11\u30ad\u30fc\u3092\u62bc\u4e0b\u3057\u3066VBE\u3092\u8d77\u52d5\u3057\u307e\u3059\uff0e\u633f\u5165\u30e1\u30cb\u30e5\u30fc\u304b\u3089\u6a19\u6e96\u30e2\u30b8\u30e5\u30fc\u30eb\u3092\u9078\u629e\u3057\uff0c\u4e0b\u8a18\u306e\u30b3\u30fc\u30c9\u3092\u8cbc\u308a\u4ed8\u3051\u307e\u3059\uff0eSeparate_by_Parent\u30d7\u30ed\u30b7\u30fc\u30b8\u30e3\u3092\u5b9f\u884c\u3059\u308b\u3068”Result”\u3068\u3044\u3046\u540d\u524d\u306e\u30b7\u30fc\u30c8\u304c\u51fa\u6765\u307e\u3059\uff0e<\/p>\n

\r\nOption Explicit\r\nFunction MajorCategoryAr(ByRef Sh As Worksheet) As String()\r\nDim mySht               As Worksheet\r\nDim myRng               As Range\r\nDim tmpAr               As Variant\r\nDim StartEnd            As Variant\r\nDim strFoodGroup        As String\r\nDim strFoodGroupJP      As String\r\nDim strFoodGroupEN      As String\r\nDim strSubFoodGroup     As String\r\nDim strSubFoodGroupJP   As String\r\nDim strSubFoodGroupEN   As String\r\nDim strSub_Category     As String\r\nDim strSub_CategoryJP   As String\r\nDim strSub_CategoryEN   As String\r\nDim strMajor_Category   As String\r\nDim StartNumber()       As String\r\nDim Exit_Number()       As String\r\nDim FoodGroupJP()       As String\r\nDim FoodGroupEN()       As String\r\nDim Sub_FoodGroup_JP()  As String\r\nDim Sub_FoodGroup_EN()  As String\r\nDim Sub_Category_JPN()  As String\r\nDim Sub_Category_ENG()  As String\r\nDim Major_CategoryJP()  As String\r\nDim Major_CategoryEN()  As String\r\nDim Major_CategoryLT()  As String\r\nDim myArray()           As String\r\nDim i As Long\r\nDim j As Long\r\nDim k As Long\r\nDim n As Long\r\nDim RegExp_3_Digit_Num  As Object\r\nDim RegExp_Item_Number  As Object\r\nDim RegExp_SentakuHanni As Object\r\nDim RegExp_SubCategory1 As Object\r\nDim RegExp_SubCategory2 As Object\r\nDim RegExp_MedCategory  As Object\r\nDim RegExp_Foods_Group  As Object\r\nDim RegExp_Jpn_Eng_Mix  As Object\r\nDim RegExp_JapaneseOnly As Object\r\nDim RegExp_Upper_Lower  As Object\r\nDim RegExp_Upper_Only   As Object\r\nDim RegExp_Lower_Only   As Object\r\nDim RegExp_RoundBracket As Object\r\nDim RegExp_SquareBracket    As Object\r\nDim RegExp_AngleBracket As Object\r\nDim myMatches           As Object\r\nDim myMatch             As Object\r\nConst Ptn_3_Digit_Num   As String = \"[0-9]{3}$\"\r\nConst Ptn_Item_Number   As String = \"^[0-9]{5}$\"\r\nConst Ptn_SentakuHanni  As String = \"(\uff0c|\uff5e)\"\r\nConst Ptn_SubCategory1  As String = \"^(\uff08|\\().(\uff09|\\))$\"\r\nConst Ptn_SubCategory2  As String = \"^(\uff1c|\uff1e)$\"\r\nConst Ptn_MedCategory   As String = \"^\\[.\\]$\"\r\nConst Ptn_FoodGroupNum  As String = \"^([\uff10-\uff19]|[0-9]{2})$\"\r\nConst Ptn_Jpn_Eng_Mix   As String = \"^[^A-Za-z0-9]+(\\([^A-Za-z0-9]+\\))?([A-Za-z])\"\r\nConst Ptn_JapaneseOnly  As String = \"^[^A-Za-z0-9]+(\\([^A-Za-z0-9]+\\))?$\"\r\nConst Ptn_Upper_Lower   As String = \"[A-Z][a-z]+\"\r\nConst Ptn_Upper_Only    As String = \"[A-Z]+\"\r\nConst Ptn_Lower_Only    As String = \"^[a-z]+$\"\r\nConst Ptn_RoundStart    As String = \"^[\\(\uff08]\"\r\nConst Ptn_Round_Exit    As String = \"[\\(\uff08][^A-Za-z0-9]+[\\)\uff09]\"\r\nConst Ptn_SquareStart   As String = \"^\\[\"\r\nConst Ptn_Square_Exit   As String = \"\\[[^A-Za-z0-9]+\\]\"\r\nConst Ptn_AngleStart    As String = \"^[\\<\uff1c]\"\r\nConst Ptn_Angle_Exit    As String = \"[\\<\uff1c][^A-Za-z0-9]+[\\>\uff1e]\"\r\nSet mySht = Sh\r\nSet myRng = mySht.UsedRange\r\ntmpAr = myRng\r\nSet RegExp_3_Digit_Num = CreateObject(\"VBScript.RegExp\")\r\nSet RegExp_Item_Number = CreateObject(\"VBScript.RegExp\")\r\nSet RegExp_SentakuHanni = CreateObject(\"VBScript.RegExp\")\r\nSet RegExp_SubCategory1 = CreateObject(\"VBScript.RegExp\")\r\nSet RegExp_SubCategory2 = CreateObject(\"VBScript.RegExp\")\r\nSet RegExp_MedCategory = CreateObject(\"VBScript.RegExp\")\r\nSet RegExp_Foods_Group = CreateObject(\"VBScript.RegExp\")\r\nSet RegExp_Jpn_Eng_Mix = CreateObject(\"VBScript.RegExp\")\r\nSet RegExp_JapaneseOnly = CreateObject(\"VBScript.RegExp\")\r\nSet RegExp_Upper_Lower = CreateObject(\"VBScript.RegExp\")\r\nSet RegExp_Upper_Only = CreateObject(\"VBScript.RegExp\")\r\nSet RegExp_Lower_Only = CreateObject(\"VBScript.RegExp\")\r\nSet RegExp_RoundBracket = CreateObject(\"VBScript.RegExp\")\r\nSet RegExp_SquareBracket = CreateObject(\"VBScript.RegExp\")\r\nSet RegExp_AngleBracket = CreateObject(\"VBScript.RegExp\")\r\nWith RegExp_3_Digit_Num\r\n    .Pattern = \"[0-9]{3}$\"\r\n    .IgnoreCase = True\r\n    .Global = True\r\nEnd With\r\nWith RegExp_Item_Number\r\n    .Pattern = \"^[0-9]{5}$\"\r\n    .IgnoreCase = True\r\n    .Global = True\r\nEnd With\r\nWith RegExp_SentakuHanni\r\n    .Pattern = \"(\uff0c|\uff5e)\"\r\n    .IgnoreCase = True\r\n    .Global = True\r\nEnd With\r\nWith RegExp_SubCategory1\r\n    .Pattern = \"^(\uff08|\\().(\uff09|\\))$\"\r\n    .IgnoreCase = True\r\n    .Global = True\r\nEnd With\r\nWith RegExp_SubCategory2\r\n    .Pattern = \"^(\uff1c|\uff1e)$\"\r\n    .IgnoreCase = True\r\n    .Global = True\r\nEnd With\r\nWith RegExp_MedCategory\r\n    .Pattern = \"^\\[.\\]$\"\r\n    .IgnoreCase = True\r\n    .Global = True\r\nEnd With\r\nWith RegExp_Foods_Group\r\n    .Pattern = \"^([\uff10-\uff19]|[0-9]{2})$\"\r\n    .IgnoreCase = True\r\n    .Global = True\r\nEnd With\r\nWith RegExp_Jpn_Eng_Mix\r\n    .Pattern = \"^[^A-Za-z0-9]+(\\([^A-Za-z0-9]+\\))?([A-Za-z])\"\r\n    .IgnoreCase = True\r\n    .Global = True\r\nEnd With\r\nWith RegExp_JapaneseOnly\r\n    .Pattern = \"^[^A-Za-z0-9]+(\\([^A-Za-z0-9]+\\))?$\"\r\n    .IgnoreCase = True\r\n    .Global = True\r\nEnd With\r\nWith RegExp_Upper_Lower\r\n    .Pattern = \"[A-Z][a-z]+\"\r\n    .IgnoreCase = False\r\n    .Global = True\r\nEnd With\r\nWith RegExp_Upper_Only\r\n    .Pattern = \"[A-Z]+\"\r\n    .IgnoreCase = False\r\n    .Global = True\r\nEnd With\r\nWith RegExp_Lower_Only\r\n    .Pattern = \"^[a-z]+$\"\r\n    .IgnoreCase = False\r\n    .Global = True\r\nEnd With\r\nj = 0\r\nFor i = LBound(tmpAr) + 1 To UBound(tmpAr)\r\n    With RegExp_RoundBracket\r\n        .Pattern = Ptn_RoundStart\r\n        .IgnoreCase = True\r\n        .Global = True\r\n    End With\r\n    With RegExp_SquareBracket\r\n        .Pattern = Ptn_SquareStart\r\n        .IgnoreCase = True\r\n        .Global = True\r\n    End With\r\n    With RegExp_AngleBracket\r\n        .Pattern = Ptn_AngleStart\r\n        .IgnoreCase = True\r\n        .Global = True\r\n    End With\r\n    strFoodGroup = \"\"\r\n    strSubFoodGroup = \"\"\r\n    strSub_Category = \"\"\r\n    strMajor_Category = \"\"\r\n    ReDim Preserve StartNumber(j)\r\n    ReDim Preserve Exit_Number(j)\r\n    ReDim Preserve FoodGroupJP(j)\r\n    ReDim Preserve FoodGroupEN(j)\r\n    ReDim Preserve Sub_FoodGroup_JP(j)\r\n    ReDim Preserve Sub_FoodGroup_EN(j)\r\n    ReDim Preserve Sub_Category_JPN(j)\r\n    ReDim Preserve Sub_Category_ENG(j)\r\n    ReDim Preserve Major_CategoryJP(j)\r\n    ReDim Preserve Major_CategoryEN(j)\r\n    ReDim Preserve Major_CategoryLT(j)\r\n    If RegExp_3_Digit_Num.Test(tmpAr(i, 1)) Then\r\n        Select Case True\r\n        Case RegExp_Item_Number.Test(tmpAr(i, 1))\r\n            StartNumber(j) = tmpAr(i, 1)\r\n            Exit_Number(j) = tmpAr(i, 1)\r\n        Case RegExp_SentakuHanni.Test(tmpAr(i, 1))\r\n            StartEnd = StartExit(tmpAr(i, 1))\r\n            StartNumber(j) = StartEnd(0)\r\n            Exit_Number(j) = StartEnd(1)\r\n            Erase StartEnd\r\n        End Select\r\n        FoodGroupJP(j) = strFoodGroupJP\r\n        FoodGroupEN(j) = strFoodGroupEN\r\n        If (i >= 19 And i <= 27) _\r\n        Or (i >= 370 And i <= 596) _\r\n        Or (i >= 599 And i <= 626) _\r\n        Or (i >= 635 And i <= 639) _\r\n        Or (i >= 646 And i <= 668) _\r\n        Then\r\n            Sub_FoodGroup_JP(j) = strSubFoodGroupJP\r\n            Sub_FoodGroup_EN(j) = strSubFoodGroupEN\r\n        End If\r\n        If tmpAr(i, 2) = \"\" Then\r\n            Sub_Category_JPN(j) = strSub_CategoryJP\r\n            Sub_Category_ENG(j) = strSub_CategoryEN\r\n        End If\r\n        For k = 2 To 8\r\n            strMajor_Category = strMajor_Category & \" \" & tmpAr(i, k)\r\n        Next k\r\n        strMajor_Category = Trim(strMajor_Category)\r\n        On Error Resume Next\r\n        For k = 1 To 8\r\n            If RegExp_Lower_Only.Test(tmpAr(i + 1, 1)) _\r\n            And Not RegExp_SubCategory1.Test(tmpAr(i + 1, 1)) _\r\n            And Not RegExp_SubCategory2.Test(tmpAr(i + 1, 1)) _\r\n            And Not RegExp_Foods_Group.Test(tmpAr(i + 1, 1)) _\r\n            And Not RegExp_3_Digit_Num.Test(tmpAr(i + 1, 1)) _\r\n            Then\r\n                strMajor_Category = strMajor_Category & \" \" & tmpAr(i + 1, k)\r\n            End If\r\n        Next k\r\n        On Error GoTo 0\r\n        strMajor_Category = Trim(strMajor_Category)\r\n        If RegExp_Jpn_Eng_Mix.Test(strMajor_Category) Then\r\n            StartEnd = Separate_Jpn_Eng(strMajor_Category)\r\n            Major_CategoryJP(j) = StartEnd(0)\r\n            Erase StartEnd\r\n            Set myMatches = RegExp_Upper_Lower.Execute(strMajor_Category)\r\n            Major_CategoryEN(j) = Mid(strMajor_Category, _\r\n                                    myMatches.Item(0).firstindex + 1, _\r\n                                    myMatches.Item(myMatches.Count - 1).firstindex _\r\n                                  - myMatches.Item(0).firstindex - 1)\r\n            Set myMatch = myMatches.Item(myMatches.Count - 1)\r\n            Major_CategoryLT(j) = Mid(strMajor_Category, myMatch.firstindex + 1)\r\n        Else\r\n        End If\r\n    Else\r\n        Select Case True\r\n        Case RegExp_Foods_Group.Test(tmpAr(i, 1))\r\n            For k = 2 To 8\r\n                strFoodGroup = strFoodGroup & \" \" & tmpAr(i, k)\r\n            Next k\r\n            strFoodGroup = Trim(strFoodGroup)\r\n            Select Case True\r\n            Case RegExp_Jpn_Eng_Mix.Test(strFoodGroup)\r\n                Set myMatches = RegExp_Jpn_Eng_Mix.Execute(strFoodGroup)\r\n                Set myMatch = myMatches.Item(0)\r\n                strFoodGroupJP = Left(strFoodGroup, myMatches.Item(0).Length - 1)\r\n                strFoodGroupEN = Mid(strFoodGroup, myMatches.Item(0).Length)\r\n            Case RegExp_JapaneseOnly.Test(strFoodGroup)\r\n                Set myMatches = RegExp_JapaneseOnly.Execute(strFoodGroup)\r\n                Set myMatch = myMatches.Item(0)\r\n                strFoodGroupJP = Left(strFoodGroup, myMatches.Item(0).Length - 1)\r\n                strFoodGroupEN = Mid(strFoodGroup, myMatches.Item(0).Length)\r\n            Case Else\r\n            End Select\r\n        Case RegExp_AngleBracket.Test(tmpAr(i, 1))\r\n            For k = 1 To 8\r\n                strSubFoodGroup = strSubFoodGroup & \" \" & tmpAr(i, k)\r\n            Next k\r\n            strSubFoodGroup = Trim(strSubFoodGroup)\r\n            With RegExp_AngleBracket\r\n                .Pattern = Ptn_Angle_Exit\r\n                .IgnoreCase = True\r\n                .Global = True\r\n            End With\r\n            Set myMatches = RegExp_AngleBracket.Execute(strSubFoodGroup)\r\n            strSubFoodGroupJP = myMatches.Item(0).Value\r\n            strSubFoodGroupEN = Mid(strSubFoodGroup, myMatches.Item(0).Length + 2)\r\n            strSubFoodGroupEN = Replace(strSubFoodGroupEN, \"\uff1c\", \"<\")\r\n            strSubFoodGroupEN = Replace(strSubFoodGroupEN, \"\uff1e\", \">\")\r\n        Case RegExp_RoundBracket.Test(tmpAr(i, 1))\r\n            For k = 1 To 8\r\n                strSub_Category = strSub_Category & \" \" & tmpAr(i, k)\r\n            Next k\r\n            strSub_Category = Trim(strSub_Category)\r\n            With RegExp_RoundBracket\r\n                .Pattern = Ptn_Round_Exit\r\n                .IgnoreCase = True\r\n                .Global = True\r\n            End With\r\n            Set myMatches = RegExp_RoundBracket.Execute(strSub_Category)\r\n            On Error Resume Next\r\n            strSub_CategoryJP = myMatches.Item(0).Value\r\n            strSub_CategoryJP = Replace(strSub_CategoryJP, \"(\", \"\uff08\")\r\n            strSub_CategoryJP = Replace(strSub_CategoryJP, \")\", \"\uff09\")\r\n            strSub_CategoryEN = Mid(strSub_Category, myMatches.Item(0).Length + 2)\r\n            strSub_CategoryEN = Replace(strSub_CategoryEN, \"\uff08\", \"(\")\r\n            strSub_CategoryEN = Replace(strSub_CategoryEN, \"\uff09\", \")\")\r\n            On Error GoTo 0\r\n        Case Else\r\n        End Select\r\n        j = j - 1\r\n    End If\r\n    j = j + 1\r\nNext i\r\nReDim myArray(UBound(StartNumber), 10)\r\nFor n = LBound(myArray) To UBound(myArray)\r\n    myArray(n, 0) = StartNumber(n)\r\n    myArray(n, 1) = Exit_Number(n)\r\n    myArray(n, 2) = FoodGroupJP(n)\r\n    myArray(n, 3) = FoodGroupEN(n)\r\n    myArray(n, 4) = Sub_FoodGroup_JP(n)\r\n    myArray(n, 5) = Sub_FoodGroup_EN(n)\r\n    myArray(n, 6) = Sub_Category_JPN(n)\r\n    myArray(n, 7) = Sub_Category_ENG(n)\r\n    myArray(n, 8) = Major_CategoryJP(n)\r\n    myArray(n, 9) = Major_CategoryEN(n)\r\n    myArray(n, 10) = Major_CategoryLT(n)\r\nNext n\r\nMajorCategoryAr = myArray\r\nEnd Function\r\n\r\nFunction StartExit(ByVal InputStr As String) As String()\r\n    Dim str     As String\r\n    Dim Ar()    As String\r\n    str = InputStr\r\n    ReDim Ar(1)\r\n    Ar(0) = Left(str, 5)\r\n    Ar(1) = Left(str, 2) & Right(str, 3)\r\n    StartExit = Ar\r\nEnd Function\r\n\r\nFunction Separate_Jpn_Eng(ByVal InputStr As String) As String()\r\n    Dim str                 As String\r\n    Dim Ar()                As String\r\n    Dim RegExp_Jpn_Eng_Mix  As Object\r\n    Dim myMatches           As Object\r\n    Dim myMatch             As Object\r\n    Const Ptn_Jpn_Eng_Mix   As String = \"^[^A-Za-z0-9]+(\\([^A-Za-z0-9]+\\))?([A-Za-z])\"\r\n    str = InputStr\r\n    ReDim Ar(1)\r\n    Set RegExp_Jpn_Eng_Mix = CreateObject(\"VBScript.RegExp\")\r\n    With RegExp_Jpn_Eng_Mix\r\n        .Pattern = Ptn_Jpn_Eng_Mix\r\n        .IgnoreCase = True\r\n        .Global = True\r\n    End With\r\n    Set myMatches = RegExp_Jpn_Eng_Mix.Execute(str)\r\n    For Each myMatch In myMatches\r\n        If myMatches.Count > 0 Then\r\n            Ar(0) = Left(str, myMatches.Item(0).Length - 1)\r\n            Ar(1) = Mid(str, myMatches.Item(0).Length)\r\n        End If\r\n    Next myMatch\r\n    Separate_Jpn_Eng = Ar\r\nEnd Function\r\n\r\nSub Separate_by_Parent()\r\nDim mySht1              As Worksheet\r\nDim mySht2              As Worksheet\r\nDim mySht3              As Worksheet\r\nDim myRng               As Range\r\nDim tmpAr               As Variant\r\nDim Major_CategoryAr    As Variant\r\nDim No_Cancel_Ar        As Variant\r\nDim ItemNamAr()         As String\r\nDim ItemNumAr()         As String\r\nDim JapaneseName()      As String\r\nDim English_Name()      As String\r\nDim ItemArray()         As String\r\nDim Residual_JPN()      As String\r\nDim Residual_ENG()      As String\r\nDim Residual_Row()      As String\r\nDim i                   As Long\r\nDim j                   As Long\r\nDim k                   As Long\r\nDim m                   As Long\r\nDim n                   As Long\r\nDim p                   As Long\r\nDim q                   As Long\r\nDim r                   As Long\r\nDim s                   As Long\r\nDim t                   As Long\r\nDim str_JPN_Analyse     As String\r\nDim str_ENG_Analyse     As String\r\nDim strFoodGroup        As String\r\nDim strFoodGroupJP      As String\r\nDim strFoodGroupEN      As String\r\nDim strSubFoodGroup     As String\r\nDim strSubFoodGroupJP   As String\r\nDim strSubFoodGroupEN   As String\r\nDim strSub_Category     As String\r\nDim strSub_CategoryJP   As String\r\nDim strSub_CategoryEN   As String\r\nDim strMajor_Category   As String\r\nDim strMajor_CategoryJP As String\r\nDim strMajor_CategoryEN As String\r\nDim strMediumCategory   As String\r\nDim strMediumCategoryJP As String\r\nDim strMediumCategoryEN As String\r\nDim strMinor_Category   As String\r\nDim strMinor_CategoryJP As String\r\nDim strMinor_CategoryEN As String\r\nDim strDetailCategory   As String\r\nDim strDetailCategoryJP As String\r\nDim strDetailCategoryEN As String\r\nDim FoodGrouNum()       As String\r\nDim FoodGroupJP()       As String\r\nDim FoodGroupEN()       As String\r\nDim Sub_FoodGroup_JP()  As String\r\nDim Sub_FoodGroup_EN()  As String\r\nDim Sub_Group_JP_Row()  As String\r\nDim Sub_Group_EN_Row()  As String\r\nDim Sub_Category_JPN()  As String\r\nDim Sub_Category_ENG()  As String\r\nDim SubCategory_RowJ()  As String\r\nDim SubCategory_RowE()  As String\r\nDim Major_CategoryJP()  As String\r\nDim Major_CategoryEN()  As String\r\nDim Major_CategoryLT()  As String\r\nDim Major_JPN_RowNum()  As String\r\nDim Major_ENG_RowNum()  As String\r\nDim Major_Temp_Array()  As String\r\nDim MediumCategoryJP()  As String\r\nDim MediumCategoryEN()  As String\r\nDim Med_JP_RowNumber()  As Long\r\nDim Med_EN_RowNumber()  As Long\r\nDim Med_Category_JPN()  As String\r\nDim Med_Category_ENG()  As String\r\nDim MediumCategoryAr()  As String\r\nDim Minor_CategoryJP()  As String\r\nDim Minor_CategoryEN()  As String\r\nDim Min_JP_RowNumber()  As Long\r\nDim Min_EN_RowNumber()  As Long\r\nDim Min_Category_JPN()  As String\r\nDim Min_Category_ENG()  As String\r\nDim Minor_CategoryAr()  As String\r\nDim DetailCategoryJP()  As String\r\nDim DetailCategoryEN()  As String\r\nConst Ptn_FoodGroupNum  As String = \"^([\uff10-\uff19]|[0-9]{2})$\"\r\nConst Ptn_Jpn_Eng_Mix   As String = \"^[^A-Za-z0-9]+(\\([^A-Za-z0-9]+\\))?([A-Za-z])\"\r\nConst Ptn_JapaneseOnly  As String = \"^[^A-Za-z0-9\\*]+(\\([^A-Za-z0-9]+\\))?$\"\r\nConst Ptn_Upper_Lower   As String = \"[A-Za-z\\s:\\-,]+\" '\"[A-Za-z,\\s]+\"\r\nConst Ptn_Upper_Only    As String = \"[A-Z]+\"\r\nConst Ptn_Lower_Only    As String = \"^[a-z]+$\"\r\nConst Ptn_AngleStart    As String = \"^[\\<\uff1c]\"\r\nConst Ptn_Angle_JPN     As String = \"[<\uff1c].+[>\uff1e]\"\r\nConst Ptn_Angle_ENG     As String = \"[<\uff1c].+[>\uff1e]\"\r\nConst Ptn_RoundStart    As String = \"^[\\(\uff08][^0-9]+\"\r\nConst Ptn_Round_JPN     As String = \"[\\(\uff08][^A-Za-z0-9]+[\\)\uff09]\"\r\nConst Ptn_Round_ENG     As String = \"[\\(\uff08][A-Za-z\\s]+[\\)\uff09]\"\r\nConst Ptn_SquareStart   As String = \"^\\[\"\r\nConst Ptn_Square_JPN    As String = \"\\[[^A-Za-z0-9]+\\]\"\r\nConst Ptn_Square_ENG    As String = \"\\[[A-Za-z\\s:\\-,]+(\\]|\uff3d)\"\r\nDim RegExp_MedCategory      As Object\r\nDim RegExp_Foods_Group      As Object\r\nDim RegExp_Jpn_Eng_Mix      As Object\r\nDim RegExp_JapaneseOnly     As Object\r\nDim RegExp_English_Only     As Object\r\nDim RegExp_Upper_Lower      As Object\r\nDim RegExp_Upper_Only       As Object\r\nDim RegExp_Lower_Only       As Object\r\nDim RegExp_Angle_Bracket    As Object\r\nDim RegExp_Angle_Bracket_JP As Object\r\nDim RegExp_Angle_Bracket_EN As Object\r\nDim RegExp_Round_Bracket    As Object\r\nDim RegExp_Round_Bracket_JP As Object\r\nDim RegExp_Round_Bracket_EN As Object\r\nDim RegExp_SquareBracket    As Object\r\nDim RegExp_SquareBracket_JP As Object\r\nDim RegExp_SquareBracket_EN As Object\r\nDim RegExp_5_Number     As Object\r\nDim RegExp_Japanese     As Object\r\nDim RegExp_Alphabet     As Object\r\nDim myMatches           As Object\r\nDim myMatch             As Object\r\nConst Ptn_5_Number      As String = \"^[0-9]{5}$\"\r\nConst Ptn_Japanese      As String = \"[^A-Za-z0-9]{2,}\"\r\nConst Ptn_Alphabet      As String = \"^[A-Za-z]{2,}\"\r\nDim CEREALS             As Long\r\nDim POTATOES            As Long\r\nDim SUGARS              As Long\r\nDim PULSES              As Long\r\nDim NUTS                As Long\r\nDim VEGETABLES          As Long\r\nDim FRUITS              As Long\r\nDim MUSHROOMS           As Long\r\nDim ALGAE               As Long\r\nDim FISHES              As Long\r\nDim MEATS               As Long\r\nDim EGGS                As Long\r\nDim MILK                As Long\r\nDim OIL                 As Long\r\nDim CONFECTIONERIES     As Long\r\nDim BEVERAGES           As Long\r\nDim SEASONINGS          As Long\r\nDim PREPARED            As Long\r\nDim RegExpJapaneseName  As Object\r\nConst Ptn_JapaneseName  As String = \"^([0-9%]{1,3})?[^A-Za-z0-9]+\"\r\nSet RegExpJapaneseName = CreateObject(\"VBScript.RegExp\")\r\nWith RegExpJapaneseName\r\n    .Pattern = Ptn_JapaneseName\r\n    .IgnoreCase = True\r\n    .Global = True\r\nEnd With\r\nDim RegExp_EnglishName  As Object\r\nDim Ptn_EnglishName   As String\r\nPtn_EnglishName = \"^[A-Za-z0-9%\\.,\\-'\" & ChrW(&HC0) & \"-\" & ChrW(&HFF) & \"]+$\"\r\nSet RegExp_EnglishName = CreateObject(\"VBScript.RegExp\")\r\nWith RegExp_EnglishName\r\n    .Pattern = Ptn_EnglishName\r\n    .IgnoreCase = True\r\n    .Global = True\r\nEnd With\r\nSet RegExp_5_Number = CreateObject(\"VBScript.RegExp\")\r\nSet RegExp_MedCategory = CreateObject(\"VBScript.RegExp\")\r\nSet RegExp_Foods_Group = CreateObject(\"VBScript.RegExp\")\r\nSet RegExp_Jpn_Eng_Mix = CreateObject(\"VBScript.RegExp\")\r\nSet RegExp_JapaneseOnly = CreateObject(\"VBScript.RegExp\")\r\nSet RegExp_English_Only = CreateObject(\"VBScript.RegExp\")\r\nSet RegExp_Upper_Lower = CreateObject(\"VBScript.RegExp\")\r\nSet RegExp_Upper_Only = CreateObject(\"VBScript.RegExp\")\r\nSet RegExp_Lower_Only = CreateObject(\"VBScript.RegExp\")\r\nSet RegExp_Angle_Bracket = CreateObject(\"VBScript.RegExp\")\r\nSet RegExp_Angle_Bracket_JP = CreateObject(\"VBScript.RegExp\")\r\nSet RegExp_Angle_Bracket_EN = CreateObject(\"VBScript.RegExp\")\r\nSet RegExp_Round_Bracket = CreateObject(\"VBScript.RegExp\")\r\nSet RegExp_Round_Bracket_JP = CreateObject(\"VBScript.RegExp\")\r\nSet RegExp_Round_Bracket_EN = CreateObject(\"VBScript.RegExp\")\r\nSet RegExp_SquareBracket = CreateObject(\"VBScript.RegExp\")\r\nSet RegExp_SquareBracket_JP = CreateObject(\"VBScript.RegExp\")\r\nSet RegExp_SquareBracket_EN = CreateObject(\"VBScript.RegExp\")\r\nSet RegExp_Japanese = CreateObject(\"VBScript.RegExp\")\r\nSet RegExp_Alphabet = CreateObject(\"VBScript.RegExp\")\r\n    With RegExp_5_Number\r\n        .Pattern = Ptn_5_Number\r\n        .IgnoreCase = True\r\n        .Global = True\r\n    End With\r\n    With RegExp_Angle_Bracket\r\n        .Pattern = Ptn_AngleStart\r\n        .IgnoreCase = True\r\n        .Global = True\r\n    End With\r\n    With RegExp_Angle_Bracket_JP\r\n        .Pattern = Ptn_Angle_JPN\r\n        .IgnoreCase = True\r\n        .Global = True\r\n    End With\r\n    With RegExp_Angle_Bracket_EN\r\n        .Pattern = Ptn_Angle_ENG\r\n        .IgnoreCase = True\r\n        .Global = True\r\n    End With\r\n    With RegExp_Round_Bracket\r\n        .Pattern = Ptn_RoundStart\r\n        .IgnoreCase = True\r\n        .Global = True\r\n    End With\r\n    With RegExp_Round_Bracket_JP\r\n        .Pattern = Ptn_Round_JPN\r\n        .IgnoreCase = True\r\n        .Global = True\r\n    End With\r\n    With RegExp_Round_Bracket_EN\r\n        .Pattern = Ptn_Round_ENG\r\n        .IgnoreCase = True\r\n        .Global = True\r\n    End With\r\n    With RegExp_SquareBracket\r\n        .Pattern = Ptn_SquareStart\r\n        .IgnoreCase = True\r\n        .Global = True\r\n    End With\r\n    With RegExp_SquareBracket_JP\r\n        .Pattern = Ptn_Square_JPN\r\n        .IgnoreCase = True\r\n        .Global = True\r\n    End With\r\n    With RegExp_SquareBracket_EN\r\n        .Pattern = Ptn_Square_ENG\r\n        .IgnoreCase = True\r\n        .Global = True\r\n    End With\r\n    With RegExp_Japanese\r\n        .Pattern = Ptn_Japanese\r\n        .IgnoreCase = True\r\n        .Global = True\r\n    End With\r\n    With RegExp_Alphabet\r\n        .Pattern = Ptn_Alphabet\r\n        .IgnoreCase = False\r\n        .Global = True\r\n    End With\r\n    With RegExp_JapaneseOnly\r\n        .Pattern = Ptn_JapaneseOnly\r\n        .IgnoreCase = True\r\n        .Global = True\r\n    End With\r\nSet mySht1 = Worksheets(\"Sheet1\")\r\nSet mySht2 = Worksheets(\"Sheet2\")\r\nSet myRng = mySht1.UsedRange\r\ntmpAr = myRng\r\nMajor_CategoryAr = MajorCategoryAr(mySht2)\r\nReDim Preserve Major_CategoryAr(UBound(Major_CategoryAr), UBound(Major_CategoryAr, 2) + 2)\r\nm = 0\r\nFor i = LBound(tmpAr) To UBound(tmpAr)\r\n    For n = LBound(Major_CategoryAr) To UBound(Major_CategoryAr)\r\n        If Major_CategoryAr(n, 0) = tmpAr(i, 1) Then\r\n            Major_CategoryAr(n, 11) = i\r\n        End If\r\n        If Major_CategoryAr(n, 1) = tmpAr(i, 1) Then\r\n            Major_CategoryAr(n, 12) = i\r\n        End If\r\n    Next n\r\nNext i\r\nm = 0\r\nn = 0\r\np = 0\r\nq = 0\r\nNo_Cancel_Ar = NoCancelArray(mySht1)\r\nFor r = LBound(No_Cancel_Ar) To UBound(No_Cancel_Ar)\r\nFor i = No_Cancel_Ar(r, 0) To No_Cancel_Ar(r, 1)\r\n        str_JPN_Analyse = \"\"\r\n        str_ENG_Analyse = \"\"\r\n        On Error Resume Next\r\n        For k = 1 To 5\r\n            str_JPN_Analyse = str_JPN_Analyse & tmpAr(i, k)\r\n            str_ENG_Analyse = str_ENG_Analyse & \" \" & tmpAr(i + 1, k)\r\n            str_ENG_Analyse = Replace(str_ENG_Analyse, \"  \", \" \")\r\n        Next k\r\n        For k = 1 To 3\r\n            str_ENG_Analyse = str_ENG_Analyse & \" \" & tmpAr(i + 2, k)\r\n            str_ENG_Analyse = Replace(str_ENG_Analyse, \"  \", \" \")\r\n        Next k\r\n        On Error GoTo 0\r\n        str_ENG_Analyse = Trim(str_ENG_Analyse)\r\n        Select Case True\r\n        Case RegExp_Angle_Bracket.Test(str_JPN_Analyse) And _\r\n             RegExp_Angle_Bracket.Test(str_ENG_Analyse)\r\n            ReDim Preserve Sub_FoodGroup_JP(p)\r\n            ReDim Preserve Sub_FoodGroup_EN(p)\r\n            ReDim Preserve Sub_Group_JP_Row(p)\r\n            ReDim Preserve Sub_Group_EN_Row(p)\r\n            Set myMatches = RegExp_Angle_Bracket_JP.Execute(str_JPN_Analyse)\r\n            Sub_FoodGroup_JP(p) = myMatches.Item(0).Value\r\n            Sub_Group_JP_Row(p) = i\r\n            Set myMatches = RegExp_Angle_Bracket_EN.Execute(str_ENG_Analyse)\r\n            Sub_FoodGroup_EN(p) = myMatches.Item(0).Value\r\n            Sub_FoodGroup_EN(p) = Replace(Sub_FoodGroup_EN(p), \"\uff1c\", \"<\")\r\n            Sub_FoodGroup_EN(p) = Replace(Sub_FoodGroup_EN(p), \"\uff1e\", \">\")\r\n            Sub_Group_EN_Row(p) = i + 1\r\n            p = p + 1\r\n        Case RegExp_Round_Bracket_JP.Test(str_JPN_Analyse) And _\r\n             RegExp_Round_Bracket_EN.Test(str_ENG_Analyse)\r\n            ReDim Preserve Sub_Category_JPN(n)\r\n            ReDim Preserve Sub_Category_ENG(n)\r\n            ReDim Preserve SubCategory_RowJ(n)\r\n            ReDim Preserve SubCategory_RowE(n)\r\n            Set myMatches = RegExp_Round_Bracket_JP.Execute(str_JPN_Analyse)\r\n            Sub_Category_JPN(n) = myMatches.Item(0).Value\r\n            Sub_Category_JPN(n) = Replace(Sub_Category_JPN(n), \"(\", \"\uff08\")\r\n            Sub_Category_JPN(n) = Replace(Sub_Category_JPN(n), \")\", \"\uff09\")\r\n            SubCategory_RowJ(n) = i\r\n            Set myMatches = RegExp_Round_Bracket_EN.Execute(str_ENG_Analyse)\r\n            Sub_Category_ENG(n) = myMatches.Item(0).Value\r\n            Sub_Category_ENG(n) = Replace(Sub_Category_ENG(n), \"\uff08\", \"(\")\r\n            Sub_Category_ENG(n) = Replace(Sub_Category_ENG(n), \"\uff09\", \")\")\r\n            SubCategory_RowE(n) = i + 1\r\n            n = n + 1\r\n        Case RegExp_SquareBracket_JP.Test(str_JPN_Analyse) And _\r\n             RegExp_SquareBracket_EN.Test(str_ENG_Analyse)\r\n            ReDim Preserve MediumCategoryJP(m)\r\n            ReDim Preserve Med_JP_RowNumber(m)\r\n            ReDim Preserve MediumCategoryEN(m)\r\n            ReDim Preserve Med_EN_RowNumber(m)\r\n            Set myMatches = RegExp_SquareBracket_JP.Execute(str_JPN_Analyse)\r\n            MediumCategoryJP(m) = myMatches.Item(0).Value\r\n            Med_JP_RowNumber(m) = i\r\n            Set myMatches = RegExp_SquareBracket_EN.Execute(str_ENG_Analyse)\r\n            MediumCategoryEN(m) = myMatches.Item(0).Value\r\n            Med_EN_RowNumber(m) = i + 1\r\n            m = m + 1\r\n        Case RegExp_Japanese.Test(str_JPN_Analyse) And _\r\n             RegExp_Alphabet.Test(str_ENG_Analyse)\r\n            ReDim Preserve Major_CategoryJP(q)\r\n            ReDim Preserve Major_CategoryEN(q)\r\n            ReDim Preserve Major_JPN_RowNum(q)\r\n            ReDim Preserve Major_ENG_RowNum(q)\r\n            Set myMatches = RegExp_Japanese.Execute(str_JPN_Analyse)\r\n            Major_CategoryJP(q) = myMatches.Item(0).Value\r\n            Major_JPN_RowNum(q) = i\r\n            Set myMatches = RegExp_Alphabet.Execute(str_ENG_Analyse)\r\n            Major_CategoryEN(q) = myMatches.Item(0).Value\r\n            Major_ENG_RowNum(q) = i + 1\r\n            q = q + 1\r\n        Case Else\r\n        End Select\r\nNext i\r\nNext r\r\nReDim Major_Temp_Array(UBound(Major_CategoryJP), 5)\r\nFor q = LBound(Major_Temp_Array) To UBound(Major_Temp_Array) - 1\r\n    Major_Temp_Array(q, 0) = Major_CategoryJP(q)\r\n    Major_Temp_Array(q, 1) = Major_JPN_RowNum(q)\r\n    Major_Temp_Array(q, 2) = Major_JPN_RowNum(q + 1)\r\n    Major_Temp_Array(q, 3) = Major_CategoryEN(q)\r\n    Major_Temp_Array(q, 4) = Major_ENG_RowNum(q)\r\n    Major_Temp_Array(q, 5) = Major_ENG_RowNum(q + 1)\r\nNext q\r\n    Major_Temp_Array(q, 0) = Major_CategoryJP(UBound(Major_Temp_Array))\r\n    Major_Temp_Array(q, 1) = Major_JPN_RowNum(UBound(Major_Temp_Array))\r\n    Major_Temp_Array(q, 2) = 32757\r\n    Major_Temp_Array(q, 3) = Major_CategoryEN(UBound(Major_Temp_Array))\r\n    Major_Temp_Array(q, 4) = Major_ENG_RowNum(UBound(Major_Temp_Array))\r\n    Major_Temp_Array(q, 5) = 32757\r\nReDim MediumCategoryAr(UBound(MediumCategoryJP), 5)\r\nFor m = LBound(MediumCategoryAr) To UBound(MediumCategoryAr) - 1\r\n    MediumCategoryAr(m, 0) = MediumCategoryJP(m)\r\n    MediumCategoryAr(m, 1) = Med_JP_RowNumber(m)\r\n    MediumCategoryAr(m, 2) = Med_JP_RowNumber(m + 1)\r\n    MediumCategoryAr(m, 3) = MediumCategoryEN(m)\r\n    MediumCategoryAr(m, 4) = Med_EN_RowNumber(m)\r\n    MediumCategoryAr(m, 5) = Med_EN_RowNumber(m + 1)\r\nNext m\r\n    MediumCategoryAr(m, 0) = MediumCategoryJP(UBound(MediumCategoryAr))\r\n    MediumCategoryAr(m, 1) = Med_JP_RowNumber(UBound(MediumCategoryAr))\r\n    MediumCategoryAr(m, 2) = 26271\r\n    MediumCategoryAr(m, 3) = MediumCategoryEN(UBound(MediumCategoryAr))\r\n    MediumCategoryAr(m, 4) = Med_EN_RowNumber(UBound(MediumCategoryAr))\r\n    MediumCategoryAr(m, 5) = 26271\r\nFor m = LBound(MediumCategoryAr) To UBound(MediumCategoryAr)\r\n    For n = LBound(Major_CategoryAr) To UBound(Major_CategoryAr)\r\n        If CLng(MediumCategoryAr(m, 1)) > CLng(Major_CategoryAr(n, 11)) And _\r\n           CLng(MediumCategoryAr(m, 1)) < CLng(Major_CategoryAr(n, 12)) And _\r\n           CLng(Major_CategoryAr(n, 12)) < CLng(MediumCategoryAr(m, 2)) Then\r\n            MediumCategoryAr(m, 2) = Major_CategoryAr(n, 12)\r\n        End If\r\n        If CLng(MediumCategoryAr(m, 4)) > CLng(Major_CategoryAr(n, 11)) And _\r\n           CLng(MediumCategoryAr(m, 4)) < CLng(Major_CategoryAr(n, 12)) And _\r\n           CLng(Major_CategoryAr(n, 12)) < CLng(MediumCategoryAr(m, 5)) Then\r\n            MediumCategoryAr(m, 5) = Major_CategoryAr(n, 12)\r\n            Exit For\r\n        End If\r\n    Next n\r\nNext m\r\np = 0\r\nFor i = LBound(tmpAr) To UBound(tmpAr) - 1\r\n    strMinor_CategoryJP = \"\"\r\n    strMinor_CategoryEN = \"\"\r\n    For n = LBound(Major_CategoryAr) To UBound(Major_CategoryAr)\r\n        If RegExp_JapaneseOnly.Test(tmpAr(i, 1)) And _\r\n       Not RegExp_5_Number.Test(tmpAr(i, 1)) And _\r\n       Not RegExp_Round_Bracket.Test(tmpAr(i, 1)) And _\r\n       Not RegExp_SquareBracket.Test(tmpAr(i, 1)) And _\r\n       Not RegExp_Angle_Bracket.Test(tmpAr(i, 1)) And _\r\n           InStr(tmpAr(i, 1), Major_CategoryAr(n, 8)) <> 0 And _\r\n           InStr(tmpAr(i + 1, 1), Major_CategoryAr(n, 9)) <> 0 And _\r\n           i >= Major_CategoryAr(n, 11) And _\r\n           i <= Major_CategoryAr(n, 12) Then\r\n            ReDim Preserve Minor_CategoryJP(p)\r\n            ReDim Preserve Minor_CategoryEN(p)\r\n            ReDim Preserve Min_JP_RowNumber(p)\r\n            ReDim Preserve Min_EN_RowNumber(p)\r\n            For k = 1 To 2\r\n                strMinor_CategoryJP = strMinor_CategoryJP & tmpAr(i, k)\r\n                strMinor_CategoryEN = strMinor_CategoryEN & \" \" & tmpAr(i + 1, k)\r\n                strMinor_CategoryEN = Trim(strMinor_CategoryEN)\r\n            Next k\r\n            Set myMatches = RegExp_JapaneseOnly.Execute(strMinor_CategoryJP)\r\n            Minor_CategoryJP(p) = strMinor_CategoryJP\r\n            Min_JP_RowNumber(p) = i\r\n            Set myMatches = RegExp_Upper_Lower.Execute(strMinor_CategoryEN)\r\n            Minor_CategoryEN(p) = strMinor_CategoryEN\r\n            Min_EN_RowNumber(p) = i + 1\r\n            p = p + 1\r\n        Else\r\n        End If\r\n    Next n\r\nNext i\r\nj = 0\r\nFor i = LBound(tmpAr) To UBound(tmpAr)\r\n    strFoodGroup = \"\"\r\n    strSubFoodGroup = \"\"\r\n    strSub_Category = \"\"\r\n    strMajor_Category = \"\"\r\n    strMinor_Category = \"\"\r\n    strDetailCategory = \"\"\r\n    If RegExp_5_Number.Test(tmpAr(i, 1)) And tmpAr(i, 2) <> \"\uff08\u6b20\u756a\uff09\" Then\r\n        ReDim Preserve ItemNamAr(j)\r\n        ReDim Preserve ItemNumAr(j)\r\n        ReDim Preserve FoodGrouNum(j)\r\n        ReDim Preserve FoodGroupJP(j)\r\n        ReDim Preserve FoodGroupEN(j)\r\n        ReDim Preserve Sub_FoodGroup_JP(j)\r\n        ReDim Preserve Sub_FoodGroup_EN(j)\r\n        ReDim Preserve Sub_Category_JPN(j)\r\n        ReDim Preserve Sub_Category_ENG(j)\r\n        ReDim Preserve Major_CategoryJP(j)\r\n        ReDim Preserve Major_CategoryEN(j)\r\n        ReDim Preserve Major_CategoryLT(j)\r\n        ReDim Preserve Med_Category_JPN(j)\r\n        ReDim Preserve Med_Category_ENG(j)\r\n        ReDim Preserve Minor_CategoryJP(j)\r\n        ReDim Preserve Minor_CategoryEN(j)\r\n        ReDim Preserve DetailCategoryJP(j)\r\n        ReDim Preserve DetailCategoryEN(j)\r\n        ReDim Preserve JapaneseName(j)\r\n        ReDim Preserve English_Name(j)\r\n        ItemNamAr(j) = tmpAr(i, 1)\r\n        ItemNumAr(j) = i\r\n        Select Case True\r\n            Case Left(tmpAr(i, 1), 2) = \"01\"\r\n                FoodGrouNum(j) = Left(tmpAr(i, 1), 2)\r\n                FoodGroupJP(j) = \"\u7a40\u985e\"\r\n                FoodGroupEN(j) = \"CEREALS\"\r\n                CEREALS = CEREALS + 1\r\n            Case Left(tmpAr(i, 1), 2) = \"02\"\r\n                FoodGrouNum(j) = Left(tmpAr(i, 1), 2)\r\n                FoodGroupJP(j) = \"\u3044\u3082\u53ca\u3073\u3067\u3093\u7c89\u985e\"\r\n                FoodGroupEN(j) = \"POTATOES AND STARCHES\"\r\n                POTATOES = POTATOES + 1\r\n            Case Left(tmpAr(i, 1), 2) = \"03\"\r\n                FoodGrouNum(j) = Left(tmpAr(i, 1), 2)\r\n                FoodGroupJP(j) = \"\u7802\u7cd6\u53ca\u3073\u7518\u5473\u985e\"\r\n                FoodGroupEN(j) = \"SUGARS\"\r\n                SUGARS = SUGARS + 1\r\n            Case Left(tmpAr(i, 1), 2) = \"04\"\r\n                FoodGrouNum(j) = Left(tmpAr(i, 1), 2)\r\n                FoodGroupJP(j) = \"\u8c46\u985e\"\r\n                FoodGroupEN(j) = \"PULSES\"\r\n                PULSES = PULSES + 1\r\n            Case Left(tmpAr(i, 1), 2) = \"05\"\r\n                FoodGrouNum(j) = Left(tmpAr(i, 1), 2)\r\n                FoodGroupJP(j) = \"\u7a2e\u5b9f\u985e\"\r\n                FoodGroupEN(j) = \"NUTS AND SEEDS\"\r\n                NUTS = NUTS + 1\r\n            Case Left(tmpAr(i, 1), 2) = \"06\"\r\n                FoodGrouNum(j) = Left(tmpAr(i, 1), 2)\r\n                FoodGroupJP(j) = \"\u91ce\u83dc\u985e\"\r\n                FoodGroupEN(j) = \"VEGETABLES\"\r\n                VEGETABLES = VEGETABLES + 1\r\n            Case Left(tmpAr(i, 1), 2) = \"07\"\r\n                FoodGrouNum(j) = Left(tmpAr(i, 1), 2)\r\n                FoodGroupJP(j) = \"\u679c\u5b9f\u985e\"\r\n                FoodGroupEN(j) = \"FRUITS\"\r\n                FRUITS = FRUITS + 1\r\n            Case Left(tmpAr(i, 1), 2) = \"08\"\r\n                FoodGrouNum(j) = Left(tmpAr(i, 1), 2)\r\n                FoodGroupJP(j) = \"\u304d\u306e\u3053\u985e\"\r\n                FoodGroupEN(j) = \"MUSHROOMS\"\r\n                MUSHROOMS = MUSHROOMS + 1\r\n            Case Left(tmpAr(i, 1), 2) = \"09\"\r\n                FoodGrouNum(j) = Left(tmpAr(i, 1), 2)\r\n                FoodGroupJP(j) = \"\u85fb\u985e\"\r\n                FoodGroupEN(j) = \"ALGAE\"\r\n                ALGAE = ALGAE + 1\r\n            Case Left(tmpAr(i, 1), 2) = \"10\"\r\n                FoodGrouNum(j) = Left(tmpAr(i, 1), 2)\r\n                FoodGroupJP(j) = \"\u9b5a\u4ecb\u985e\"\r\n                FoodGroupEN(j) = \"FISHES AND SHELLFISHES\"\r\n                FISHES = FISHES + 1\r\n            Case Left(tmpAr(i, 1), 2) = \"11\"\r\n                FoodGrouNum(j) = Left(tmpAr(i, 1), 2)\r\n                FoodGroupJP(j) = \"\u8089\u985e\"\r\n                FoodGroupEN(j) = \"MEATS\"\r\n                MEATS = MEATS + 1\r\n            Case Left(tmpAr(i, 1), 2) = \"12\"\r\n                FoodGrouNum(j) = Left(tmpAr(i, 1), 2)\r\n                FoodGroupJP(j) = \"\u5375\u985e\"\r\n                FoodGroupEN(j) = \"EGGS\"\r\n                EGGS = EGGS + 1\r\n            Case Left(tmpAr(i, 1), 2) = \"13\"\r\n                FoodGrouNum(j) = Left(tmpAr(i, 1), 2)\r\n                FoodGroupJP(j) = \"\u4e73\u985e\"\r\n                FoodGroupEN(j) = \"MILKS\"\r\n                MILK = MILK + 1\r\n            Case Left(tmpAr(i, 1), 2) = \"14\"\r\n                FoodGrouNum(j) = Left(tmpAr(i, 1), 2)\r\n                FoodGroupJP(j) = \"\u6cb9\u8102\u985e\"\r\n                FoodGroupEN(j) = \"FATS AND OILS\"\r\n                OIL = OIL + 1\r\n            Case Left(tmpAr(i, 1), 2) = \"15\"\r\n                FoodGrouNum(j) = Left(tmpAr(i, 1), 2)\r\n                FoodGroupJP(j) = \"\u83d3\u5b50\u985e\"\r\n                FoodGroupEN(j) = \"CONFECTIONERIES\"\r\n                CONFECTIONERIES = CONFECTIONERIES + 1\r\n            Case Left(tmpAr(i, 1), 2) = \"16\"\r\n                FoodGrouNum(j) = Left(tmpAr(i, 1), 2)\r\n                FoodGroupJP(j) = \"\u3057\u597d\u98f2\u6599\u985e\"\r\n                FoodGroupEN(j) = \"BEVERAGES\"\r\n                BEVERAGES = BEVERAGES + 1\r\n            Case Left(tmpAr(i, 1), 2) = \"17\"\r\n                FoodGrouNum(j) = Left(tmpAr(i, 1), 2)\r\n                FoodGroupJP(j) = \"\u8abf\u5473\u6599\u53ca\u3073\u9999\u8f9b\u6599\u985e\"\r\n                FoodGroupEN(j) = \"SEASONINGS AND SPICES\"\r\n                SEASONINGS = SEASONINGS + 1\r\n            Case Left(tmpAr(i, 1), 2) = \"18\"\r\n                FoodGrouNum(j) = Left(tmpAr(i, 1), 2)\r\n                FoodGroupJP(j) = \"\u8abf\u7406\u52a0\u5de5\u98df\u54c1\u985e\"\r\n                FoodGroupEN(j) = \"PREPARED FOODS\"\r\n                PREPARED = PREPARED + 1\r\n            Case Else\r\n        End Select\r\n        If RegExpJapaneseName.Test(tmpAr(i, 2)) Then\r\n            Set myMatches = RegExpJapaneseName.Execute(tmpAr(i, 2))\r\n            JapaneseName(j) = myMatches.Item(0).Value\r\n        End If\r\n        For t = 1 To 6\r\n            If RegExp_EnglishName.Test(tmpAr(i + 1, t)) Then\r\n                English_Name(j) = English_Name(j) & \" \" & tmpAr(i + 1, t)\r\n                English_Name(j) = Trim(English_Name(j))\r\n            Else\r\n                Exit For\r\n            End If\r\n        Next t\r\n        For k = LBound(Major_CategoryAr) To UBound(Major_CategoryAr)\r\n            If CLng(tmpAr(i, 1)) >= CLng(Major_CategoryAr(k, 0)) _\r\n           And CLng(tmpAr(i, 1)) <= CLng(Major_CategoryAr(k, 1)) Then\r\n                Sub_FoodGroup_JP(j) = Major_CategoryAr(k, 4)\r\n                Sub_FoodGroup_EN(j) = Major_CategoryAr(k, 5)\r\n                Sub_Category_JPN(j) = Major_CategoryAr(k, 6)\r\n                Sub_Category_ENG(j) = Major_CategoryAr(k, 7)\r\n                Major_CategoryJP(j) = Major_CategoryAr(k, 8)\r\n                Major_CategoryEN(j) = Major_CategoryAr(k, 9)\r\n                Major_CategoryLT(j) = Major_CategoryAr(k, 10)\r\n                For m = LBound(MediumCategoryAr) To UBound(MediumCategoryAr)\r\n                    If i >= CLng(MediumCategoryAr(m, 1)) And _\r\n                       i <= CLng(MediumCategoryAr(m, 2)) Then\r\n                        Med_Category_JPN(j) = MediumCategoryAr(m, 0)\r\n                    End If\r\n                    If i >= CLng(MediumCategoryAr(m, 4)) And _\r\n                       i <= CLng(MediumCategoryAr(m, 5)) Then\r\n                        Med_Category_ENG(j) = MediumCategoryAr(m, 3)\r\n                    End If\r\n                Next m\r\n            Else\r\n            End If\r\n        Next k\r\n    Else\r\n        j = j - 1\r\n    End If\r\n    j = j + 1\r\nNext i\r\nReDim ItemArray(UBound(ItemNamAr), 14)\r\nFor i = LBound(ItemArray) To UBound(ItemArray)\r\n    ItemArray(i, 0) = ItemNamAr(i)\r\n    ItemArray(i, 1) = FoodGrouNum(i)\r\n    ItemArray(i, 2) = FoodGroupJP(i)\r\n    ItemArray(i, 3) = FoodGroupEN(i)\r\n    ItemArray(i, 4) = Sub_FoodGroup_JP(i)\r\n    ItemArray(i, 5) = Sub_FoodGroup_EN(i)\r\n    ItemArray(i, 6) = Sub_Category_JPN(i)\r\n    ItemArray(i, 7) = Sub_Category_ENG(i)\r\n    ItemArray(i, 8) = Major_CategoryJP(i)\r\n    ItemArray(i, 9) = Major_CategoryEN(i)\r\n    ItemArray(i, 10) = Major_CategoryLT(i)\r\n    ItemArray(i, 11) = Med_Category_JPN(i)\r\n    ItemArray(i, 12) = Med_Category_ENG(i)\r\n    ItemArray(i, 13) = JapaneseName(i)\r\n    ItemArray(i, 14) = English_Name(i)\r\nNext i\r\nSet mySht3 = Worksheets.Add\r\nWith mySht3\r\n    .Name = \"Result\"\r\n    .Range(\"A1\").Value = \"ItemNumber\"\r\n    .Range(\"B1\").Value = \"\u98df\u54c1\u7fa4\u756a\u53f7\"\r\n    .Range(\"C1\").Value = \"\u98df\u54c1\u7fa4\"\r\n    .Range(\"D1\").Value = \"FoodGroup\"\r\n    .Range(\"E1\").Value = \"\u526f\u5206\u985e\"\r\n    .Range(\"F1\").Value = \"SubFoodGroup\"\r\n    .Range(\"G1\").Value = \"\u533a\u5206\"\r\n    .Range(\"H1\").Value = \"SubCategory\"\r\n    .Range(\"I1\").Value = \"\u5927\u5206\u985e\"\r\n    .Range(\"J1\").Value = \"MajorCategory\"\r\n    .Range(\"K1\").Value = \"AcademicName\"\r\n    .Range(\"L1\").Value = \"\u4e2d\u5206\u985e\"\r\n    .Range(\"M1\").Value = \"MediumCategory\"\r\n    .Range(\"N1\").Value = \"\u5c0f\u5206\u985e\u30fb\u7d30\u5206\"\r\n    .Range(\"O1\").Value = \"MinorCategory_Details\"\r\n    .Range(\"A2:O1879\").Value = ItemArray\r\nEnd With\r\nEnd Sub\r\n\r\nFunction NoCancelArray(ByRef Sh As Worksheet) As Variant\r\nDim mySht           As Worksheet\r\nDim myRng           As Range\r\nDim tmpAr           As Variant\r\nDim i               As Long\r\nDim j               As Long\r\nDim RegExpCancel    As Object\r\nDim RegExp_Exit     As Object\r\nConst StrCancel     As String = \"^(1\\)|residues)$\"\r\nDim CancelItem()    As String\r\nDim CancelRow1()    As String\r\nDim CancelRow2()    As String\r\nDim myCancelAr()    As String\r\nDim Cancel_Array()  As String\r\nSet RegExpCancel = CreateObject(\"VBScript.RegExp\")\r\nWith RegExpCancel\r\n    .Pattern = StrCancel\r\n    .IgnoreCase = True\r\n    .Global = True\r\nEnd With\r\nSet mySht = Sh\r\nSet myRng = mySht.UsedRange\r\ntmpAr = myRng\r\nj = 0\r\nFor i = LBound(tmpAr) To UBound(tmpAr)\r\n    If RegExpCancel.Test(tmpAr(i, 1)) Then\r\n        ReDim Preserve CancelItem(j)\r\n        ReDim Preserve CancelRow1(i)\r\n        CancelItem(j) = tmpAr(i, 1)\r\n        CancelRow1(j) = i\r\n        j = j + 1\r\n    End If\r\nNext i\r\nReDim myCancelAr(UBound(CancelItem), 1)\r\nFor j = LBound(myCancelAr) To UBound(myCancelAr)\r\n    myCancelAr(j, 0) = CancelItem(j)\r\n    myCancelAr(j, 1) = CancelRow1(j)\r\nNext j\r\nReDim Preserve myCancelAr(UBound(myCancelAr), 2)\r\nj = 0\r\nFor i = LBound(myCancelAr) To UBound(myCancelAr) - 1\r\n    If myCancelAr(i, 0) = \"1)\" Then\r\n        If myCancelAr(i + 2, 0) = \"residues\" Then\r\n            myCancelAr(i, 2) = myCancelAr(i + 2, 1)\r\n        Else\r\n            myCancelAr(i, 2) = myCancelAr(i + 1, 1)\r\n        End If\r\n        j = j + 1\r\n    End If\r\nNext i\r\nErase CancelRow1\r\nj = 0\r\nReDim CancelRow1(j)\r\nReDim CancelRow2(j)\r\nCancelRow1(j) = myCancelAr(j, 1)\r\nCancelRow2(j) = myCancelAr(j, 2)\r\nFor i = LBound(myCancelAr) + 1 To UBound(myCancelAr)\r\n    If myCancelAr(i, 0) = \"1)\" And _\r\n       myCancelAr(i - 1, 0) <> \"1)\" Then\r\n        j = j + 1\r\n        ReDim Preserve CancelRow1(j)\r\n        ReDim Preserve CancelRow2(j)\r\n        CancelRow1(j) = myCancelAr(i, 1)\r\n        CancelRow2(j) = myCancelAr(i, 2)\r\n    End If\r\nNext i\r\nReDim Cancel_Array(UBound(CancelRow1), 1)\r\nj = 0\r\nFor j = LBound(Cancel_Array) To UBound(Cancel_Array)\r\n    Cancel_Array(j, 0) = CancelRow1(j)\r\n    Cancel_Array(j, 1) = CancelRow2(j)\r\nNext j\r\nj = 0\r\nCancel_Array(j, 0) = 1\r\nCancel_Array(j, 1) = CancelRow1(j)\r\nFor j = LBound(Cancel_Array) + 1 To UBound(Cancel_Array)\r\n    Cancel_Array(j, 0) = CancelRow2(j - 1)\r\n    Cancel_Array(j, 1) = CancelRow1(j)\r\nNext j\r\nNoCancelArray = Cancel_Array\r\nEnd Function\r\n<\/pre>\n
\u53c2\u7167\uff1a
\n\u65e5\u672c\u98df\u54c1\u6a19\u6e96\u6210\u5206\u88682010\u306ecsv\u30d5\u30a1\u30a4\u30eb<\/a>
\n\u65e5\u672c\u98df\u54c1\u6a19\u6e96\u6210\u5206\u88682010\u306e\u98df\u54c1\u756a\u53f7\u3092\u30ab\u30c6\u30b4\u30ea\u30fc\u5206\u985e\u3059\u308b\u3000\u305d\u306e2<\/a><\/p>\n