Need to loop through cell reference as variables for query

**crowby1** · 06-05-2012, 11:03 AM

Hi

I am looking to modify a spreadsheet macro I have already written so that it uses customer numbers from 250 cell references to do a query for each customer. The results of each query populate into a table. Originally I only needed to query 50 customers so rather than write an array (not knowing much about these) I basically repeated the query across 50 variables (e.g. cust1 = Range("A1"), cust2 = Range("A2") etc). There was no loop, the script literally defined the variable and ran the query 50 times, selecting the range of the query by moving down 3 lines each time. I know, very long winded but it did the job.

To repeat the query in the macro 250 times causes the macro to report back it is too large so naturally I want to find out how to create an array. The thing is, I have Googled this quite heavily and although people have explained how to write an array, it never seems to suit my needs.

The spreadsheet has 250 queries so I intend to get it to move down this and populate the data in the query results into the table on the left of the screen. I need the query to move through the cells containing the customer numbers/ variables, but not to lose its place in the query sequence. Ideally I would like to modify what I already have rather than scrap the whole spreadsheet and start from scratch.

Any help would be much appreciated!

**Pulsus** · 06-05-2012, 11:13 AM

Crowby1,

You can use This in your Code:

Please Login or Register  to view this content.

**crowby1** · 06-05-2012, 11:31 AM

Wow thanks Pulsus for coming back so quickly. I was trying to increase the range in a loop before but it was causing the variable itself to increment rather than the cell reference (so you can probably guess what I was doing). This looks like exactly what I need. I look forward to trying it out when I am back at work tomorrow. If I'm ever in Sao Paulo I owe you a beer!

**Pulsus** · 06-05-2012, 12:28 PM

crowby1,

I hope this can help, if don´t please let me know so we can try out another solution. I will look again tomorrow for the answer.

Nice to know about the beer, who knows one day...

Cheers,

**crowby1** · 06-06-2012, 08:43 AM

Originally Posted by Pulsus

crowby1,

I hope this can help, if don´t please let me know so we can try out another solution. I will look again tomorrow for the answer.

Nice to know about the beer, who knows one day...

Cheers,

It worked a treat, thanks Pulsus. I was on another help site before I came to this one and an 'expert' wanted to charge me £16 for an answer, so a pint of beer is a small price to pay!