Pull Multiple values from a website

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
 

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Gary Abley

New Member
Joined
Sep 24, 2019
Messages
6
The information in B1:B30 will be vehicle Registartion numbers, I then want the MOT expiry date to poulate in the next column.

Ie. B1 = NA51XKN, and return the date in C1, B2 would then be a different Reistration etc

Hope that makes some or sort of sense.

Thanks Gary
 

Gary Abley

New Member
Joined
Sep 24, 2019
Messages
6
Sorry to be a pain, but just wondering if anybody has got any further ideas on how i can complete the above
 

jamescooper

Well-known Member
Joined
Sep 8, 2014
Messages
771
I am getting error 1004 on the line:

Code:
If Target.Row = Range("RegNo").Row And _
Target.Column = Range("RegNo").Offset(1, 0).Column Then
 

Gary Abley

New Member
Joined
Sep 24, 2019
Messages
6
I am getting error 1004 on the line:

Code:
If Target.Row = Range("RegNo").Row And _
Target.Column = Range("RegNo").Offset(1, 0).Column Then
Hi James,

This might be because I created a Name Range in cell "B1"

I have also noticed I didnt send the correct website link, the correct link is below.

IE.navigate "https://www.suttonparkgroup.co.uk/feedsin/gov/mot-check.php?reg=" & Range("RegNo").Value

Would it be easier if i attached a copy of the spreadheet?

Many Thanks
 

Gary Abley

New Member
Joined
Sep 24, 2019
Messages
6
After Looking into this further it the name range that is causing the issue, how do I change the code to run through multiple cells rather than a name range.

Your help would be really appreciated as I am struggling to resolve

thanks Gary
 

Watch MrExcel Video

Forum statistics

Threads
1,099,575
Messages
5,469,477
Members
406,656
Latest member
Kriscrawford76

This Week's Hot Topics

Top