Avoiding run time error '424': object required

[DesignerKB2]

Hi,

I've written some VBA code to scrape information from a website using .document.getElementsByTagName and .document.getElementsByClassName however the webpage is dynamic and as a result the object I’m looking for doesn’t always appear in the source code. When this happens I get an “Avoiding run time error '424': object required” appear. Instead of getting this error I just want the Macro to realise that the information doesn’t exist and then move on to the next bit of code/loop rather than stop the macro. Any ideas?

Thanks,
Kieran

The code is here

Sub Select_Cells()
Dim cellrow As Integer
Dim cellcol As Integer
Dim landsize As String
cellrow = 25
cellcol = 3
Cells(cellrow, cellcol).Select
Dim IE As Object
Dim webpage As String
Dim t As Integer, r As Integer, c As Integer
Dim elemCollection As Object
Dim guesstimate As Object
For cellrow = 25 To 25
webpage = "inser property profile for 36 Goodwood Street, RICHMOND VIC 3121 for the onthehouse website in australia"
Set IE = CreateObject("internetexplorer.application")

With IE

.navigate webpage
While IE.ReadyState <> 4
DoEvents
Wend

Set elemCollection = IE.document.getElementsByTagName("TABLE")

ThisWorkbook.Worksheets(2).Cells(cellrow, 6) = elemCollection(0).Rows(1).Cells(1).innerText
ThisWorkbook.Worksheets(2).Cells(cellrow, 7) = elemCollection(2).Rows(1).Cells(0).innerText
ThisWorkbook.Worksheets(2).Cells(cellrow, 8) = elemCollection(2).Rows(1).Cells(3).innerText


Set guesstimate = IE.document.getElementsByClassName("guesstimate-value-estimate")
For t = 0 To (guesstimate.Length - 1)

ThisWorkbook.Worksheets(2).Cells(cellrow, 9) = guesstimate(t).innerText
Next t

End With
Next cellrow
Set IE = Nothing

MsgBox ("Complete")
End Sub

[SamT]

I cleaned and formatted your code to better read it. There are two logic errors in it, (See comments in code.) also can you tell us what line the error is occurring at?

Sub Select_Cells()

Dim cellrow As Integer
Dim IE As Object
Dim webpage As String
Dim t As Integer
Dim elemCollection As Object
Dim guesstimate As Object

webpage = "inser property profile for 36 Goodwood Street, RICHMOND VIC 3121 for the onthehouse website in australia"
Set IE = CreateObject("internetexplorer.application")

For cellrow = 25 To 25 'What?
With IE
.navigate webpage
While IE.ReadyState <> 4
DoEvents
Wend
Set elemCollection = .document.getElementsByTagName("TABLE")
End With 'IE

With ThisWorkbook.Worksheets(2)
.Cells(cellrow, 6) = elemCollection(0).Rows(1).Cells(1).innerText
.Cells(cellrow, 7) = elemCollection(2).Rows(1).Cells(0).innerText
.Cells(cellrow, 8) = elemCollection(2).Rows(1).Cells(3).innerText

Set guesstimate = IE.document.getElementsByClassName("guesstimate-value-estimate")
For t = 0 To (guesstimate.Length - 1)
.Cells(cellrow, 9) = guesstimate(t).innerText 'CellRow is constant for all values of t
Next t
End With 'ThisWorkbook

Next cellrow
Set IE = Nothing

MsgBox ("Complete")
End Sub

[DesignerKB2]

Thanks for cleaning up the code Sam T.

The first for loop that has cellrow = 25 to 25 was in there so that I could search multiple webpages with one macro. In the original code I had webpage = Cells(cellrow, cellcol).Value instead of the comment I put in the quotations "". I put that in for this forum because it was a particular webpage I was having trouble with.

You comment about the cellrow is constant for all values of t is valid. I was copying some more complicated code and just reduced it until I got the answer I wanted. On review I think there is only 1 item in the guesstimate-value-estimate Class Name so I could probably replace (t) with (0) and remove the for loop. I will try that.

So far as where I get the errors they occur for all the .Cells lines of code depending on whether the information is available or not. ie sometimes the websites I go to dont have the guesstimate-value-estimate class name so that creates an error, sometimes the first "TABLE" only has one row in it so the first .Cells returns an error.

Overnight my subconscious must have been working on the code because when I woke up in the morning I had another search on line and worked out that I could suppress the errors and keep the code going by adding an On Error Resume Next at the top of the code. This has resolved my issue.

Thanks again Sam T for looking at my issue.

Regards,
Kieran

[SamT]

:thumb