Hyperlink Parsing Starting at "wrong" row

I have a routine that cycles through a list of web site addresses on one worksheet, imports the data in another worksheet and then cleans up the data. It uses a for...next loop to process each hyperlink. Unfortunately, the first hyperlink processed is always the second hyperlink in the list (which is in row 2). How can I force the loop to start with the first hyperlink?

Here is the essence of the code:

Please Login or Register  to view this content.

Hello Peaceful4getFul,

Your loop isn't the problem. The For Each ..Next loops through the Hyperlinks collection objectstarting with the first object in the collection. A For... Next loop would be suspect because you specify a starting index. I would suggest you look at the GetNewTargetCell function, and check you are getting the right sTargetCell returned.

Siincerely,
Leith Ross

Leith, the GetNewTargetCell function determines cell to import the data to, rather than where to get the website address from.

I created a less elegant solution that uses an IsEmpty check to move through the list of source hyperlinks with the correct starting point.

While i have a "solution", i'd really like to figure out why it does what it does.

Hello Peaceful4getFul,

I think I have the answer. A hyperlink has the ability to link externally and within the document (bookmark). External links, like a web site, are stored in the Hyperlink.Address property while bookmarks are stored in the Huperlink.SubAddress property. This could account for your index being "off".

Sincerely,
Leith Ross

Leif, i'm not sure i understand your answer (which does not mean it is wrong)

Where does a bookmark come into play?

Here is additional background:
The source data is a list of 1645 hyperlinks. If you follow each link, you are presented another list of hyperlinks associated with the particular topic, on the same website.

My program loops through each of the 1645 hyperlinks, sucks the data in and stores the data as a set of records/rows in a new sheet. The end result is about 28,000 records (which is why i automated this.)

The loop always starts in row 2, even if is reset "LastUsed", force the first cell to be selected, etc.

There are no hyperlinks in the workbook that point to places in the workbook, only to an external website. The web query is stored in the table along with the cells that are imported.

I place the data from each web query on the row following the last batch of data. (appending each import to the list).

No data is manipulated in the "source" sheet - i.e., the list of 1645 hyperlinks.

Hello Peaceful4getFul,

Sorry, I forgot to mention the bookmark (SubAddress property) can also point to a page on the website where the data is stored.

Sincerely,
Leith Ross