So I thought I had gotten close to this, the code goes through a column of unrefined addresses, generates a google search url in another, and then pulls the address google has an writes it to the 3rd column.
Before I could only get it to work if I specified the cell location, I want it to work by going down every URL in a column and writing the address one by one.
So i thought "let's put the getElementsByClassName in another loop"
Needless to say it doesn't work, I get automation error on the IE.Navigate line.
Private Sub CommandButton1_Click()
Dim IE As Object
' Create InternetExplorer Object
Set IE = CreateObject("InternetExplorer.Application")
' You can uncoment Next line To see form results
IE.Visible = False
'START LOOP
' URL to get data from
For r = 2 To 3
IE.navigate Sheets("Sheet1").Cells(r, "A").Value
Do While IE.Busy
Application.Wait DateAdd("s", 1, Now)
Loop
Dim dd As String, c
' Runs loops to look for the value within the classname, if classname alternates it will change the element, if null it will exit.
For Each c In Array("vk_sh vk_bk", "_Xbe")
On Error Resume Next
dd = IE.document.getElementsByClassName(c)(0).innerText
On Error GoTo 0
If Len(dd) > 0 Then Exit For
' Gives a confirmation message and writes the result to a cell
Cells(r, "C").Value = dd
Next
Next r
' /LOOP
' Show IE
IE.Visible = False
' Clean up
Set IE = Nothing
End Sub
Notes:
From 2 to 3 is correct, the list is pretty long so i want to test it out with just 2 addresses first.
Can someone more proficient in VBA tell me where I am going wrong?
UPDATED: Changed range to cells