Sleeper: Searching Web Data

[BexleyManor]

Hi all,

Having done most of the hard work on this one, I'm having a mental block as to the final solution??!!

From the code you will see that I can pick out individual data from the webpage, however I would like the code to pick out all instances or the requested data.

Private Sub cmdGETproxy_Click()
 Dim Wkb As Workbook, Rng As Range
Set Wkb = Workbooks.Open("http://aliveproxy.com/") 
Set Rng = Wkb.Worksheets(1).Cells.Find("High Anonymity") 
MsgBox "High Anonymity: " & Rng.Offset(0, -2).Value & Chr(10) & "SSL/HTTPS: " & Rng.Offset(0, 1).Value & Chr(10) & "Host Country: " & Rng.Offset(0, -1).Value
Wkb.Close   
 end sub

Any suggestions kind folks??

[mvidas]

Hi Bexley,

Try the following. It sets another range variable to the first instance of "High Anonymity", then loops through all other cells that contain that until it reaches the first again. Let me know if you have any questions!

Matt

Private Sub cmdGETproxy_Click()
    Dim Wkb As Workbook, Rng As Range, Rng1 As Range
Set Wkb = Workbooks.Open("http://aliveproxy.com/")
Set Rng = Wkb.Worksheets(1).Cells.Find("High Anonymity")
If Not Rng Is Nothing Then
     Set Rng1 = Rng
     Msgbox "High Anonymity: " & Rng.Offset(0, -2).Value & Chr(10) & "SSL/HTTPS: " & Rng.Offset(0, 1).Value & Chr(10) & "Host Country: " & Rng.Offset(0, -1).Value
     Set Rng = Wkb.Worksheets(1).Cells.FindNext(Rng)
     Do Until Rng1.Address = Rng.Address
      Msgbox "High Anonymity: " & Rng.Offset(0, -2).Value & Chr(10) & "SSL/HTTPS: " & Rng.Offset(0, 1).Value & Chr(10) & "Host Country: " & Rng.Offset(0, -1).Value
      Set Rng = Wkb.Worksheets(1).Cells.FindNext(Rng)
     Loop
    Else
     Msgbox "None found."
    End If
Wkb.Close
End Sub

Matt

[BexleyManor]

Hi Matt,

Many thanks for the input, much appreciated.

All seems to work ok until it gets to the end of the loop then I get an application error.

Is there any way I can have the code read the column of data top to bottom then exit when it reaches the end??

Thanks.

[mvidas]

Hi Matt,
Many thanks for the input, much appreciated.
All seems to work ok until it gets to the end of the loop then I get an application error.

Hmm, not sure why you'd get an application error, what line is it erroring at?

Is there any way I can have the code read the column of data top to bottom then exit when it reaches the end??

To do that you'd have to loop through each cell, which would take a longer time. Thats why I have it the way it does, so it compares the found cell to the first found cell (and when they match, the region has been completely searched). Try this variation, it may prove more useful in reality for you. It takes all findings and adds them to a range variable 'Found', and then loops through all the findings at the end. This way if you're going to do something with them other than msgbox, it may be a little easier for you. Also you can specify the column to search through. Here's the revision:

Private Sub cmdGETproxy_Click()
    Dim Wkb As Workbook, Rng As Range, Col As Range, Found As Range
Set Wkb = Workbooks.Open("http://aliveproxy.com/")
    Set Col = Wkb.Sheets(1).Columns("C") 'Column to search through
Set Col = Intersect(Col, Col.Parent.UsedRange) 'trim column to used area
    Set Rng = Col.Find("High Anonymity", Col.Cells(Col.Cells.Count))
    If Not Rng Is Nothing Then
        Set Found = Rng
        Set Rng = Col.FindNext(Rng)
        Do Until Rng.Address = Found.Cells(1).Address
            Set Found = Union(Found, Rng)
            Set Rng = Col.FindNext(Rng)
        Loop
    Else
        Msgbox "None found."
    End If
    For Each Rng In Found.Cells
     Msgbox "High Anonymity: " & Rng.Offset(0, -2).Value & Chr(10) & "SSL/HTTPS: " & Rng.Offset(0, 1).Value & Chr(10) & "Host Country: " & Rng.Offset(0, -1).Value
    Next Rng
Wkb.Close
End Sub

Of course let me know if its still not behaving
Matt