Re: Rank with criteria vba
Hi thanks
I've put Application.countifs($A$2:$A$10,A2,$B$2:$B$10,">"&B2)+1 in a loop and its working perfect
Expect some values in the B Column are "-"
How can i leave them values out?
Posts by qaz2123434
-
-
Hi i need to rank a column based on the Id Column
Id Value Result
1 5 1
1 4 2
2 8 1
2 7 2
2 6 3
3 9 1
3 8 2
3 7 3
3 6 4I know this can be done with a sumproduct formula but i need it in Vba
Thanks -
Re: Convert Miles and Furlongs to Yards
Thanks apo thats perfect
-
Hi
I need vba code to convert cell contents to yards
1 mile = 1760
1furlong = 220
1 yd = 1e.g
1m4f 100yds = 2740
1m = 1760
7f 30yds = 1570
2m5f = 4620
2m 110yds = 3630Thanks
-
Re: Add a comma after html innertext
Yeah Its called Keep Your Money!
-
Re: Add a comma after html innertext
I got it sorted just a simple macro, im an idiot!
CodeSub format() Columns("A:A").Select Selection.NumberFormat = "@" Columns("B:B").Select Selection.NumberFormat = "@" Columns("F:F").Select Selection.NumberFormat = "@" Range("A1").Select End Sub -
Re: MACRO to SELECT REQUIRED DATA FROM WEBSITE
Code
Display MoreSub getdata() Dim IE As Object the_start: Set IE = CreateObject("InternetExplorer.Application") IE.Visible = True IE.navigate "http://www.cotlook.com/" Do DoEvents If Err.Number <> 0 Then IE.Quit Set IE = Nothing GoTo the_start: End If Loop Until IE.readystate = 4 Set elemcollection = IE.document.GetElementsByTagName("table") For t = 0 To (elemcollection.Length - 1) For r = 0 To (elemcollection(t).Rows.Length - 1) For c = 0 To (elemcollection(t).Rows(r).Cells.Length - 1) ThisWorkbook.Sheets("sheet1").Cells(ActRw + r + 1, c + 1) = Trim(elemcollection(t).Rows(r).Cells(c).innertext) Next c Next r ActRw = ActRw + elemcollection(t).Rows.Length + 1 Next t IE.Quit Set IE = Nothing End Sub -
Re: Add a comma after html innertext
that will only work for the first time run the code twice and it changes back to dates
-
Re: Add a comma after html innertext
Im having an issue with Formatting now, i need Columns A,B and F to formatted as text on the "Cards" sheet but excel keeps changing its when i load the cards
and reads Column F as a date -
Re: Add a comma after html innertext
Thanks for your time pike
-
Re: Add a comma after html innertext
The Links Need To Be Updated At Midnight and The Betting Forecast Only Becomes Available At 9.00 AM
-
Re: Add a comma after html innertext
Pike you genius, it seems to get it twice but that dosent matter thank you
-
Re: Add a comma after html innertext
Its the same, only the time and title for the first race on each card of each meeting is missing. But thats not really important, the name of the race meeting and each races betting forecast is all i now need. The bettingforecast looks to be between "A" tags i thought they were for links. Can this be got?
-
Re: Add a comma after html innertext
Every time the link changes the time and race title are missing from the first table, could you put the name of the Meeting in it to e.g "Catterick WED 23 APR 2014" and the Bettingforecast
-
Re: Add a comma after html innertext
That is not getting all the cards and it has lost some of the table information and the time and race title
-
Re: Add a comma after html innertext
This Gets All the cards but i dont have the betting forecast for each card or the Name of the Meeting
Code
Display MoreSub LoadAllRaceCards() Dim IE As Object the_start: Set IE = CreateObject("InternetExplorer.Application") IE.Visible = True For rep = 1 To 20 If Len(Sheets("Links").Range("A" & rep)) = 0 Then GoTo the_end: End If pagetoload = Sheets("Links").Range("A" & rep) IE.navigate (pagetoload) Do DoEvents If Err.Number <> 0 Then IE.Quit Set IE = Nothing GoTo the_start: End If Loop Until IE.readystate = 4 Set elemcollection = IE.document.GetElementsByTagName("table") For t = 0 To (elemcollection.Length - 1) For r = 0 To (elemcollection(t).Rows.Length - 1) For c = 0 To (elemcollection(t).Rows(r).Cells.Length - 1) ThisWorkbook.Sheets("Cards").Cells(ActRw + r + 1, c + 1) = Trim(elemcollection(t).Rows(r).Cells(c).innertext) Next c Next r ActRw = ActRw + elemcollection(t).Rows.Length + 1 Next t Next rep the_end: IE.Quit Set IE = Nothing End Sub -
Re: Add a comma after html innertext
this is how i get the racingpost links
Code
Display MoreSub Getlinks() Dim IE As Object the_start: Set IE = CreateObject("InternetExplorer.Application") IE.Visible = False IE.navigate ("http://www.racingpost.com/horses2/cards/home.sd") Do DoEvents If Err.Number <> 0 Then IE.Quit Set IE = Nothing GoTo the_start: End If Loop Until IE.readystate = 4 Set AllHyperLinks = IE.document.GetElementsByTagName("A") Sheets("Links").Range("A1:A200").Clear r = 0 For Each Hyperlink In AllHyperLinks If InStr(Hyperlink.innertext, "Show all racecards for this meeting on one page") > 0 Then If InStr(Hyperlink, "crs_id") > 0 Then Sheets("Links").Range("A1").Offset(r, c).Value = (Hyperlink & "&type=0&tab=lc_") r = r + 1 End If End If Next IE.Quit Set IE = Nothing End Sub -
Re: Add a comma after html innertext
thanks pike buts thats not it, maybe you could have a look at this workbook it gets all the tables but not the Race Meeting name or The Betting forecast
-
Re: Add a comma after html innertext
Csv brings it in as one column. i can save it as html with a comma i replaced the ">" at the end of each elements innertext to ",<" How can i bring a html file into excel?
-
Re: Add a comma after html innertext
when i get the innertext i save it to a text file so that i can import it back to excel and get what i need from the text, but the problem is its all in one column
if i could add a comma to the innertext then i could use delimit