Loop For Each

**ann.gelovani** · 09-04-2019, 04:38 AM

Hi everyone,

I am trying to loop the range of formulas, that are located on one row and as a result i want to see a table which will include results of this formula based on parameters.

To tell the story, i have 3 tabs in excel, summary, parameters and drill down,

In parameters sheet, i put information about each contract so i have lets say 10 contract ID in each column and on rows i have specific parameters for those contracts.
Drill down takes the parameters and does calculation for each contract. (This is looped via For Each) So in one cell it picks the contract ID one by one and does the calculation for each one.
On Summary sheet, i want to transfer the results of those calculation also one by one, but instead of overwriting the results in one range, i want to have a table, which will have contract IDs in rows and some results in columns.
I guess i need to still use For Each loop, and i even indicated the range for the Contract IDs to be not one cell, but an actual range, but it still overwrites it all in one cell.
Here is the code,
Can you help?

Please Login or Register  to view this content.

**Richard Buttrey** · 09-04-2019, 04:54 AM

This question really should be a FAQ. I see it time and time again and looping just isn't the best way of doing this sort of thing.

Whenever you find yourself creating looping code stop and think is this really necessary? Most of the time it isn't. Loops are inherently slow when they have to jump back and forth between VBA and the Excel application - there's a big time overhead at each pass through the loop.

The fastest, most efficient and elegant way for this sort of stuff is to use an Autofilter to filter the rows you're interested in.
Then use syntax like

Please Login or Register  to view this content.

then paste to your destination cell.

This uses just one Copy / Paste instruction rather than many if you're looping through many cells.

So use the column that contains your 'singlecontract' references and Autofilter it for Non Blanks then copy and paste all the visible cells. Then finish by removing the Autofilter.

**ann.gelovani** · 09-04-2019, 05:15 AM

Hi Richard,

Thank you!

And Sorry, This is literally the second time in my life i am trying to do something using VBA and looping or autofilter or basically anything related to any kind of coding is something, i have never done before.
So, pardon my really basic questions.

I will get myself familiar with autofilter.

**Richard Buttrey** · 09-04-2019, 06:21 AM

Upload your workbook if you need more specific help.

**ann.gelovani** · 09-04-2019, 06:58 AM

i would love to, but i can't upload it as the button for upload does not work, and i do not have enough posts to put the link.

**Richard Buttrey** · 09-04-2019, 10:00 AM

To upload a file click the Go Advanced button at the foot of your post, look underneath the post area for the Manage Attachments section and take it from there.

Be sure to show a before and after situation with manually calculated results, explaining which information is data and which is results, and if it's not blindingly obvious how you have arrived at your results some explanatory notes as well.

Loop For Each

LinkBack

Thread Tools

Rate This Thread

Display

Loop For Each

Re: Loop For Each

Re: Loop For Each

Re: Loop For Each

Re: Loop For Each

Re: Loop For Each

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] VBA to loop through cell-defined range and paste table output after each loop.

Loop through a data validations list and for each loop count the number of occurences

[SOLVED] (Beginner help) For loop inside do loop that displays information from reference sheet.

[SOLVED] Auto email loop with formatted table data breaking on second loop VBA

[SOLVED] VBA to loop within a loop of multiple blocks of data (currentregion) ... Expert needed

[SOLVED] Copy dynamically changing column and Paste using VBA Loop (Loop within Loop)

Why did an inner loop variable start overwriting the outer loop range suddenly?

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions