Pull Multiple values from a website

[Gary Abley]

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

 

[jamescooper]

What is in cell B1 to B30?

 

[Gary Abley]

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]

Sorry to be a pain, but just wondering if anybody has got any further ideas on how i can complete the above

 

[jamescooper]

I am getting error 1004 on the line:

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

 

[Gary Abley]

I am getting error 1004 on the line:

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]

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