For each cell in range 1 and for each cell in range 2?

[gwsampso]

Hi Guys,

Im having a little trouble when calling our database.

The code is trying to extract total cashflows - over a range of Portfolios “A4:A16” for a range of different Months “B2:M2”

I can successful create the code to produce cashflows for the Range of Portfolios for a single date but im unsure how to expand this from

For each cell in range

To

For each cell in range 1 and for each cell in range 2

I could replicate my working code 12 times just changing the dates and the offset part of the code but this is certainly not clean.

Im thinking maybe I have to do something with arrays?

Would love some help please

Thanks

gws

13 Ibbotson Cashflow Matrix.xlsm

Please Login or Register  to view this content.

[gwsampso]

new day maybe a new perspective?

please

[Norie]

You could loop through the portfolios and dates like this.

Please Login or Register  to view this content.

I'm not sure how that would fit with your query, because I'm not sure about the criteria.

By the way, it might be an idea to only create one connection to the database rather than one every time you loop.

Also, you can dump the results from a query in one go using CopyFromRecordset - that might not be appropriate though.

[gwsampso]

always such a simple solution i over look!

thanks Norie

finished code for those wondering

Calls Database, sums cashflows over a range of dates and porfolios, then dumps where the cells intersect

sure it can be cleaner but good enough for me

gws

Please Login or Register  to view this content.

[Norie]

Thanks for the feedback.

By the way, does creating so many connections not cause problems?

[gwsampso]

it hasn't caused a problem yet and it makes 150odds connections

I do close the connection everytime so it could probably run a little faster

[Norie]

I suppose if it's working then that's the important thing.