VB Error 91

[levdavs]

I have the following script and it keeps giving error 91 at line 33. I don't have much experience writing macros so any help would be greatly appreciated.

Sub GetPropertyInfoByPIN()

Dim IE As Object
Dim url As String
Dim pin As String
Dim propertyOwner As String
Dim mailingAddress As String
Dim citystatezip As String
Dim html As Object
Dim elem As Object
Dim inpParid As Object
Dim btAgree As Object
Dim btSearch As Object
Dim lastRow As Long
Dim i As Long

' Define the URL of the county assessor's property search page
url = "Greene County Property Information" ' Replace with the actual URL of the website

' Create an instance of Internet Explorer (IE)
Set IE = CreateObject("InternetExplorer.Application")
IE.Visible = False ' Set to False to hide the IE window while processing

' Open the URL
IE.Navigate url
Do While IE.Busy Or IE.ReadyState <> 4
DoEvents
Loop
Application.Wait Now + TimeValue("0:00:02") ' Add some wait time to allow dynamic content to load

' Agree to the disclaimer
Set elem = html.getElementByID("btAgree")
html.getElementByID("btAgree").Click

' Loop through the list of PINs in Excel (assuming starting at cell A2)
lastRow = Cells(Rows.Count, 1).End(xlUp).Row

For i = 2 To lastRow
pin = Cells(i, 1).Value ' Get PIN from column A

' Find the input field for the PIN (adjust based on the webpage)
Set html = IE.document

' Attempt to set the input field value
Set elem = html.getElementByID("inpParid") ' Change "inpParid" to the actual ID of the input field for PIN
If Not elem Is Nothing Then
elem.Value = pin
' Submit the form (adjust if needed)
html.getElementByID("btSearch").Click ' Change "searchButton" to the actual button ID

' Wait for the results page to load
Do While IE.Busy Or IE.ReadyState <> 4
DoEvents
Loop

' Extract the property owner name (adjust based on the website structure)
Set elem = html.getElementByID("Owner") ' Replace with the correct ID of the property owner name element
If Not elem Is Nothing Then
propertyOwner = elem.innerText
Else
propertyOwner = "Not Found"
End If

' Extract the mailing address (adjust based on the website structure)
Set elem = html.getElementByID("Address") ' Replace with the correct ID of the mailing address element
If Not elem Is Nothing Then
mailingAddress = elem.innerText
Else
mailingAddress = "Not Found"
End If

' Extract the city, state, and zip (adjust based on the website structure)
Set elem = html.getElementByID("City, State, Zip") ' Replace with the correct ID of the city, state, zip element
If Not elem Is Nothing Then
citystatezip = elem.innerText
Else
citystatezip = "Not Found"
End If

' Output the result back into Excel (columns B and C for owner and mailing address)
Cells(i, 2).Value = propertyOwner
Cells(i, 3).Value = mailingAddress
Cells(i, 4).Value = citystatezip

' Pause before next request to avoid overloading the server
Application.Wait (Now + TimeValue("0:00:02"))
Else
' PIN input field not found
MsgBox "PIN input field not found on page!"
Exit Sub
End If
Next i

' Close Internet Explorer after completing the task
IE.Quit
Set IE = Nothing

MsgBox "Property information retrieval completed!"

End Sub

 

[Micron]

I have some suggestions for your posts going forward. You can take them to heart or not - up to you, but I think they'd improve your chances of getting answers sooner:
- don't make us look up the error number as there are literally thousands of them. I suspect 91 is Object Variable or With block variable not set, but not sure.
- post code within code tags (use vba button on posting toolbar). I'm an old curmudgeon and usually won't read code like that any more. I'm probably not alone.
- use comments to identify the line, or post the actual line by itself - anything but give the line number.

VBA editor doesn't support line numbers anymore, if it ever did. I posted your code into a new module and the toolbar shows that line 33 is a comment.

That may be because you posted the wrong number, or if you experiment, you'll notice that it all depends on how much code is in other procedures (subs or functions) above the problem code.

 

[Peter_SSs]

Welcome to the MrExcel board!

To demonstrate what @Micron has said about posting vba code, I have posted your code again below. You can see that if you use the available code tags, it makes your code much easier to read/debug & copy. My signature block at the bottom of this post has more details.

Sub GetPropertyInfoByPIN()

    Dim IE As Object
    Dim url As String
    Dim pin As String
    Dim propertyOwner As String
    Dim mailingAddress As String
    Dim citystatezip As String
    Dim html As Object
    Dim elem As Object
    Dim inpParid As Object
    Dim btAgree As Object
    Dim btSearch As Object
    Dim lastRow As Long
    Dim i As Long

    ' Define the URL of the county assessor's property search page
    url = "[URL='https://www.greenecountyassessor.org/assessor/search/commonsearch.aspx?mode=realprop']Greene County Property Information[/URL]"  ' Replace with the actual URL of the website

    ' Create an instance of Internet Explorer (IE)
    Set IE = CreateObject("InternetExplorer.Application")
    IE.Visible = False ' Set to False to hide the IE window while processing

    ' Open the URL
    IE.Navigate url
    Do While IE.Busy Or IE.ReadyState <> 4
        DoEvents
    Loop
    Application.Wait Now + TimeValue("0:00:02") ' Add some wait time to allow dynamic content to load
   
    ' Agree to the disclaimer
    Set elem = html.getElementByID("btAgree")
    html.getElementByID("btAgree").Click

    ' Loop through the list of PINs in Excel (assuming starting at cell A2)
    lastRow = Cells(Rows.Count, 1).End(xlUp).Row
   
    For i = 2 To lastRow
        pin = Cells(i, 1).Value  ' Get PIN from column A
       
        ' Find the input field for the PIN (adjust based on the webpage)
        Set html = IE.document

        ' Attempt to set the input field value
        Set elem = html.getElementByID("inpParid")  ' Change "inpParid" to the actual ID of the input field for PIN
        If Not elem Is Nothing Then
            elem.Value = pin
            ' Submit the form (adjust if needed)
            html.getElementByID("btSearch").Click ' Change "searchButton" to the actual button ID
           
            ' Wait for the results page to load
            Do While IE.Busy Or IE.ReadyState <> 4
                DoEvents
            Loop
           
            ' Extract the property owner name (adjust based on the website structure)
            Set elem = html.getElementByID("Owner")  ' Replace with the correct ID of the property owner name element
            If Not elem Is Nothing Then
                propertyOwner = elem.innerText
            Else
                propertyOwner = "Not Found"
            End If
           
            ' Extract the mailing address (adjust based on the website structure)
            Set elem = html.getElementByID("Address")  ' Replace with the correct ID of the mailing address element
            If Not elem Is Nothing Then
                mailingAddress = elem.innerText
            Else
                mailingAddress = "Not Found"
            End If
           
            ' Extract the city, state, and zip (adjust based on the website structure)
            Set elem = html.getElementByID("City, State, Zip")  ' Replace with the correct ID of the city, state, zip element
            If Not elem Is Nothing Then
                citystatezip = elem.innerText
            Else
                citystatezip = "Not Found"
            End If
           
            ' Output the result back into Excel (columns B and C for owner and mailing address)
            Cells(i, 2).Value = propertyOwner
            Cells(i, 3).Value = mailingAddress
            Cells(i, 4).Value = citystatezip
           
            ' Pause before next request to avoid overloading the server
            Application.Wait (Now + TimeValue("0:00:02"))
        Else
            ' PIN input field not found
            MsgBox "PIN input field not found on page!"
            Exit Sub
        End If
    Next i

    ' Close Internet Explorer after completing the task
    IE.Quit
    Set IE = Nothing

    MsgBox "Property information retrieval completed!"

End Sub