run time error 91 object variable or with block not set - reference is not found in looping program

renewgeorgia · Mar 22, 2016

Hi,
I am a little new here. Running a VBA script in excel where it calls to a website to pull information into the excel sheet.

The excel has a list of addresses. The script uses the addresses to interact with an URL / API to get search results.

It works fine up until the address in question only has part of the results. For instance, the first ten addresses come back with all the info, but the 11th has everything but the last sale price. So it comes up as Error 91 and stops. Why? because the term "lastsaleprice" it is searching for is not in the data that was returned for this particular address.

I attempted an if else statement, but it did not work.

I want it to either skip the area that is blank, or return "no data", and continue on with the remaining addresses. The continuing on to the next address is the main goal.

The area is red is where it is currently getting stuck, but I want to add if /else or whatever fixes this error to all statements so it does not get stuck and runs to completion.

any help is greatly appreciated.

Here is the code:

Sub ZillowXML()

' Zillow Web Service ID
ZWSID = "edited out"

' Number of header columns
Headers = 2

' Columns containing addresses
Address = "E"
City = "F"
State = "G"
Zip = "H"

' Columns to return data
ErrorMessage = "J"
HomeDetails = "K"
Graphsanddata = "L"
Mapthishome = "M"
Comparables = "N"
latitude = "O"
longitude = "P"
ZAmount = "Q"
LastUpdate = "R"
zLow = "S"
zHigh = "T"
Rent = "U"
RentLastUpdate = "V"
RentLow = "W"
RentHigh = "X"
Region = "Y"
Overview = "Z"
FSBO = "AA"
forsale = "AB"
taxassessmentyear = "ac"
taxassessment = "ad"
yearbuilt = "ae"
finishedsqft = "af"
bedrooms = "ag"
bathrooms = "ah"
lastSoldDate = "ai"
lastSoldPrice = "aj"

' Changes to make
' Automatically read address from a MsgBox dialog and transpose that list to a new sheet
' Automatically create new columns to put data in
' Convert new data range to a table and name it "Zillow"

Dim xmldoc As MSXML2.DOMDocument60
Dim xmlNodeList As MSXML2.IXMLDOMNodeList
Dim myNode As MSXML2.IXMLDOMNode
Dim WS As Worksheet: Set WS = ActiveSheet

' Seth column to display API URL for troubleshooting
'xmlURL = "E"

' Tell user the code is running
Application.StatusBar = "Starting search"

' Count Rows
LastRow = Cells(Rows.Count, 1).End(xlUp).Row

'___________________________________________________

' Begin looping through rows to find and insert data
' i = 3 leaves the first two row as column headers
For i = Headers + 1 To LastRow

'Clear previous data from cells
WS.Range(ErrorMessage & i) = ""
WS.Range(HomeDetails & i) = ""
WS.Range(Graphsanddata & i) = ""
WS.Range(Mapthishome & i) = ""
WS.Range(Comparables & i) = ""
WS.Range(latitude & i) = ""
WS.Range(longitude & i) = ""
WS.Range(ZAmount & i) = ""
WS.Range(LastUpdate & i) = ""
WS.Range(zLow & i) = ""
WS.Range(zHigh & i) = ""
WS.Range(Rent & i) = ""
WS.Range(RentLastUpdate & i) = ""
WS.Range(RentLow & i) = ""
WS.Range(RentHigh & i) = ""
WS.Range(Region & i) = ""
WS.Range(Overview & i) = ""
WS.Range(FSBO & i) = ""
WS.Range(forsale & i) = ""
WS.Range(taxassessmentyear & i) = ""
WS.Range(taxassessment & i) = ""
WS.Range(yearbuilt & i) = ""
WS.Range(finishedsqft & i) = ""
WS.Range(bedrooms & i) = ""
WS.Range(bathrooms & i) = ""
WS.Range(lastSoldDate & i) = ""
WS.Range(lastSoldPrice & i) = ""

' Create Zillow API URL
rowAddress = WS.Range(Replace(Address, " ", "+") & i)
rowCity = WS.Range(City & i)
rowState = WS.Range(State & i)
rowZip = WS.Range(Zip & i)
' Comment out to use testing URL
URL = "http://www.zillow.com/webservice/GetDeepSearchResults.htm?zws-id=" & ZWSID & "&address=" & rowAddress & "&citystatezip=" & rowCity & "%2C+" & rowState & "%2C+" & rowZip & "&rentzestimate=true"
' Local URL for testing
'URL = ("C:\Users\John\Dropbox\Excel\ZillowXML.xml")

' Uncomment to Display API URL for troubleshooting
'WS.Range(xmlURL & i) = ""
'WS.Range(xmlURL & i).Formula = "=HYPERLINK(""" & URL & """,""API URL"")"

' Tell user what address is being searched for
Application.StatusBar = "Retrieving: " & i & " of " & LastRow - Headers & ": " & rowAddress & ", " & rowCity & ", " & rowState

'Open XML page
Set xmldoc = New MSXML2.DOMDocument60
xmldoc.async = False

' Check XML document is loaded
If xmldoc.Load(URL) Then

Set xmlMessage = xmldoc.SelectSingleNode("//message/text")
Set xmlMessageCode = xmldoc.SelectSingleNode("//message/code")

' Check for an error message
If xmlMessageCode.Text <> 0 Then

' Return error message
WS.Range(ErrorMessage & i) = xmlMessage.Text

Else
' Get XML data from Zillow
Set xmlHomeDetails = xmldoc.SelectSingleNode("//response/results/result/links/homedetails")
Set xmlGraphsAndData = xmldoc.SelectSingleNode("//response/results/result/links/graphsanddata")
Set xmlComparables = xmldoc.SelectSingleNode("//response/results/result/links/comparables")
Set xmlMapthishome = xmldoc.SelectSingleNode("//response/results/result/links/mapthishome")
' Push data to preadsheet
If xmlHomeDetails Is Nothing Then
WS.Range(HomeDetails & i) = "No home details available"
Else
WS.Range(HomeDetails & i).Formula = "=HYPERLINK(""" & xmlHomeDetails.Text & """,""Zillow Details"")"
End If
If xmlGraphsAndData Is Nothing Then
WS.Range(Graphsanddata & i) = "No graphs available"
Else
WS.Range(Graphsanddata & i).Formula = "=HYPERLINK(""" & xmlGraphsAndData.Text & """,""Graphs & Data"")"
End If
If xmlComparables Is Nothing Then
WS.Range(Comparables & i) = "No comparables available"
Else
WS.Range(Comparables & i).Formula = "=HYPERLINK(""" & xmlComparables.Text & """,""Zillow Comparables"")"
End If
If xmlMapthishome Is Nothing Then
WS.Range(Mapthishome & i) = "No map available"
Else
WS.Range(Mapthishome & i).Formula = "=HYPERLINK(""" & xmlMapthishome.Text & """,""Zillow Map"")"
End If

' Retrieve Lat & Long
Set xmlLatitude = xmldoc.SelectSingleNode("//response/results/result/address/latitude")
Set xmlLongitude = xmldoc.SelectSingleNode("//response/results/result/address/longitude")
' Push data to preadsheet
WS.Range(latitude & i) = xmlLatitude.Text
WS.Range(longitude & i) = xmlLongitude.Text

' Retrieve Zestimate
Set xmlZAmount = xmldoc.SelectSingleNode("//response/results/result/zestimate/amount")
Set xmlZLastUpdate = xmldoc.SelectSingleNode("//response/results/result/zestimate/last-updated")
Set xmlZValLow = xmldoc.SelectSingleNode("//response/results/result/zestimate/valuationRange/low")
Set xmlZValHigh = xmldoc.SelectSingleNode("//response/results/result/zestimate/valuationRange/high")
' Push data to preadsheet
WS.Range(ZAmount & i) = xmlZAmount.Text
WS.Range(ZAmount & i).NumberFormat = "$#,##0_);($#,##0)"
WS.Range(LastUpdate & i) = xmlZLastUpdate.Text
WS.Range(zLow & i) = xmlZValLow.Text
WS.Range(zLow & i).NumberFormat = "$#,##0_);($#,##0)"
WS.Range(zHigh & i) = xmlZValHigh.Text
WS.Range(zHigh & i).NumberFormat = "$#,##0_);($#,##0)"

' Retrieve RentZestimate
Set xmlRZAmount = xmldoc.SelectSingleNode("//response/results/result/rentzestimate/amount")
Set xmlRZLastUpdate = xmldoc.SelectSingleNode("//response/results/result/rentzestimate/last-updated")
Set xmlRZValLow = xmldoc.SelectSingleNode("//response/results/result/rentzestimate/valuationRange/low")
Set xmlRZValHigh = xmldoc.SelectSingleNode("//response/results/result/rentzestimate/valuationRange/high")
' Push data to preadsheet
WS.Range(Rent & i) = xmlRZAmount.Text
WS.Range(Rent & i).NumberFormat = "$#,##0_);($#,##0)"
WS.Range(RentLastUpdate & i) = xmlRZLastUpdate.Text
WS.Range(RentLow & i) = xmlRZValLow.Text
WS.Range(RentLow & i).NumberFormat = "$#,##0_);($#,##0)"
WS.Range(RentHigh & i) = xmlRZValHigh.Text
WS.Range(RentHigh & i).NumberFormat = "$#,##0_);($#,##0)"

' Retrieve Year built
Set xmlyearBuilt = xmldoc.SelectSingleNode("//response/results/result/yearBuilt")
' Push data to preadsheet
WS.Range(yearbuilt & i) = xmlyearBuilt.Text

' Retrieve Finished Sq Feet
Set xmlfinishedSqFt = xmldoc.SelectSingleNode("//response/results/result/finishedSqFt")
' Push data to preadsheet
WS.Range(finishedsqft & i) = xmlfinishedSqFt.Text

' Retrieve bedrooms
Set xmlbedrooms = xmldoc.SelectSingleNode("//response/results/result/bedrooms")
' Push data to preadsheet
WS.Range(bedrooms & i) = xmlbedrooms.Text

' Retrieve bathrooms
Set xmlbathrooms = xmldoc.SelectSingleNode("//response/results/result/bathrooms")
' Push data to preadsheet
WS.Range(bathrooms & i) = xmlbathrooms.Text

' Retrieve last Sold Price

Set xmllastSoldPrice = xmldoc.SelectSingleNode("//response/results/result/lastSoldPrice")
' Push data to preadsheet
If xmlHomeDetails Is Nothing Then
WS.Range(lastSoldPrice & i) = "None"
Else
WS.Range(lastSoldPrice & i) = xmllastSoldPrice.Text
WS.Range(lastSoldPrice & i).NumberFormat = "$#,##0_);($#,##0)"

End If

End If

' Document failed to load statement
Else
WS.Range(ErrorMessage & i) = "The document failed to load. Check your internet connection."

End If

' Loop to top for next row
Next i

' Tell user the search is complete
Application.StatusBar = "Search complete!"

End Sub

Private Sub CommandButton1_Click()

End Sub

mrmmickle1 · Mar 22, 2016

Use:

Code:

[COLOR=#0000ff][B]Sub[/B][/COLOR] YourSub()
[B][COLOR=#0000ff]
On Error Resume Next[/COLOR][/B]

[COLOR=#008000][B]'YOUR CODE[/B][/COLOR]

[COLOR=#0000ff][B]On Error Go To 0[/B][/COLOR]
[COLOR=#0000ff][B]
End Sub[/B][/COLOR]

renewgeorgia · Mar 23, 2016

mrmmickle1 said:

Use:

Code:

[COLOR=#0000ff][B]Sub[/B][/COLOR] YourSub()
[B][COLOR=#0000ff]
On Error Resume Next[/COLOR][/B]

[COLOR=#008000][B]'YOUR CODE[/B][/COLOR]

[COLOR=#0000ff][B]On Error Go To 0[/B][/COLOR]
[COLOR=#0000ff][B]
End Sub[/B][/COLOR]

Thanks for the response.

If you do not mind my asking, do I input this at the beginning? or end of the script?

Do I put it in exactly as you have written? ( I am not familiar with sub)

Thanks

mrmmickle1 · Mar 23, 2016

Code:

[COLOR=#0000ff][B]Sub [/B][/COLOR][COLOR=#333333]ZillowXML()
[/COLOR][COLOR=#0000ff][B]On Error Resume Next[/B][/COLOR][COLOR=#333333]

[/COLOR][B][COLOR=#008000]'All exisiting code[/COLOR][/B][COLOR=#333333]

[/COLOR][COLOR=#0000ff][B]On Error GoTo 0[/B][/COLOR][COLOR=#333333]

[B]End Sub[/B][/COLOR]

On error resume next will suppress errors. YOu really only want to put in in an area that you expect an error to occur. Otherwise.... it could potentially mask other errors and prevent you prom being aware of them.... On Error GoTo 0 resets the error handling back to normal.

Another thing you could do is create an error handler that handles specific errors different ways...

Code:

[B][COLOR=#0000ff]On Error GoTo[/COLOR][/B] ErrHandler

[COLOR=#008000][B]'Your Code[/B][/COLOR]

[B][COLOR=#0000ff]On Error GoTo 0[/COLOR][/B]
[COLOR=#0000ff][B]Exit Sub[/B][/COLOR]
ErrHandler:

[COLOR=#0000ff][B]If [/B][/COLOR]Err.Number = X [B][COLOR=#0000ff]Then[/COLOR][/B]

'[B][COLOR=#008000]Do This[/COLOR][/B]

[B][COLOR=#0000ff]Else[/COLOR][/B]
[B][COLOR=#008000]
'Do Something else[/COLOR][/B]
[B][COLOR=#0000ff]
End If

End Sub[/COLOR][/B]

renewgeorgia · Mar 23, 2016

Hi,

I am having trouble determining where to put it and I am also getting a compiler error: Ambiguous name detected:zillowxml

It appears the main VBA is named zillowxml, does this need to be named differently?

I tried the :

Sub ZillowXML()
On Error Resume Next

'All exisiting code

On Error GoTo 0

End Sub

I tried this version since I want it to continue down the sheet. I do not care about error messages. At this point I just want it to move on to the next part.

Where in the long code do I need to place it? Should I rename it?

Thanks

mrmmickle1 · Mar 23, 2016

Code:

[COLOR=#0000ff][B]On Error Resume Next[/B][/COLOR]
[COLOR=#000000]
' Retrieve last Sold Price

Set xmllastSoldPrice = xmldoc.SelectSingleNode("//response/results/result/lastSoldPrice")
' Push data to preadsheet
If xmlHomeDetails Is Nothing Then
WS.Range(lastSoldPrice & i) = "None"
Else
WS.Range(lastSoldPrice & i) = xmllastSoldPrice.Text
WS.Range(lastSoldPrice & i).NumberFormat = "$#,##0_);($#,##0)"[/COLOR][COLOR=#ff0000]

[/COLOR][B][COLOR=#0000ff]On Error GoTo 0[/COLOR][/B][COLOR=#ff0000]
[/COLOR]

renewgeorgia · Mar 23, 2016

Sorry to keep bothering you, but I am sure I almost have it... just now getting an error about an else without an if. I did not get the code before, only after adding the new info.

What I have now is:

Code:

Sub ZillowXML()
' Miscrosoft XML v6.0 must be enabled from the VBA editor
' To enable, in macro editor go to Tools>References and check the box next to "Miscrosoft XML v6.0"


' Zillow API overview and signup
' http://www.zillow.com/howto/api/APIOverview.htm


' Zillow Web Service ID
ZWSID = "removed"


' Number of header columns
Headers = 2


' Columns containing addresses
Address = "E"
City = "F"
State = "G"
Zip = "H"


' Columns to return data
ErrorMessage = "J"
HomeDetails = "K"
Graphsanddata = "L"
Mapthishome = "M"
Comparables = "N"
latitude = "O"
longitude = "P"
ZAmount = "Q"
LastUpdate = "R"
zLow = "S"
zHigh = "T"
Rent = "U"
RentLastUpdate = "V"
RentLow = "W"
RentHigh = "X"
Region = "Y"
Overview = "Z"
FSBO = "AA"
forsale = "AB"
taxassessmentyear = "ac"
taxassessment = "ad"
yearbuilt = "ae"
finishedsqft = "af"
bedrooms = "ag"
bathrooms = "ah"
lastSoldDate = "ai"
lastSoldPrice = "aj"






' DO NOT EDIT BELOW THIS LINE UNLESS YOU KNOW WHAT YOU'RE GETTING INTO
'_______________________________________________________________________________


' Changes to make
' Automatically read address from a MsgBox dialog and transpose that list to a new sheet
' Automatically create new columns to put data in
' Convert new data range to a table and name it "Zillow"


Dim xmldoc As MSXML2.DOMDocument60
Dim xmlNodeList As MSXML2.IXMLDOMNodeList
Dim myNode As MSXML2.IXMLDOMNode
Dim WS As Worksheet: Set WS = ActiveSheet


' Seth column to display API URL for troubleshooting
'xmlURL = "E"


' Tell user the code is running
Application.StatusBar = "Starting search"


' Count Rows
LastRow = Cells(Rows.Count, 1).End(xlUp).Row


'___________________________________________________


' Begin looping through rows to find and insert data
    ' i = 3 leaves the first two row as column headers
    For i = Headers + 1 To LastRow


        'Clear previous data from cells
        WS.Range(ErrorMessage & i) = ""
        WS.Range(HomeDetails & i) = ""
        WS.Range(Graphsanddata & i) = ""
        WS.Range(Mapthishome & i) = ""
        WS.Range(Comparables & i) = ""
        WS.Range(latitude & i) = ""
        WS.Range(longitude & i) = ""
        WS.Range(ZAmount & i) = ""
        WS.Range(LastUpdate & i) = ""
        WS.Range(zLow & i) = ""
        WS.Range(zHigh & i) = ""
        WS.Range(Rent & i) = ""
        WS.Range(RentLastUpdate & i) = ""
        WS.Range(RentLow & i) = ""
        WS.Range(RentHigh & i) = ""
        WS.Range(Region & i) = ""
        WS.Range(Overview & i) = ""
        WS.Range(FSBO & i) = ""
        WS.Range(forsale & i) = ""
        WS.Range(taxassessmentyear & i) = ""
        WS.Range(taxassessment & i) = ""
        WS.Range(yearbuilt & i) = ""
        WS.Range(finishedsqft & i) = ""
        WS.Range(bedrooms & i) = ""
        WS.Range(bathrooms & i) = ""
        WS.Range(lastSoldDate & i) = ""
        WS.Range(lastSoldPrice & i) = ""


        ' Create Zillow API URL
        rowAddress = WS.Range(Replace(Address, " ", "+") & i)
        rowCity = WS.Range(City & i)
        rowState = WS.Range(State & i)
        rowZip = WS.Range(Zip & i)
        ' Comment out to use testing URL
        URL = "http://www.zillow.com/webservice/GetDeepSearchResults.htm?zws-id=" & ZWSID & "&address=" & rowAddress & "&citystatezip=" & rowCity & "%2C+" & rowState & "%2C+" & rowZip & "&rentzestimate=true"
        ' Local URL for testing
        'URL = ("C:\Users\John\Dropbox\Excel\ZillowXML.xml")


        ' Uncomment to Display API URL for troubleshooting
        'WS.Range(xmlURL & i) = ""
        'WS.Range(xmlURL & i).Formula = "=HYPERLINK(""" & URL & """,""API URL"")"


         ' Tell user what address is being searched for
         Application.StatusBar = "Retrieving: " & i & " of " & LastRow - Headers & ": " & rowAddress & ", " & rowCity & ", " & rowState


        'Open XML page
        Set xmldoc = New MSXML2.DOMDocument60
        xmldoc.async = False


        ' Check XML document is loaded
        If xmldoc.Load(URL) Then


            Set xmlMessage = xmldoc.SelectSingleNode("//message/text")
            Set xmlMessageCode = xmldoc.SelectSingleNode("//message/code")


            ' Check for an error message
            If xmlMessageCode.Text <> 0 Then


                ' Return error message
                WS.Range(ErrorMessage & i) = xmlMessage.Text


            Else
                ' Get XML data from Zillow
                Set xmlHomeDetails = xmldoc.SelectSingleNode("//response/results/result/links/homedetails")
                Set xmlGraphsAndData = xmldoc.SelectSingleNode("//response/results/result/links/graphsanddata")
                Set xmlComparables = xmldoc.SelectSingleNode("//response/results/result/links/comparables")
                Set xmlMapthishome = xmldoc.SelectSingleNode("//response/results/result/links/mapthishome")
                ' Push data to preadsheet
                If xmlHomeDetails Is Nothing Then
                    WS.Range(HomeDetails & i) = "No home details available"
                Else
                    WS.Range(HomeDetails & i).Formula = "=HYPERLINK(""" & xmlHomeDetails.Text & """,""Zillow Details"")"
                End If
                If xmlGraphsAndData Is Nothing Then
                    WS.Range(Graphsanddata & i) = "No graphs available"
                Else
                    WS.Range(Graphsanddata & i).Formula = "=HYPERLINK(""" & xmlGraphsAndData.Text & """,""Graphs & Data"")"
                End If
                If xmlComparables Is Nothing Then
                    WS.Range(Comparables & i) = "No comparables available"
                Else
                    WS.Range(Comparables & i).Formula = "=HYPERLINK(""" & xmlComparables.Text & """,""Zillow Comparables"")"
                End If
                If xmlMapthishome Is Nothing Then
                    WS.Range(Mapthishome & i) = "No map available"
                Else
                    WS.Range(Mapthishome & i).Formula = "=HYPERLINK(""" & xmlMapthishome.Text & """,""Zillow Map"")"
                End If


                ' Retrieve Lat & Long
                Set xmlLatitude = xmldoc.SelectSingleNode("//response/results/result/address/latitude")
                Set xmlLongitude = xmldoc.SelectSingleNode("//response/results/result/address/longitude")
                ' Push data to preadsheet
                WS.Range(latitude & i) = xmlLatitude.Text
                WS.Range(longitude & i) = xmlLongitude.Text


                ' Retrieve Zestimate
                Set xmlZAmount = xmldoc.SelectSingleNode("//response/results/result/zestimate/amount")
                Set xmlZLastUpdate = xmldoc.SelectSingleNode("//response/results/result/zestimate/last-updated")
                Set xmlZValLow = xmldoc.SelectSingleNode("//response/results/result/zestimate/valuationRange/low")
                Set xmlZValHigh = xmldoc.SelectSingleNode("//response/results/result/zestimate/valuationRange/high")
                ' Push data to preadsheet
                WS.Range(ZAmount & i) = xmlZAmount.Text
                WS.Range(ZAmount & i).NumberFormat = "$#,##0_);($#,##0)"
                WS.Range(LastUpdate & i) = xmlZLastUpdate.Text
                WS.Range(zLow & i) = xmlZValLow.Text
                WS.Range(zLow & i).NumberFormat = "$#,##0_);($#,##0)"
                WS.Range(zHigh & i) = xmlZValHigh.Text
                WS.Range(zHigh & i).NumberFormat = "$#,##0_);($#,##0)"


                ' Retrieve RentZestimate
                Set xmlRZAmount = xmldoc.SelectSingleNode("//response/results/result/rentzestimate/amount")
                Set xmlRZLastUpdate = xmldoc.SelectSingleNode("//response/results/result/rentzestimate/last-updated")
                Set xmlRZValLow = xmldoc.SelectSingleNode("//response/results/result/rentzestimate/valuationRange/low")
                Set xmlRZValHigh = xmldoc.SelectSingleNode("//response/results/result/rentzestimate/valuationRange/high")
                ' Push data to preadsheet
                WS.Range(Rent & i) = xmlRZAmount.Text
                WS.Range(Rent & i).NumberFormat = "$#,##0_);($#,##0)"
                WS.Range(RentLastUpdate & i) = xmlRZLastUpdate.Text
                WS.Range(RentLow & i) = xmlRZValLow.Text
                WS.Range(RentLow & i).NumberFormat = "$#,##0_);($#,##0)"
                WS.Range(RentHigh & i) = xmlRZValHigh.Text
                WS.Range(RentHigh & i).NumberFormat = "$#,##0_);($#,##0)"
                           
                                   
                ' Retrieve Year built
                Set xmlyearBuilt = xmldoc.SelectSingleNode("//response/results/result/yearBuilt")
                ' Push data to preadsheet
                WS.Range(yearbuilt & i) = xmlyearBuilt.Text
                                                
                ' Retrieve Finished Sq Feet
                Set xmlfinishedSqFt = xmldoc.SelectSingleNode("//response/results/result/finishedSqFt")
                ' Push data to preadsheet
                WS.Range(finishedsqft & i) = xmlfinishedSqFt.Text
                
                ' Retrieve bedrooms
                Set xmlbedrooms = xmldoc.SelectSingleNode("//response/results/result/bedrooms")
                ' Push data to preadsheet
                WS.Range(bedrooms & i) = xmlbedrooms.Text
                
                ' Retrieve bathrooms
                Set xmlbathrooms = xmldoc.SelectSingleNode("//response/results/result/bathrooms")
                ' Push data to preadsheet
                WS.Range(bathrooms & i) = xmlbathrooms.Text
                
                On Error Resume Next


                ' Retrieve last Sold Price


                Set xmllastSoldPrice = xmldoc.SelectSingleNode("//response/results/result/lastSoldPrice")
                ' Push data to preadsheet
                If xmlHomeDetails Is Nothing Then
                WS.Range(lastSoldPrice & i) = "None"
                Else
                WS.Range(lastSoldPrice & i) = xmllastSoldPrice.Text
                WS.Range(lastSoldPrice & i).NumberFormat = "$#,##0_);($#,##0)"


                On Error GoTo 0


          
                
            End If


       ' Document failed to load statement
       
[COLOR=#ff0000]       Else[/COLOR]
       WS.Range(ErrorMessage & i) = "The document failed to load. Check your internet connection."


       End If


    ' Loop to top for next row
    Next i


' Tell user the search is complete
Application.StatusBar = "Search complete!"


End Sub

The Else is red is the culprit according to the editor.

Again, if I go back to old code, it is fine, but the new if else somehow messes with this else.

Any suggestions?
And again, thank you... sorry for all the questions.

mrmmickle1 · Mar 23, 2016

Try this:

Code:

         [COLOR=#0000ff][B]       On Error Resume Next[/B][/COLOR]

                [COLOR=#0000ff][B]Set [/B][/COLOR]xmllastSoldPrice = xmldoc.SelectSingleNode("//response/results/result/lastSoldPrice")
       
               [COLOR=#0000ff][B] If [/B][/COLOR]xmlHomeDetails [COLOR=#0000ff][B]Is Nothing[/B][/COLOR] [B][COLOR=#0000ff]Then[/COLOR][/B]
                     WS.Range(lastSoldPrice & i) = "None"
             [COLOR=#0000ff][B]   Else[/B][/COLOR]
                    WS.Range(lastSoldPrice & i) = xmllastSoldPrice.Text
                    WS.Range(lastSoldPrice & i).NumberFormat = "$#,##0_);($#,##0)"
              [B][COLOR=#0000ff]  End If
                
                On Error GoTo 0[/COLOR][/B]

renewgeorgia · Mar 23, 2016

Thanks, it did get past the Else issue, but the original issue is still there. It still, at the same place, says "run time error 91"

It happens after 8 properties work fine, and always gets stuck on the 9th. If I delete the ninth, and run it from the start, it continues on past the 9th.

When I look at the source url that it is pulling for the 9th, it is apparent the issue is that there is no last sold price for that property and that is why I am getting the Run time error 91.

Any suggestions?

Maybe should I send you the excel doc by email?

I do not want to post it all here since it has my zillow password in it.

run time error 91 object variable or with block not set - reference is not found in looping program

renewgeorgia

New Member

Excel Facts

mrmmickle1

Well-known Member

renewgeorgia

New Member

mrmmickle1

Well-known Member

renewgeorgia

New Member

mrmmickle1

Well-known Member

renewgeorgia

New Member

mrmmickle1

Well-known Member

renewgeorgia

New Member

Similar threads

Forum statistics

Share this page

run time error 91 object variable or with block not set - reference is not found in looping program

New Member

Excel Facts

Well-known Member

New Member

Well-known Member

New Member

Well-known Member

New Member

Well-known Member

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock