Originally Posted by
Rawland Hustle
I changed the code (marked red below) but that didn't work. What am I doing wrong?
In what way didn't it work? You haven't said. Any error messages? As already mentioned, use the macro recorder whilst doing a manual Find in the Excel GUI and incorporate the code generated (which will need editing to remove Select, Activate) and assign the range returned by Find to a range variable (as you do for the code in red).
The code in red is using the code name for the sheet (Sheet1.Range), which may not be correct for your specific situation. And is your web query on Sheet1 (code name). Use sheet names instead like Sheets("Sheet1").Range to avoid confusion with code names. Also, shouldn't the Find be done inside the loop after the Refresh, if the web query worked?
Also, if your range Sheet2.Range("A2:A29") contains complete URLs, changing the query table connection parameter for each URL in the loop should just be:
Remember, the code you linked to is specific to the particular request posted so probably won't work exactly for your situation without modification. I would also comment out the On Error lines whilst testing the code so that errors are not suppressed - in this case it should only be used to trap possible errors from the web query.
Bookmarks