[solring]

Any sample code for logging into a user account for Pinnacle to get to the Dynamic Odds pages (the ones with drop-downs) rather than the static lines available to everyone?

[oilcountry99]

heres an easy on for you guys....

In cell A1 i have 951 L.A. Dodgers in cell A2 I want to see just L.A. Dodgers

I'm wondering what formula I need in A2 to produce these results.

Also will the same formula work if i have 8 L.A. Dodgers or 25 L.A. Dodgers in cell A1

Oil

[oilcountry99]

Here's another one....

In VBA how do I find a variable and delete all rows below.

I want to find tomorrows date so I used


Dim nextday As String

nextday = Format(Date + 1, "d-mmm-yy")


'Clear rows below tomorrow's date (nextday)

With Sheets("Sheet1")
.Rows(nextday.Row & ":" & .Rows.Count).Delete
End With

I know this is jacked, because I don't know how to do it......please help!!!

[b_rad_1983]

heres an easy on for you guys....

In cell A1 i have 951 L.A. Dodgers in cell A2 I want to see just L.A. Dodgers

I'm wondering what formula I need in A2 to produce these results.

Also will the same formula work if i have 8 L.A. Dodgers or 25 L.A. Dodgers in cell A1

Oil

You will want to use either LEFT,RIGHT or Mid function

[a4u2fear]

Any sample code for logging into a user account for Pinnacle to get to the Dynamic Odds pages (the ones with drop-downs) rather than the static lines available to everyone?

someone else posted this question in the think tank and he PMd me about it. It seems like Pinnacle recently changed their log in to have encryption so I do not know how as of now.

[a4u2fear]

heres an easy on for you guys....

In cell A1 i have 951 L.A. Dodgers in cell A2 I want to see just L.A. Dodgers

I'm wondering what formula I need in A2 to produce these results.

Also will the same formula work if i have 8 L.A. Dodgers or 25 L.A. Dodgers in cell A1

Oil

You will need to use the "instr(...)" command to find the first instance of a space or " ". The instr command will return the integer, in this case, 4 because there are three digits before it.

You can then use the "mid(...)" function to return the string from the 5th digit of "951 L.A. Dodgers" to the end. The 5th digit is L because the 4th is the space.

You can use the "len(...)" function to find the length of "951 L.A. Dodgers" so you use the Mid function to grab the string from the 5th digit to the end of the string (told by len)

[a4u2fear]

Here's another one....

In VBA how do I find a variable and delete all rows below.

I want to find tomorrows date so I used


Dim nextday As String

nextday = Format(Date + 1, "d-mmm-yy")


'Clear rows below tomorrow's date (nextday)

With Sheets("Sheet1")
.Rows(nextday.Row & ":" & .Rows.Count).Delete
End With

I know this is jacked, because I don't know how to do it......please help!!!

you need to use:
Application.Match(stringtosearchfor, range,0)

if you're looking in column A, start searching in A1:A100 or wahtever. If the string is found, and is must match exactly, it will return the row it is in.

[a4u2fear]

Just a random thought, but most of you guys posting in here have been members of the forum for quite a few years but have very few posts. Glad I'm getting some posts out of you or at least exciting your interest.

[b_rad_1983]

brad, you need more loops to simplify. You basically have a (4) sets of (3) lines written for each division above. ]

The below is for metro and atlantic parts you posted
You could simplify to

for z=0 to 4 step 4

y=0
for x=1 to 4

if x=3 then
y=1
elseif x=4 then
y=4
end if

Sheets("Sheet1").Cells(lastrowsheetone + 1+z, x+1) = Sheets("Temp").Cells(findmatch + 2+z, x+y) '1st
Sheets("Sheet1").Cells(lastrowsheetone + 2+z, x+1) = Sheets("Temp").Cells(findmatch + 3+z, x+y) '2nd
Sheets("Sheet1").Cells(lastrowsheetone + 3+z, x+1) = Sheets("Temp").Cells(findmatch + 4+z, x+y) '3rd

Next

I like this, it gives me the Atlantic and Metro but I do not know enough to get the wild card.

i think I will need one more loop to do the wild card..

I want to try just the wild card as a loop,




Dim wild As Integer
wild = 9


For Z = 0 To 4 Step 4


y = 0
For x = 1 To 4


If x = 3 Then '3 is division
y = 1
ElseIf x = 4 Then
y = 4
End If


Do
Sheets("Sheet1").Cells(lastrowsheetone + wild + Z, x + 1) = Sheets("Temp").Cells(findmatch + wild + 1 + Z, x + y) '3rd
wild = wild + 1


Loop Until wild = 16
Next
Next




It outputs--------------------------------------------------------

Wild Card
	1
	2
	3
	4
	5
		Ottawa
		Carolina
		NY Islanders
		Florida
		Buffalo
		Central
		x - St. Louis
		x - Colorado
		x - Chicago
		Pacific
		x - Anaheim
		x - San Jose
		Los Angeles
		Wild Card
		Minnesota
		Phoenix
		Dallas
		Vancouver
		Nashville
		Winnipeg
		Calgary
		Edmonton

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

then overflow error.. I'm more or less just guessing at the moment.

[oilcountry99]

you need to use:
Application.Match(stringtosearchfor, range,0)

if you're looking in column A, start searching in A1:A100 or wahtever. If the string is found, and is must match exactly, it will return the row it is in.

How do I do the delete portion after the string is found?

Where you show 'stringtosearchfor' do I replace that with my variable 'nextday'?

This seems like such a basic idea 'find this and once it is found delete the that row and the rows below. Is this not something people typically do.

Sorry but I'm quite new to this, appreciate all your help.

So frustrating at times, after a lot of searching and experimentation with no success.... Arghh

[b_rad_1983]

I ended up getting the wild card with the following which is very basic..

Dim i As Integer


i = 9


Do
Sheets("Sheet1").Cells(lastrowsheetone + i, 2) = Sheets("Temp").Cells(findmatch + i + 1, 1)
Sheets("Sheet1").Cells(lastrowsheetone + i, 3) = Sheets("Temp").Cells(findmatch + i + 1, 2)
Sheets("Sheet1").Cells(lastrowsheetone + i, 4) = Sheets("Temp").Cells(findmatch + i + 1, 4)
Sheets("Sheet1").Cells(lastrowsheetone + i, 5) = Sheets("Temp").Cells(findmatch + i + 1, 8)
i = i + 1
Loop Until i = 21

[a4u2fear]

How do I do the delete portion after the string is found?

Where you show 'stringtosearchfor' do I replace that with my variable 'nextday'?

This seems like such a basic idea 'find this and once it is found delete the that row and the rows below. Is this not something people typically do.

Sorry but I'm quite new to this, appreciate all your help.

So frustrating at times, after a lot of searching and experimentation with no success.... Arghh

string to search for is your date you are looking for yes

Sheets("Games").Rows(matched & ":" & lastrow).Delete

where matched=Application.Match(stringtosearch for, range,0)

and lastrow is the last row of your data you want deleted, the exact code for last row is in one of the earlier pages. It finds the last row that is populated, or you could set it to an arbitrary number that you know the data is within, i.e. 1000 if the data is in the first 1000 rows

[a4u2fear]

I ended up getting the wild card with the following which is very basic..

Dim i As Integer


i = 9


Do
Sheets("Sheet1").Cells(lastrowsheetone + i, 2) = Sheets("Temp").Cells(findmatch + i + 1, 1)
Sheets("Sheet1").Cells(lastrowsheetone + i, 3) = Sheets("Temp").Cells(findmatch + i + 1, 2)
Sheets("Sheet1").Cells(lastrowsheetone + i, 4) = Sheets("Temp").Cells(findmatch + i + 1, 4)
Sheets("Sheet1").Cells(lastrowsheetone + i, 5) = Sheets("Temp").Cells(findmatch + i + 1, 8)
i = i + 1
Loop Until i = 21

glad you figured it out.

[b_rad_1983]

glad you figured it out.

I was able to do the same as an alternate to what you did plus the wild card,


Dim i As Integer
i = 1


Do


If i = 4 Then
i = 5
ElseIf i = 8 Then
i = 9
ElseIf i = 12 Then
i = 13
Else
Sheets("Sheet1").Cells(lastrowsheetone + i, 2) = Sheets("Temp").Cells(findmatch + i + 1, 1)
Sheets("Sheet1").Cells(lastrowsheetone + i, 3) = Sheets("Temp").Cells(findmatch + i + 1, 2)
Sheets("Sheet1").Cells(lastrowsheetone + i, 4) = Sheets("Temp").Cells(findmatch + i + 1, 4)
Sheets("Sheet1").Cells(lastrowsheetone + i, 5) = Sheets("Temp").Cells(findmatch + i + 1, 8)
i = i + 1
End If




Loop Until i = 21

[oilcountry99]

string to search for is your date you are looking for yes

Sheets("Games").Rows(matched & ":" & lastrow).Delete

where matched=Application.Match(stringtosearch for, range,0)

and lastrow is the last row of your data you want deleted, the exact code for last row is in one of the earlier pages. It finds the last row that is populated, or you could set it to an arbitrary number that you know the data is within, i.e. 1000 if the data is in the first 1000 rows

Here it is !!!!!!!!!!!!!!!!!!!!!! So rewarding when it finally goes as it should, thanks a million


Sub Schedule()
'
' Schedule Macro
'
Dim nextday As Date
Dim lastrow As Long
Dim findmatch As String


'Get schedule from cbssports

With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://www.cbssports.com/mlb/schedules/day/0401/regular", Destination:= _
Range("$A$1"))
.Name = "regular"
.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


'find last row on sheet

lastrow = ActiveSheet.UsedRange.Rows.Count


'Define tomorrows date

nextday = Format(Date + 1, "d-mmm-yy")


'Find tomorrows date

findmatch = Application.Match(CLng(nextday), Range("A1:A100"), 0)


'Clear rows below tomorrow's date (nextday)

ActiveSheet.Rows(findmatch & ":" & lastrow).Delete


End Sub

[b_rad_1983]

Good work OIL!

[b_rad_1983]

a4u2fear,
What's next for us?

[b_rad_1983]

What about bringing in a table without using a temp sheet. This table will be also formatted as is from the site including colours and fonts.

[b_rad_1983]

Here is a code to bring in MLB standings

Im not sure how to add come colors to highlight the titles

Sub Webquery()
Dim url As String

url = "URL;http://espn.go.com/mlb/standings"

With Worksheets("Sheet1").QueryTables.Add(Connection:=url, Destination:=Worksheets("Sheet1").Range("A1"))

.Name = "table"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = "2"
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = True
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With



End Sub

[b_rad_1983]

'Change colors
Range("A1:l1,a20:l20").Interior.Color = RGB(25, 25, 112)
Range("A1:l1,a20:l20").Font.Color = vbWhite
Range("A2:l2,a8:l8,a14:l14,a21:l21,a27:l27,a33: l33").Interior.Color = RGB(100, 149, 237)

'Add Border
Range("A1:l38").Borders.LineStyle = xlContinuous

[solring]

Any examples on importing and manipulating xml using vba?

[pringles]

someone else posted this question in the think tank and he PMd me about it. It seems like Pinnacle recently changed their log in to have encryption so I do not know how as of now.

The problem is how do I input the authentication into VB code ?
I received an answer directing me to
http://excelramblings.blogspot.com/2...ntication.html

What I want to do is just to get the XML feed to excel sheet, but this time i have to use VB which is why Im asking for your help as you know your way in VB

[oilcountry99]

Here it is !!!!!!!!!!!!!!!!!!!!!! So rewarding when it finally goes as it should, thanks a million


Sub Schedule()
'
' Schedule Macro
'
Dim nextday As Date
Dim lastrow As Long
Dim findmatch As String


'Get schedule from cbssports

With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://www.cbssports.com/mlb/schedules/day/0401/regular", Destination:= _
Range("$A$1"))
.Name = "regular"
.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


'find last row on sheet

lastrow = ActiveSheet.UsedRange.Rows.Count


'Define tomorrows date

nextday = Format(Date + 1, "d-mmm-yy")


'Find tomorrows date

findmatch = Application.Match(CLng(nextday), Range("A1:A100"), 0)


'Clear rows below tomorrow's date (nextday)

ActiveSheet.Rows(findmatch & ":" & lastrow).Delete


End Sub

For some reason I'm getting a type mismatch error 13 on the red code above, any ideas why? (It was working before)

thanks for your help.

[b_rad_1983]

glad you figured it out.

I'm currently using the following to bring in my NHL standings. Problem I have is that I have formulas in R1:T31 and it loads my table to A31.
I would like to force it to start at A1.



Public Sub PullData()


Dim lastrowsheetone As Integer
Dim findmatch As Integer


lastrowsheetone = Sheets("Sheet1").UsedRange.Rows.Count
findmatch = Application.Match("DIV", Sheets("Sheet2").Range("C1:C300"), 0)


For x = 1 To 31 '30 teams
For Y = 1 To 17 '17 Titles


Sheets("Sheet1").Cells(lastrowsheetone + x - 1, Y) = Sheets("Sheet2").Cells(findmatch + x - 1, Y) 'this is Everything


Next
Next

End Sub

[solring]

I'm currently using the following to bring in my NHL standings. Problem I have is that I have formulas in R1:T31 and it loads my table to A31.
I would like to force it to start at A1.

That's because you're telling it to start on row 31 by using "lastrowsheetone". Just use the "x-1" part...

Sheets("Sheet1").Cells(lastrowsheetone + x - 1, Y) = Sheets("Sheet2").Cells(findmatch + x - 1, Y) 'this is Everything
End Sub

[b_rad_1983]

That's because you're telling it to start on row 31 by using "lastrowsheetone". Just use the "x-1" part...

Thanks solring!

I tried replacing lastrowsheetone with a1, "a1", "$A$1". Type mismatch...

Im not sure what type of value it needs. I will keep plugging away at it!

[b_rad_1983]

Also I have a cell with 1:05 PM ET

How can i search from n1:n100 , when it finds a cell with time , I would like to
Change colors for every time it finds time

Range("a11").Interior.Color = RGB(25, 25, 112)
Range("a11").Font.Color = vbWhite

I tried something like this

Set FoundCell =Range("n1:n100").Find(what:=Time)

but no luck

[b_rad_1983]

Maybe it would be easier to say something like

if a1 contains "at" then
Range("a1: p1").Interior.Color = RGB(25, 25, 112)
Range("a1: p1").Font.Color = vbWhite

or would i need to use a $a$1?

the keyword "at" could appear until a100

[b_rad_1983]

I have this

Dim cell As Object


For Each cell In ActiveSheet.UsedRange
If cell.Value = "STARTERS" Then
'cell.Interior.ColorIndex = 36
Range("A1: p1").Interior.Color = RGB(25, 25, 112)
Range("A1: p1").Font.Color = vbWhite
End If

But it only puts does the color in a1: p1
where I would like the color to be up 2 rows then fill across

[solring]

I tried replacing lastrowsheetone with a1, "a1", "$A$1". Type mismatch...

Im not sure what type of value it needs. I will keep plugging away at it!

Both rows and columns are represented in the .Cells function by number. So, "A1" = row 1, column 1. "Z43" = row 43, column 26.

[oilcountry99]

B_rad
Not exactly sure what your trying to do but this might be another option. Create a sheet formatted how you like as far as colours fonts etc. import your data/web query to a temp sheet and then copy and paste it to your formatted pretty sheet. Just make sure your imported data is in the same cells as your formatted sheet. They must coordinate.

Also when you paste use pastespecial values

[b_rad_1983]

Im just trying to add some colours to the table but only when there are certain key words.
Your way does work oil but, if there are more games then others it won't have the proper colours, if I add the colours to too many rows there may not be data.

[b_rad_1983]

It would be nice if there is an option to import the colours from the site with the table

[b_rad_1983]

Got it Oil...

This will search row a1 through a100. Look for the word STARTERS. Once found it will move up 2 rows and color the entire row as well as change the font color.


Dim r As Range
Dim i As Long
Set r = Range("A1:A100")
For i = r.Rows.Count To 1 Step -1
With r.Cells(i, 1)
If .Value = "STARTERS" Then
Range("A" & i - 2).EntireRow.Interior.Color = RGB(25, 25, 112)
Range("A" & i - 2).EntireRow.Font.Color = vbWhite
End If
End With
Next I

[b_rad_1983]

That's because you're telling it to start on row 31 by using "lastrowsheetone". Just use the "x-1" part...

I ended up just erasing the last part and moved my formulas to the page that brings in the table.