National Institute of Health and Nutrition has revised ‘METs table of Physical Activities’ 2011 edition. 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.
1. Open the file and copy all text.
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.
3. Press ‘Alt’ key and ‘F11’ key to launch VBE, insert module, and run the following code.
Option Explicit
Sub METS()
Dim mySht As Worksheet
Dim myRng As Range
Dim myAr As Variant
Dim i As Long
Dim j As Long
Dim myReg As Object
Dim myMatches As Object
Dim myMatch As Object
Const strReg As String = "^[0-9]{5}$"
Dim CODE() As String
Dim METS() As Single
Dim MajorHeading() As String
Dim SpecificActivities() As String
Dim myArray() As Variant
Set myReg = CreateObject("VBScript.RegExp")
With myReg
.Pattern = strReg
.IgnoreCase = True
.Global = True
End With
Set mySht = ActiveSheet
Set myRng = mySht.UsedRange
myAr = myRng
j = 0
For i = LBound(myAr) To UBound(myAr)
If myReg.Test(myAr(i, 1)) Then
Set myMatches = myReg.Execute(myAr(i, 1))
ReDim Preserve CODE(j)
ReDim Preserve METS(j)
ReDim Preserve MajorHeading(j)
ReDim Preserve SpecificActivities(j)
CODE(j) = myAr(i, 1)
METS(j) = myAr(i + 1, 1)
MajorHeading(j) = myAr(i + 2, 1)
SpecificActivities(j) = myAr(i + 3, 1)
j = j + 1
End If
Next i
ReDim myArray(j, 3)
For j = LBound(myArray) To UBound(myArray) - 1
myArray(j, 0) = CODE(j)
myArray(j, 1) = METS(j)
myArray(j, 2) = MajorHeading(j)
myArray(j, 3) = SpecificActivities(j)
Next j
Set mySht = Worksheets.Add
mySht.Range("A1").Value = "CODE"
mySht.Range("B1").Value = "METS"
mySht.Range("C1").Value = "MajorHeading"
mySht.Range("D1").Value = "SpecificActivities"
mySht.Range("A2:D827") = myArray
End Sub
