Adding Loop to Web Query

[97hills]

Hi everyone, I'm a complete VBA Beginner and I've come up with some extremely basic code, mainly from the in built macro recorder

Please Login or Register  to view this content.

The code first checks whether there is a Y in cell D1 on the "URL" sheet, if there is, it uses the url text in B1 and exports the data from the web page to cell A1 on the sheet called "1".

I then replicate this code 100 times for the sheet called "2" depending on the the other values like above, except on row 2.

Can anyone help with a more efficient way of doing this? There must be a way to add a counter to do this in a few lines of code rather than a few hundred?!

[AB33]

I am not sure if you want to loop through column B or D, but this code assumes you want to loop through column B. I also assume that you are testing each column D for "Y".

Please Login or Register  to view this content.

[97hills]

I am not sure if you want to loop through column B or D, but this code assumes you want to loop through column B. I also assume that you are testing each column D for "Y".

Please Login or Register  to view this content.

Sorry I don't think I explained it terribly well....

URL will be in column B1:100, the "Y" in D is a simple formula to just check there is a value in B1:100, it skips running the macro if there is no value. "Y" is just the denotation I have given for when there is a value.

So there's effectively 100 web queries using the url in B1:B100. These are outputted to a sheet called "1", "2" etc which coincide with the row they are within B1:B100.

That make sense?

Thanks for your help

[AB33]

If you want to skip blank cells, you can change the code. It is difficult to see what is going on with the code with out seeing the URL. There are 2 separate issues. First, does the code work and collects the right data, and second issue is the output, ie you want to create new sheets for each loop or table? I have added an error trapping line if the URL is wrong. Could you please post your URL with a sample sheet?

Please Login or Register  to view this content.