jamescooper
Well-known Member
- Joined
- Sep 8, 2014
- Messages
- 840
If you put the following hyperlinks into cells I2:I10 and then run the code below it is failing on the line:
With the runtime error code in the title of this thread.
Any ideas please?
Thanks.
[TABLE="width: 614"]
<colgroup><col></colgroup><tbody>[TR]
[TD]https://greyhoundbet.racingpost.com/dog/blocks.sd?race_id=1695059&r_date=2019-05-14&dog_id=527104&blocks=details[/TD]
[/TR]
[TR]
[TD]https://greyhoundbet.racingpost.com/dog/blocks.sd?race_id=1695059&r_date=2019-05-14&dog_id=526755&blocks=details[/TD]
[/TR]
[TR]
[TD]https://greyhoundbet.racingpost.com/dog/blocks.sd?race_id=1695059&r_date=2019-05-14&dog_id=531916&blocks=details[/TD]
[/TR]
[TR]
[TD]https://greyhoundbet.racingpost.com/dog/blocks.sd?race_id=1695059&r_date=2019-05-14&dog_id=515879&blocks=details[/TD]
[/TR]
[TR]
[TD]https://greyhoundbet.racingpost.com/dog/blocks.sd?race_id=1695059&r_date=2019-05-14&dog_id=524903&blocks=details[/TD]
[/TR]
[TR]
[TD]https://greyhoundbet.racingpost.com/dog/blocks.sd?race_id=1695059&r_date=2019-05-14&dog_id=521755&blocks=details[/TD]
[/TR]
[TR]
[TD]https://greyhoundbet.racingpost.com/dog/blocks.sd?race_id=1695060&r_date=2019-05-14&dog_id=528411&blocks=details[/TD]
[/TR]
[TR]
[TD]https://greyhoundbet.racingpost.com/dog/blocks.sd?race_id=1695060&r_date=2019-05-14&dog_id=517039&blocks=details[/TD]
[/TR]
[TR]
[TD]https://greyhoundbet.racingpost.com/dog/blocks.sd?race_id=1695060&r_date=2019-05-14&dog_id=525527&blocks=details[/TD]
[/TR]
</tbody>[/TABLE]
Code:
Set details = JSONConvert.ParseJson(.responseText)("details")
With the runtime error code in the title of this thread.
Any ideas please?
Thanks.
[TABLE="width: 614"]
<colgroup><col></colgroup><tbody>[TR]
[TD]https://greyhoundbet.racingpost.com/dog/blocks.sd?race_id=1695059&r_date=2019-05-14&dog_id=527104&blocks=details[/TD]
[/TR]
[TR]
[TD]https://greyhoundbet.racingpost.com/dog/blocks.sd?race_id=1695059&r_date=2019-05-14&dog_id=526755&blocks=details[/TD]
[/TR]
[TR]
[TD]https://greyhoundbet.racingpost.com/dog/blocks.sd?race_id=1695059&r_date=2019-05-14&dog_id=531916&blocks=details[/TD]
[/TR]
[TR]
[TD]https://greyhoundbet.racingpost.com/dog/blocks.sd?race_id=1695059&r_date=2019-05-14&dog_id=515879&blocks=details[/TD]
[/TR]
[TR]
[TD]https://greyhoundbet.racingpost.com/dog/blocks.sd?race_id=1695059&r_date=2019-05-14&dog_id=524903&blocks=details[/TD]
[/TR]
[TR]
[TD]https://greyhoundbet.racingpost.com/dog/blocks.sd?race_id=1695059&r_date=2019-05-14&dog_id=521755&blocks=details[/TD]
[/TR]
[TR]
[TD]https://greyhoundbet.racingpost.com/dog/blocks.sd?race_id=1695060&r_date=2019-05-14&dog_id=528411&blocks=details[/TD]
[/TR]
[TR]
[TD]https://greyhoundbet.racingpost.com/dog/blocks.sd?race_id=1695060&r_date=2019-05-14&dog_id=517039&blocks=details[/TD]
[/TR]
[TR]
[TD]https://greyhoundbet.racingpost.com/dog/blocks.sd?race_id=1695060&r_date=2019-05-14&dog_id=525527&blocks=details[/TD]
[/TR]
</tbody>[/TABLE]
Code:
Sub Dog_Form()
Dim LastRow As Long
Dim x As Long
Dim urls As Variant
Dim dogLinks As Variant
Application.ScreenUpdating = True
LastRow = Sheets("Races").Range("I" & Rows.Count).End(xlUp).row
urls = Sheets("Races").Range("I2:I" & LastRow).Value
Sheets("Dog Form").Visible = True
For x = LBound(urls) To UBound(urls)
dogLinks = getDogForm(urls(x, 1))
Sheets("Dog Form").Cells(Sheets("Dog Form").Rows.Count, 1).End(xlUp).Offset(1).Resize(UBound(dogLinks), 20).Value2 = dogLinks
DoEvents
Next x
Sheets("Dog Form").Range("U2").FormulaR1C1 = "=IF(RC[-18]=0,"""",RC[-18]&"" ""&VLOOKUP(RC[-19],'Look-up'!R1C8:R50C9,2,FALSE))"
Sheets("Dog Form").Range("U2").AutoFill Destination:=Range("U2:U" & Sheets("Dog Form").Range("A" & Rows.Count).End(xlUp).row)
Sheets("Dog Form").Range("V2").FormulaR1C1 = "=IF(IF(TRIM(RC[-17])="""","""",IF(OR(RC[-17]=0,RC[-17]=0),"""",IF(VLOOKUP(RC[-1],'Track Records'!C3:C7,5,FALSE)=""-"","""",IF((VLOOKUP(RC[-1],'Track Records'!C3:C7,5,FALSE)/RC[-17])*100>100,"""",VLOOKUP(RC[-1],'Track Records'!C3:C7,5,FALSE)/RC[-17])*100)))<70,"""",(IF(TRIM(RC[-17])="""","""",IF(OR(RC[-17]=0,RC[-17]=0),"""",IF(VLOOKUP(RC[-1],'Track Records'!C3:C7,5,FALSE)=""-"","""",IF((VLOOKUP(RC[-1],'Track Records'!C3:C7,5,FALSE)/RC[-17])*100>100,"""",VLOOKUP(RC[-1],'Track Records'!C3:C7,5,FALSE)/RC[-17])*100)))))"
Sheets("Dog Form").Range("V2").AutoFill Destination:=Range("V2:V" & Sheets("Dog Form").Range("A" & Rows.Count).End(xlUp).row)
Sheets("Dog Form").Range("W2").FormulaR1C1 = "=IF(IF(TRIM(RC[-7])="""","""",IF(AND(RC[-18]=0,RC[-7]=0),"""",IF((VLOOKUP(RC[-2],'Track Records'!C3:C7,4,FALSE)/RC[-7])*100>100,"""",VLOOKUP(RC[-2],'Track Records'!C3:C7,4,FALSE)/RC[-7])*100))<70,"""",IF(TRIM(RC[-7])="""","""",IF(AND(RC[-18]=0,RC[-7]=0),"""",IF((VLOOKUP(RC[-2],'Track Records'!C3:C7,4,FALSE)/RC[-7])*100>100,"""",VLOOKUP(RC[-2],'Track Records'!C3:C7,4,FALSE)/RC[-7])*100)))"
Sheets("Dog Form").Range("W2").AutoFill Destination:=Range("W2:W" & Sheets("Dog Form").Range("A" & Rows.Count).End(xlUp).row)
Sheets("Dog Form").Range("X2").FormulaR1C1 = "=(TODAY()+1)-RC[-23]"
Sheets("Dog Form").Range("X2").AutoFill Destination:=Range("X2:X" & Sheets("Dog Form").Range("A" & Rows.Count).End(xlUp).row)
Sheets("Dog Form").Range("Y2").FormulaR1C1 = "=DATEDIF(RC[-6],RC[-24],""y"")&"" Years ""&DATEDIF(RC[-6],RC[-24],""ym"")&"" Months """
Sheets("Dog Form").Range("Y2").AutoFill Destination:=Range("Y2:Y" & Sheets("Dog Form").Range("A" & Rows.Count).End(xlUp).row)
Sheets("Dog Form").Range("Z2").FormulaR1C1 = "=DATEDIF(RC[-7],TODAY()+1,""y"")&"" Years ""&DATEDIF(RC[-7],TODAY()+1,""ym"")&"" Months """
Sheets("Dog Form").Range("Z2").AutoFill Destination:=Range("Z2:Z" & Sheets("Dog Form").Range("A" & Rows.Count).End(xlUp).row)
Sheets("Dog Form").Range("AA2").FormulaR1C1 = "=INDEX(Races!C2,MATCH(TRIM(INDEX(Races!C11,MATCH(TRIM('Dog Form'!RC20),Races!C12,0))),Races!C10,0))&"" ""&INDEX(Races!C3,MATCH(TRIM(INDEX(Races!C11,MATCH(TRIM('Dog Form'!RC20),Races!C12,0))),Races!C10,0))"
Sheets("Dog Form").Range("AA2").AutoFill Destination:=Range("AA2:AA" & Sheets("Dog Form").Range("A" & Rows.Count).End(xlUp).row)
Sheets("Dog Form").Range("AB2").FormulaR1C1 = "=(LEFT(INDEX(Races!C4,MATCH(TRIM(INDEX(Races!C11,MATCH(TRIM('Dog Form'!RC20),Races!C12,0))),Races!C10,0)),3))&"" ""&INDEX(Races!C3,MATCH(TRIM(INDEX(Races!C11,MATCH(TRIM('Dog Form'!RC20),Races!C12,0))),Races!C10,0))"
Sheets("Dog Form").Range("AB2").AutoFill Destination:=Range("AB2:AB" & Sheets("Dog Form").Range("A" & Rows.Count).End(xlUp).row)
Sheets("Dog Form").Range("AC2").FormulaR1C1 = "=INDEX(Races!C5,MATCH(TRIM(INDEX(Races!C11,MATCH(TRIM('Dog Form'!RC20),Races!C12,0))),Races!C10,0))"
Sheets("Dog Form").Range("AC2").AutoFill Destination:=Range("AC2:AC" & Sheets("Dog Form").Range("A" & Rows.Count).End(xlUp).row)
Sheets("Dog Form").Range("AD2").FormulaR1C1 = "=LEFT(RC[-3],4)&"" ""&INDEX('Look-up'!R1C1:R50C1,MATCH(TRIM(MID('Dog Form'!RC[-2],5,30)),'Look-up'!R1C2:R50C2,0))"
Sheets("Dog Form").Range("AD2").AutoFill Destination:=Range("AD2:AD" & Sheets("Dog Form").Range("A" & Rows.Count).End(xlUp).row)
Columns("X:X").Select
Selection.NumberFormat = "0"
Range("A1").FormulaR1C1 = "Date"
Range("B1").FormulaR1C1 = "Track"
Range("C1").FormulaR1C1 = "Dis"
Range("D1").FormulaR1C1 = "Trp"
Range("E1").FormulaR1C1 = "Split"
Range("F1").FormulaR1C1 = "Bends"
Range("G1").FormulaR1C1 = "Fin"
Range("H1").FormulaR1C1 = "By"
Range("I1").FormulaR1C1 = "Win/Sec"
Range("J1").FormulaR1C1 = "Remarks"
Range("K1").FormulaR1C1 = "WnTm"
Range("L1").FormulaR1C1 = "Gng"
Range("M1").FormulaR1C1 = "Wght"
Range("N1").FormulaR1C1 = "SP"
Range("O1").FormulaR1C1 = "Grade"
Range("P1").FormulaR1C1 = "CalTm"
Range("Q1").FormulaR1C1 = "Dog Name"
Range("R1").FormulaR1C1 = "Trap Number"
Range("S1").FormulaR1C1 = "DOB"
Range("T1").FormulaR1C1 = "Dog ID"
Range("U1").FormulaR1C1 = "Form Event"
Range("V1").FormulaR1C1 = "Form Rating - Sectional"
Range("W1").FormulaR1C1 = "Form Rating - Full"
Range("X1").FormulaR1C1 = "Days Since Form Event"
Range("Y1").FormulaR1C1 = "Age at Date of Event"
Range("Z1").FormulaR1C1 = "Current Age"
Range("AA1").FormulaR1C1 = "Event Entered"
Range("AB1").FormulaR1C1 = "Event Distance & Track"
Range("AC1").FormulaR1C1 = "Grade"
Range("AD1").FormulaR1C1 = "Time"
Range("A1:AD1").Font.Bold = True
Columns("A:AD").Font.Size = 8
Sheets("Dog Form").Columns(21).Copy
Sheets("Dog Form").Columns(21).PasteSpecial xlPasteValues
Sheets("Dog Form").Columns(22).Copy
Sheets("Dog Form").Columns(22).PasteSpecial xlPasteValues
Sheets("Dog Form").Columns(23).Copy
Sheets("Dog Form").Columns(23).PasteSpecial xlPasteValues
Sheets("Dog Form").Columns(24).Copy
Sheets("Dog Form").Columns(24).PasteSpecial xlPasteValues
Sheets("Dog Form").Columns(25).Copy
Sheets("Dog Form").Columns(25).PasteSpecial xlPasteValues
Sheets("Dog Form").Columns(26).Copy
Sheets("Dog Form").Columns(26).PasteSpecial xlPasteValues
Sheets("Dog Form").Columns(27).Copy
Sheets("Dog Form").Columns(27).PasteSpecial xlPasteValues
Sheets("Dog Form").Columns(28).Copy
Sheets("Dog Form").Columns(28).PasteSpecial xlPasteValues
Sheets("Dog Form").Columns(29).Copy
Sheets("Dog Form").Columns(29).PasteSpecial xlPasteValues
Sheets("Dog Form").Columns(30).Copy
Sheets("Dog Form").Columns(30).PasteSpecial xlPasteValues
Application.ScreenUpdating = True
End Sub
Private Function getDogForm(ByVal url As String) As Variant
Dim forms As Collection
Dim form As Object
Dim ret() As Variant
Dim x As Long
Dim details As Object
Dim dogName As String
Dim trapNum As String
Dim dateOfBirth As String
Dim dogId As String
With CreateObject("msxml2.xmlhttp")
.Open "GET", url, False
.send
Set details = JSONConvert.ParseJson(.responseText)("details")
End With
Set forms = details("forms")
dogName = details("dogInfo")("dogName")
trapNum = details("dogInfo")("trapNum")
dateOfBirth = details("dogInfo")("dateOfBirth")
dogId = details("dogInfo")("dogId")
ReDim ret(1 To forms.Count, 1 To 20)
For Each form In forms
x = x + 1
ret(x, 1) = form("shortDate")
ret(x, 2) = form("trackShortName")
ret(x, 3) = form("distMetre")
ret(x, 4) = "[" & form("trapNum") & "]"
ret(x, 5) = form("secTimeS")
ret(x, 6) = form("bndPos")
ret(x, 7) = Right(form("rOutcomeDesc"), 3)
ret(x, 8) = form("rpDistDesc")
ret(x, 9) = form("otherDTxt")
ret(x, 10) = form("closeUpCmnt")
ret(x, 11) = form("winnersTimeS")
ret(x, 12) = form("goingType")
ret(x, 13) = form("weight")
ret(x, 14) = form("oddsDesc")
ret(x, 15) = form("rGradeCde")
ret(x, 16) = form("calcRTimeS")
ret(x, 17) = dogName
ret(x, 18) = trapNum
ret(x, 19) = dateOfBirth
ret(x, 20) = dogId
Next form
getDogForm = ret
End Function