Hi Jason,

Your codes only get the first matched class="B3", so it will always return
the first month data for a specified year. We need to modify the regular
expression to make it work. The regular expression should include 12
months' sub matches. And we also need to modify the RegexMid function to
make it has another parameter month. So this function can return the
desired month data depending on the passed in parameter. The followings are
codes work on my side now,

-----------------------------------------------------------------------

Sub getExportCanada()
Dim c As Range, rng As Range
Dim myStr As String
Dim test As Variant
Dim Val As Variant
Dim IE As Object

Const sURL1 As String =
"http://tonto.eia.doe.gov/dnav/ng/hist/n9132cn2m.htm"
Dim sURLdate As String
Dim m As String
Application.Cursor = xlWait
Set IE = CreateObject("InternetExplorer.Application")

IE.Navigate sURL1
While IE.ReadyState < 4
DoEvents
Wend
myStr = IE.Document.body.innerhtml

Dim StartRow As Long
Set rng = Range("A2").CurrentRegion


For Each c In rng
sURLdate = Format(c.Value2, "yyyy")
m = Format(c.Value2, "m")
c.Offset(0, 1).Value = RegexMid(myStr, sURLdate, CInt(m), "class=b3")
Next c

IE.Quit
Set IE = Nothing
Application.Cursor = xlDefault
End Sub

Private Function RegexMid(s As String, sYear As String, iMonth As Integer,
sMonthTotal) As String
Dim re As Object, mc As Object
Set re = CreateObject("vbscript.regexp")
re.IgnoreCase = True
re.MultiLine = True
re.Global = True
re.Pattern = "\b" & "nbsp;" & sYear & _
"[\s\S]+?" & sMonthTotal & "\D+(\d\D?\d+)" & _
"[\s\S]+?" & sMonthTotal & "\D+(\d\D?\d+)" & _
"[\s\S]+?" & sMonthTotal & "\D+(\d\D?\d+)" & _
"[\s\S]+?" & sMonthTotal & "\D+(\d\D?\d+)" & _
"[\s\S]+?" & sMonthTotal & "\D+(\d\D?\d+)" & _
"[\s\S]+?" & sMonthTotal & "\D+(\d\D?\d+)" & _
"[\s\S]+?" & sMonthTotal & "\D+(\d\D?\d+)" & _
"[\s\S]+?" & sMonthTotal & "\D+(\d\D?\d+)" & _
"[\s\S]+?" & sMonthTotal & "\D+(\d\D?\d+)" & _
"[\s\S]+?" & sMonthTotal & "\D+(\d\D?\d+)" & _
"[\s\S]+?" & sMonthTotal & "\D+(\d\D?\d+)" & _
"[\s\S]+?" & sMonthTotal & "\D+(\d\D?\d+)"

' Yes I know the syntax for the line above is wrong, I am working on that
as well

If re.test(s) = True Then
Set mc = re.Execute(s)
RegexMid = mc(0).SubMatches(iMonth - 1)
End If
Set re = Nothing
End Function
------------------------------------------------------------------

Please note, to make the codes work correctly, we must pass valid month
parsed from the Column A. That is to say, we need to fill Date in ColumnA
using 1/1/1973 instead of Jan 1973 as you used.


Best regards,
Colbert Zhou
Microsoft Newsgroup Support Team