Gary Abley
New Member
- Joined
- Sep 24, 2019
- Messages
- 6
Good Afternoon,
I am trying to look up multiple values against a website and return the value from the website in the next cell.
So if I have got data in B1:B30, I want excel to go to a website look at the data in column B and return the result in Column C.
The code below works perfect when there is only data in cell B1.
Any help would be appreciated.
Regards
Gary
My Code is:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row = Range("RegNo").Row And _
Target.Column = Range("RegNo").Offset(1, 0).Column Then
Dim IE As New InternetExplorer
'IE.Visible = True
IE.navigate "https://www.suttonparkgroup.co.uk=" & Range("RegNo").Value
Do
DoEvents
Loop Until IE.readyState = READYSTATE_COMPLETE
Dim Doc As HTMLDocument
Set Doc = IE.document
Dim smotexpiry As String
smotexpiry = Doc.getElementsByTagName("motexpiry")(0).innerText
IE.Quit
Range("Date").Value = smotexpiry
End If
End Sub
I am trying to look up multiple values against a website and return the value from the website in the next cell.
So if I have got data in B1:B30, I want excel to go to a website look at the data in column B and return the result in Column C.
The code below works perfect when there is only data in cell B1.
Any help would be appreciated.
Regards
Gary
My Code is:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row = Range("RegNo").Row And _
Target.Column = Range("RegNo").Offset(1, 0).Column Then
Dim IE As New InternetExplorer
'IE.Visible = True
IE.navigate "https://www.suttonparkgroup.co.uk=" & Range("RegNo").Value
Do
DoEvents
Loop Until IE.readyState = READYSTATE_COMPLETE
Dim Doc As HTMLDocument
Set Doc = IE.document
Dim smotexpiry As String
smotexpiry = Doc.getElementsByTagName("motexpiry")(0).innerText
IE.Quit
Range("Date").Value = smotexpiry
End If
End Sub