Public Sub ExtractNameFromWeb2()
Dim htmlDoc As New HTMLDocument
Dim htmlElements As IHTMLElementCollection
Dim url As String
Dim i As Long, n As Long
Dim parts As Variant, city As String, p As Long
Dim xmlhttp As Object
Set xmlhttp = CreateObject("MSXML2.serverXMLHTTP")
'Loop through all rows in column F and extract the parcel number from the URL
For i = 2 To Cells(Rows.Count, 7).End(xlUp).Row
url = "https://tax.norrycopa.net/taxclaim/trirsp2pp.asp?parcel=" & Cells(i, 7).Value
'Send a request to the web server and receive the HTML response
Debug.Print "Request URL: " & url
xmlhttp.Open "GET", url, False
xmlhttp.send
htmlDoc.body.innerHTML = xmlhttp.responseText
'Debug.Print "HTML Response:"
'Debug.Print htmlDoc.body.innerHTML
Set htmlElements = htmlDoc.getElementsByTagName("td")
For n = 0 To htmlElements.Length - 1
If htmlElements(n).innerText = "NAME: " Then
Range("B" & i).Value = htmlElements(n + 1).innerText
Debug.Print "Name: " & Range("B" & i).Value
ElseIf htmlElements(n).innerText = "ADDRESS: " Then
Range("C" & i).Value = htmlElements(n + 1).innerText
Debug.Print "Address: " & Range("C" & i).Value
If htmlElements(n + 2).innerText = "" Then
Debug.Print htmlElements(n + 3).innerText
parts = Split(Application.Trim(htmlElements(n + 3).innerText), " ")
city = ""
For p = 0 To UBound(parts) - 2
city = city & parts(p) & " "
Next
Range("D" & i).Value = Left(city, Len(city) - 1)
Range("E" & i).Value = parts(UBound(parts) - 1)
Range("F" & i).Value = parts(UBound(parts))
End If
End If
Next
Next
End Sub