National Institute of Health and Nutrition has revised ‘METs table of Physical Activities’ 2011 edition<\/a>. Because it’s difficult to use as PDF file, I have extracted text data from it. I have asked if I could publish the file on this blog on July 3rd, 2012.<\/p>\n

1. Open the file and copy all text.<\/p>\n

2. Create new EXCEL book and paste with ‘Text File Wizard’. In the second tab, you have to remove all check mark of delimiters. In the third tab, select ‘String’ data type of column.<\/p>\n

3. Press ‘Alt’ key and ‘F11’ key to launch VBE, insert module, and run the following code.<\/p>\n

Option Explicit\r\nSub METS()\r\nDim mySht   As Worksheet\r\nDim myRng   As Range\r\nDim myAr    As Variant\r\nDim i       As Long\r\nDim j       As Long\r\nDim myReg   As Object\r\nDim myMatches   As Object\r\nDim myMatch     As Object\r\nConst strReg    As String = \"^[0-9]{5}$\"\r\nDim CODE()  As String\r\nDim METS()  As Single\r\nDim MajorHeading()  As String\r\nDim SpecificActivities()    As String\r\nDim myArray()   As Variant\r\nSet myReg = CreateObject(\"VBScript.RegExp\")\r\nWith myReg\r\n    .Pattern = strReg\r\n    .IgnoreCase = True\r\n    .Global = True\r\nEnd With\r\n\r\nSet mySht = ActiveSheet\r\nSet myRng = mySht.UsedRange\r\nmyAr = myRng\r\nj = 0\r\nFor i = LBound(myAr) To UBound(myAr)\r\n    If myReg.Test(myAr(i, 1)) Then\r\n        Set myMatches = myReg.Execute(myAr(i, 1))\r\n        ReDim Preserve CODE(j)\r\n        ReDim Preserve METS(j)\r\n        ReDim Preserve MajorHeading(j)\r\n        ReDim Preserve SpecificActivities(j)\r\n        CODE(j) = myAr(i, 1)\r\n        METS(j) = myAr(i + 1, 1)\r\n        MajorHeading(j) = myAr(i + 2, 1)\r\n        SpecificActivities(j) = myAr(i + 3, 1)\r\n        j = j + 1\r\n    End If\r\nNext i\r\nReDim myArray(j, 3)\r\nFor j = LBound(myArray) To UBound(myArray) - 1\r\n    myArray(j, 0) = CODE(j)\r\n    myArray(j, 1) = METS(j)\r\n    myArray(j, 2) = MajorHeading(j)\r\n    myArray(j, 3) = SpecificActivities(j)\r\nNext j\r\nSet mySht = Worksheets.Add\r\nmySht.Range(\"A1\").Value = \"CODE\"\r\nmySht.Range(\"B1\").Value = \"METS\"\r\nmySht.Range(\"C1\").Value = \"MajorHeading\"\r\nmySht.Range(\"D1\").Value = \"SpecificActivities\"\r\nmySht.Range(\"A2:D827\") = myArray\r\nEnd Sub\r\n<\/pre>\n<\/div>","protected":false},"excerpt":{"rendered":"National Institute of Health and Nutrition has revised ‘METs table of Physical Activities’ 2011 ed … “Extract from text revised edition of “METs table of Physical Activities”” \u306e<\/span>\u7d9a\u304d\u3092\u8aad\u3080<\/a><\/p>\n","protected":false},"author":1,"featured_media":6026,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[6,4],"tags":[603,267,268,153],"class_list":["post-628","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-excel","category-nutrition","tag-excel","tag-mets-table-of-physical-activities","tag-national-institute-of-health-and-nutrition","tag-vba"],"aioseo_notices":[],"aioseo_head":"\n\t\t\n\t\n\t\n\t\n\t\n\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t