Almost there: Loop to search website

[dmon99]

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.

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

excel.jpg

macro pic.jpg

[westconn1]

try like

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

[snb]

Why not using a webquery ?

[dmon99]

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.

[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.

[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.