It isn't entirely clear from your code snippet how the text is placed in the
myStr variable nor whether it contains the entire html table from the
website or if it contains a line-by-line "read ins" of that table. The
following code (not RegEx) assumes the **entire** HTML table has been saved
to a file on the hard drive; it then reads that entire file in, parses the
data and place it on the active worksheet with Column A containing the years
and Columns B through M containing January through December's values for
that year appearing in Column A. I have marked the code that reads the
entire file into the myStr variable in case you are using a different method
to fill the myStr variable with the **entire** HTML table of data and need
to change that part of my code. If you keep my code as posted, you will need
to change the example location of the data file and filename that I used in
the Open statement.
Sub DistributeYearData()
Dim Rng As Range
Dim X As Long
Dim Z As Long
Dim FileNum As Long
Dim myStr As String
Dim MonthData() As String
Dim YearParts() As String
' Read in entire file all at once
' {change file location in line below}
FileNum = FreeFile
Open "d:\temp\WebYearData.txt" For Binary As #FileNum
myStr = Space(LOF(FileNum))
Get #FileNum, , myStr
Close #FileNum
' Entire file now resides in the myStr variable
Set Rng = Range("A2")
YearParts = Split(myStr, "<td class='B4' ")
For X = 0 To UBound(YearParts) - 1
Rng.Offset(X).Value = Val(YearParts(X + 1))
MonthData = Split(YearParts(X + 1), "<td class='B3'")
For Z = 1 To 12
Rng.Offset(X, Z).Value = Val(Replace(MonthData(Z), ",", ""))
Next
Next
End Sub
--
Rick (MVP - Excel)
"Jason" wrote in message
...
Okay so the title is stupid, but I don't know how to explain in just a few
words my issue.
We have a range of dates in Column A
Jan 1973
Feb 1973
...
Apr 2009
I am trying to pull numbers from a website that correspond to totals for
each month, but the website (
http://tonto.eia.doe.gov/dnav/ng/hist/n9132cn2m.htm) only lists the years,
and then each line below it corresponds to a total for each month in that
year. Here is an example of the source for year 1973:
<tr
<td class='B4' 1973</td
<td class='B3'60</td
<td class='B3'384</td
<td class='B3'1,167</td
<td class='B3'931</td
<td class='B3'1,670</td
<td class='B3'1,598</td
<td class='B3'1,758</td
<td class='B3'1,829</td
<td class='B3'1,022</td
<td class='B3'1,465</td
<td class='B3'1,483</td
<td class='B3'1,456</td
</tr
I used the following code to test getting the January total for each year,
but how can I set my loop to skip down 12 cells to the next year?
Currently
it is putting the January value in each cell for each year
Set rng = Range("A2").CurrentRegion
Set rng = rng.Resize(rng.Rows.Count, 1)
For Each c In rng
sURLdate = Format(c.Value2, "yyyy")
c.Offset(0, 1).Value = RegexMid(myStr, sURLdate, "class=b3")