loop through column of cells checking car reg in website, then move to next one after dates put in next column

Question

I need some help, not mentally haha that's a different problem, this problem I have put tens of hours in and literally cant find what type of loop or next will help me loop through my code, I have done a lot of research on do until loops which just keep looping through the first car registration endlessly or next loops which will work down the car registration column but wont actually use the next car reg. What I want my sheet to do is take a car registration and check its tax and mot date and then take that mot and tax date or lack of date and put it in the column next to the car registration, once done for it to do the same with the next car reg (which is currently in column x). also if there is an error or no date for it to just put no date in the cell next to the car reg and still move on to the next car reg to do the same check until the a cell in column x is blank/ empty. I had help from some people on here and got some working code already for the other bits, I want to keep this code as it is as much as possible, I am new to all of this so need to keep consistency for my self while I learn, been doing it 3 weeks now so not long and anything new will kick me off the tracks and complicate things for me.

to help the first car reg will be in column x row 3, and the next car reg after will be in column x row 4 and so on. the dates for the relative car reg I am putting in the column next to the car reg it relates to.

i am a fraud investigator and think the way forward is software driven by fraud experts, this is for excel vba (as most companies use excel for management information reports and data analytics), its a project I am doing for my self to help me progress at work and prevent fraud so your help here personally will help me down the line. I have appreciated the help so far from this site so thank you to those who helped me so far.

Sub TAXandMOTcheck()

'dimension (declare or set aside memory for) our variables
    Dim objIE As InternetExplorer 'special object variable representing the IE browser

'''''''''''''''''''''''''''''''''''''''''''

Dim valueofInterest As Integer

Range("x3").Select

 Do Until ActiveCell.Empty

End If
ActiveCell.Offset(1, 0).Select



'''''''''''''''''''''''''''''''''''''''''''
'''''''''''''''''''''''''''''''''''''''''''
'''''''''''''''''''''''''''''''''''''''''''
' START OF THE CODE THAT WORKS, PLEASE DONT CHANGE IT IF POSSIBLE '''''''''''''''''''''''''''''''''''''''''''

'OPEN INTERNET

'initiating a new instance of Internet Explorer and asigning it to objIE
    Set objIE = New InternetExplorer

'wait # seconds
    Application.Wait Now + TimeValue("00:00:05")

'wait here a few seconds while the browser is busy
    Do While objIE.Busy = True Or objIE.ReadyState <> 4: DoEvents: Loop

'''''''''''''''''''''''''''''''''''''''''''

'OPEN TAX/ MOT PAGE

'navigate IE to this web page (a pretty neat search engine really)
    objIE.Navigate "https://vehicleenquiry.service.gov.uk/"

'make IE browser visible (False would allow IE to run in the background)
    objIE.Visible = True

'wait # seconds
    Application.Wait Now + TimeValue("00:00:05")

'wait here a few seconds while the browser is busy
    Do While objIE.Busy = True Or objIE.ReadyState <> 4: DoEvents: Loop

'''''''''''''''''''''''''''''''''''''''''''

'ENTER DETAILS IN TO PAGE

'in the search box put cell "x3" value
    objIE.Document.getElementById("Vrm").Value = _
    Sheets("INPUT DATA").Range("x3").Value

'wait here a few seconds while the browser is busy
    Do While objIE.Busy = True Or objIE.ReadyState <> 4: DoEvents: Loop

'wait # seconds
    Application.Wait Now + TimeValue("00:00:03")

'wait here a few seconds while the browser is busy
    Do While objIE.Busy = True Or objIE.ReadyState <> 4: DoEvents: Loop

'''''''''''''''''''''''''''''''''''''''''''''''''''''

'click the 'Continue' button
    objIE.Document.getElementsByClassName("button")(0).Click

'wait here a few seconds while the browser is busy
    Do While objIE.Busy = True Or objIE.ReadyState <> 4: DoEvents: Loop

'wait # seconds
    Application.Wait Now + TimeValue("00:00:03")

'wait here a few seconds while the browser is busy
    Do While objIE.Busy = True Or objIE.ReadyState <> 4: DoEvents: Loop

'''''''''''''''''''''''''''''''''''''''''''''''''''''

'click the 'Yes' button
    objIE.Document.getElementById("Correct_True").Click

'wait here a few seconds while the browser is busy
    Do While objIE.Busy = True Or objIE.ReadyState <> 4: DoEvents: Loop

'''''''''''''''''''''''''''''''''''''''''''''''''''''

'click the 'Continue' button
    objIE.Document.getElementsByClassName("button")(0).Click

'wait here a few seconds while the browser is busy
    Do While objIE.Busy = True Or objIE.ReadyState <> 4: DoEvents: Loop

'wait # seconds
    Application.Wait Now + TimeValue("00:00:03")

'wait here a few seconds while the browser is busy
    Do While objIE.Busy = True Or objIE.ReadyState <> 4: DoEvents: Loop

'''''''''''''''''''''''''''''''''''''''''''''''''''''

'TAX EXPIRY DATE:

'get data inside element
    TaxExpiryDate = objIE.Document.getElementsByClassName("status-bar")(0).getElementsByTagName("strong")(0).innerText

'split the date from the words
    TaxExpiryDate = Split(TaxExpiryDate, vbNewLine)(1)

'add tax date to sheet
    Range("y3").Value = TaxExpiryDate

'''''''''''''''''''''''''''''''''''''''''''''''''''''

'MOT EXPIRY DATE:

'get data inside element
    MotExpiryDate = objIE.Document.getElementsByClassName("status-bar")(0).getElementsByTagName("strong")(1).innerText

'split the date from the words
    MotExpiryDate = Split(MotExpiryDate, vbNewLine)(1)

'add mot date to sheet
    Range("z3").Value = MotExpiryDate

'''''''''''''''''''''''''''''''''''''''''''

'close the browser
    objIE.Quit

'''''''''''''''''''''''''''''''''''''''''''
'''''''''''''''''''''''''''''''''''''''''''
'''''''''''''''''''''''''''''''''''''''''''
' END OF THE ABOVE CODE THAT WORKS, PLEASE DONT CHANGE THIS IF POSSIBLE '''''''''''''''''''''''''''''''''''''''''''




Loop


'''''''''''''''''''''''''''''''''''''''''''
'''''''''''''''''''''''''''''''''''''''''''
' THE BELOW CODE WORKS PLEASE DONT CHANGE IT IF POSSIBLE '''''''''''''''''''''''''''''''''''''''''''

'message boxes if true or false

    If found = True Then
    MsgBox "Value found in cell" & ActiveCell.Address
    Else
    MsgBox "Be Happy and Smile, its the end of the search. Just keep swimming, just keep swimming..."
    End If

'''''''''''''''''''''''''''''''''''''''''''

'exit our SearchBot subroutine and start new row for new website data
    End Sub

Please seek help at https://stackoverflow.com/help/how-to-ask and completely shorten your question to its main problem. Paste in the problem code Fragment. Include some of your test data, and state the expected results. — donPablo, Mar 28 '19 at 01:50
Hi donpablo, I believe I have done all that as much as I can, is no one able to help me with the details I submitted, I gave alot to help. — Gary Arg, Mar 28 '19 at 02:00
your code, as is, shouldn't run. You have a stranded End If just sitting there at the top with no start If. It is worth reading why using .Select is rarely a good idea [here](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). You also don't won't to be creating and destroying IE instances in a loop when you can simply work with the same instance throughout. Not sure what valueofInterest is as well. Can it be removed? — QHarr, Mar 28 '19 at 03:21
You also have a page load wait at the top before you have navigated to a page and multiple repeat page load waits with hardcoded waits -too many IMO. Sorry for the large number of comments. — QHarr, Mar 28 '19 at 03:24
I'm not going to post essentially the same answer again. I know you want to preserve as much of your code as possible so I will just leave a link here to a slightly updated version of my answer and suggest you review the structure of the loop, how waits are set up and the fact IE is only created once. https://pastebin.com/TW9hdWw0 — QHarr, Mar 28 '19 at 03:35
Hi qharr, hope you are well. Thank you for your time in this. I willlooom at your updates and your helpful insight and see where I end up. Have a nice day — Gary Arg, Mar 28 '19 at 08:16
Hi. here is another link showing you how to use a For Each over the range of cells containing your lookup values. You might find that easier. Ignore all my other code and just focus on how For Each rng In inputValues works and how rng is the current cell in the loop down the column X and the current row number comes from rng.row https://pastebin.com/dzz7Jvgj — QHarr, Mar 28 '19 at 21:38
hi qharr, thank you I will look in to that now, I have been looing up loops, arrays and that more, so much to consider, I am feeling the base of my knowledge is weak and maybe this is too far advanced for me. — Gary Arg, Mar 28 '19 at 22:44

loop through column of cells checking car reg in website, then move to next one after dates put in next column

0 Answers0