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
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top