Hello, I'm working on a project in VBA for Excel and the web. The goal is to trawl a college's Business School directory for a list of professors and their contact information. Once the data is collected I need to write a loop that will go through all of the individuals and paste their name/address/etc onto a master list.
So far I have been able to collect the data from the web and paste it into another sheet. However, I don't know exactly how to start constructing the for loop to go through all the individuals and paste their respective information onto the new list/sheet. I have two images that demonstrate better what I am getting at.
First, this is what the data looks like when I collect it.
Then I need to put the data into a list on another sheet, for example, this would be the first entry.
I'd imagine I'll have to hard code the first 6 digits of the telephone number since they only give you the last 4. Address information is all in cell A57 and appears as "4205 Ryan Street, Lake Charles, LA 70609" Every member will have this address.
"McNeese State University" is located at cell A61. Every member will have this University Name.
"College of Business" will probably be hardcoded. Every member will have this Business School Name.
"http://ww2/mcneese.edu/colleges/bus" will probably be hardcoded as the link on their website is to a 404. Every member will have this Business School URL.
I'm trawling the data from "http://ww2.mcneese.edu/colleges/bus/directory.asp" Every member will have this URL.
Any idea as to how this for loop should look? Any help is appreciated.
So far I have been able to collect the data from the web and paste it into another sheet. However, I don't know exactly how to start constructing the for loop to go through all the individuals and paste their respective information onto the new list/sheet. I have two images that demonstrate better what I am getting at.
First, this is what the data looks like when I collect it.
Then I need to put the data into a list on another sheet, for example, this would be the first entry.
I'd imagine I'll have to hard code the first 6 digits of the telephone number since they only give you the last 4. Address information is all in cell A57 and appears as "4205 Ryan Street, Lake Charles, LA 70609" Every member will have this address.
"McNeese State University" is located at cell A61. Every member will have this University Name.
"College of Business" will probably be hardcoded. Every member will have this Business School Name.
"http://ww2/mcneese.edu/colleges/bus" will probably be hardcoded as the link on their website is to a 404. Every member will have this Business School URL.
I'm trawling the data from "http://ww2.mcneese.edu/colleges/bus/directory.asp" Every member will have this URL.
Any idea as to how this for loop should look? Any help is appreciated.
Last edited:
