Almost there: Loop to search website

Hello all I am working on a macro that will open a webpage and search it. The macro will loop through a list of values and search the site for specific information. If the information exist on the site then the macro will enter "Y" in the corresponding cell of column F, if the website does not have the information then the macro will enter "N". Also I will need to skip values that have a "Y" in its corresponding row. This is needed because information on the website is updated daily, so this macro will be run periodically to capture the new information. I have a good portion of the code completed and what I have works great. I've tested the loop and it will search 30K+ values with no errors. The would like the array to be adapted to skip the listed values in the array, they don't need to be searched. I need help adding the next operation of the code. The code is below. Any help is greatly appreciated.

Code:

---

Sub SearchESN()
Dim I As Long
Dim IE As Object
Dim objElement As Object
Dim objCollection As Object

Dim MyFolder As String
Dim MyFile As String

Dim MyVals As Variant
MyVals = Array("*472908*", "*471905*", "*471914*", "*471935*", "*471917*", "*471920*", "*471933*", "*471932*", "*471934*") 'Enter all the values to search for


  ' Create InternetExplorer Object
Set IE = CreateObject("InternetExplorer.Application")
  ' You can uncoment Next line To see form results
IE.Visible = True
  ' Go to website
IE.Navigate "url goes here"
  ' Statusbar
Application.StatusBar = "THIS PAGE is loading. Please wait..."
  ' Wait while IE loading...
  ' Do While IE.Busy
  ' Application.Wait DateAdd("s", 1, Now)
  ' Loop
  ' IE.Visible = True
  ' Clean up
Set IE = Nothing
Set objElement = Nothing
Set objCollection = Nothing

Application.StatusBar = ""

MyFolder = "file location goes here"
  MyFile = Dir(MyFolder & "\*Data Loop.xls")

  Workbooks.Open Filename:=MyFolder & "\" & MyFile

  MyFile = Dir

  Application.Goto Range("A2"), False

Do Until IsEmpty(ActiveCell)

For Each esn In Selection
    For I = LBound(MyVals) To UBound(MyVals)
          If esn.Value Like MyVals(I) Then
          esn.Interior.ColorIndex = 6 'yellow
    Exit For
    End If
    Next I
    Next esn

    ActiveCell.Offset(1, 0).Select
Loop
    Application.Goto Range("A2"), False
End Sub

---

Attachment 11875

Attachment 11876

try like

Code:

---

For Each esn In range("a:a")
  if isempty(esn) then exit for
  if esn.offset(, 5) = "N" then
      ' get info from wedpage
  end if
Next esn

---

Why not using a webquery ?

Quote:

---

Originally Posted by snb

Why not using a webquery ?

---

I was not sure that query would pull the data and input to the sheet like a macro. I will give it a try. Thanks.

The query is suffucient to get the required information. Each esn had the be searched and then the page checked for the need data. If the data exist then put a Y in the 6th cell of the same row of currently search esn.

Quote:

---

Originally Posted by dmon99

The query is suffucient to get the required information. Each esn had the be searched and then the page checked for the need data. If the data exist then put a Y in the 6th cell of the same row of currently search esn.

---

I meant to say a webquery is insufficient to get the required information.