[b_rad_1983]

Debugging brings me to this line

Do While CodeStart < LastGameNHLData

When I bring my cursor over CodeStart I get Error2042 and LastGameNHLData is 149

So you are correct, But Im not sure how to make codestart an integer..

Thanks in advanced!

[a4u2fear]

b_rad, please look for CodeStart = and post what it equals to (or what you set it to). This is the line in the code you need to investigate. CodeStart is not getting set properly. Maybe the webpage you loaded in is a smidge different than the others or the information for that day's import is different.

[a4u2fear]

nevermind i found it, you are using my code directly:

CodeStart=Application.Match("Options", Sheets("NHLData").Range("A1:A100"), 0)

Look at the sheet "NHLData" or the sheet if you have renamed in. If you do not see the word "Options" in column A, rows 1 through 100, that is what is causing the error. I'm not sure how much you have used this code, it could just be one day that this is happening for, or did you just start? Sometimes SBR or whatever site your loading does have bad data some days and you cannot import that day.

By the way, I started a tutorial thread, if interested in handicapper think tank.

[b_rad_1983]

I just used it based on what you described in here. I found the other thread and will jump over there once I get through this error.
So far I have only copied what you putin here so iit's the data from the day in 2007.
I won't have time to try anything until late tomorrow. Have a great weekend!

[aramakilx]

thx alot, very useful data!

[b_rad_1983]

It seems every time I run the macro it over writes everything in my "nhldata" sheet. So if I add it options in 1-100 anywhere it erases it.

[a4u2fear]

It seems every time I run the macro it over writes everything in my "nhldata" sheet. So if I add it options in 1-100 anywhere it erases it.

Yes that is correct. NHLdata is the sheet where each game is loaded, then you take the data and put it into another sheet, then the NHLdata is erased and then populated with the next imported game. Again the information is moved to another sheet and this repeats. NHLdata is just the imported webpage.

[b_rad_1983]

Lets switch over to the think tank!

[A4K]

Okay, I got the data I needed, but the game score is off. When I look to see which cell the score is in, it appears the score is recorded in double a few cells up from where the module was told to look. I adjusted which cells to extract data from, but the score is still listed in double.

Example: April 5th 2009.. Braves @ Phillies, the final score was 4-1 Atlanta. The data in the cell is 44 for the Braves and 11 for the Phillies. How do I get Excel to split the data in those cells?

[a4u2fear]

A4K, can you post your code? This thread is older than another I have posted and I can't tell exactly which code you are using and why it is happening.

[A4K]

Here is the code, after I modified it from the beginning of this thread....

Public MLByear As String
Public MLBmonth As String
Public MLBday As String


Public Sub importdata()


'add a new sheet to put the data in
Application.DisplayAlerts = False
Application.DisplayAlerts = True
Sheets.Add After:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Name = "MLBData"


'http://www.sportsbookreview.com/betting-odds/mlb-baseball/pointspread/?date=20090405
MLByear = "09"
MLBmonth = "04"
MLBday = "05"




With Sheets("MLBData").QueryTables.Add(Connection:= _
"URL;http://www.sportsbookreview.com/betting-odds/mlb-baseball/pointspread/?date=" & "20" & MLByear & MLBmonth & MLBday, Destination:= _
Range("$A$1"))
.Name = "20" & MLByear & MLBmonth & MLBday
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With


CodeStart = Application.Match("Options", Sheets("MLBData").Range("A1:A100"), 0)


Sheets("MLBGames").Cells(2, 1) = Sheets("MLBData").Cells(CodeStart - 1, 1) 'Home team name
Sheets("MLBGames").Cells(2, 2) = Sheets("MLBData").Cells(CodeStart - 8, 1) 'Home team score
Sheets("MLBGames").Cells(2, 3) = Sheets("MLBData").Cells(CodeStart + 2, 1) 'Home team run line


Sheets("MLBGames").Cells(2, 4) = Sheets("MLBData").Cells(CodeStart - 2, 1) 'Away team name
Sheets("MLBGames").Cells(2, 5) = Sheets("MLBData").Cells(CodeStart - 7, 1) 'Away team score
Sheets("MLBGames").Cells(2, 6) = Sheets("MLBData").Cells(CodeStart + 1, 1) 'Away team run line


End Sub


When looking at the Data sheet, it appears the score comes out funny from SBR. The score comes in double. Look at cells 91 and 92 on the data sheet. The score should be 4 and a 1. Instead it got pulled as 44 and 11. How do I trim this?

Side note: This tutorial is great. I plan on setting up an Excel sheet and writing a loop to pull all of the run line data that I need for the last 5 seasons. I have everything else that I need. Thanks.

[a4u2fear]

A4K. Few things:

The display alerts command helps speed up the code. You have =false then =true right after. You want to put the =false at the beginning of code (below Public sub....) then =true at the very end of the code (above end sub). This will speed up code.

For the import, I don't have a good answer for you and I can't figure it out at the moment. I did however import a few pages and it looks to be very repetitive.

If the score is 8-4 it will be
88
44

if the score is 10-2 it will be
1010
22

if the score is 0-1 it will be
0
1

You can use a function called len(variable) where variable is the cell (i.e. Cells(44,1) = row 44 column 1)

if cells(44,1)=0, len (cells(44,1))=1
if cells(44,1)=44, len (cells(44,1))=2
if cells(44,1)=1010, len (cells(44,1))=4

so, if you know the length =1, you know that team scored 0,

if the length is 2, i.e. cells(44,1)=44, you know the team scored:
mid(cells(44,1),1,1)=4.
The first 1 here is the starting integer, or the first digit of cells(44,1), the second one is the length of the score we want or 1 (its 2 digits in length with the score doubled), we know the score =4

if the length is 4, i.e. cells(44,1)=1010, you know the team scored double digits:
mid(cells(44,1),1,2)=10.
The first 1 here is the starting integer, or the first digit of cells(44,1), the second one is the length of the score we want or 2 (its4 digits in length with the score doubled, so we want half), we know the score=10

Also, when you import a date with two games, you will need to find multiple duplicates of "options", which can be tricky, please post all answers in the newest Excel Tutorial in handicapper think tank

[chrismunney]

awesome stuff