Nested Loops

This is how I would approach that:

Please Login or Register  to view this content.

Hi JBeaucaire,

first of all, thank you so much for ur help here. To be hines I havent seen such a solution yet, so I ll have to work myself through it.

However, I ve been thinking that in case somebody can help me with these 3 loops I can fill out the rest myself, but with your example I am bit swamped. I actually want to do a simple calculation based on some signals. It d be quite difficult to explain it here, but I ve explained it in the attached excel sheet with formulas and some colours.

I ll try to understand your example in the meantime. I d be really really happy if you could have another look at it!

Cheers
Reza

I'm actually spotting the start (the first "XXX") and end (the first "Y") of the range to enter the "test", putting in a formula in column AP for that entire range, then removing the formulas and leaving the results behind. Then I find the next "XXX" after the last "Y", then the next "Y" after the new "XXX" and repeat.

If you're performing a "test" in each row of data, you would change the formula inserted to perform the actual test for that row. If you can devise a formula to test the first row where you WOULD do the test, I can show you how to adapt the formula it insert your real formulaic test into that existing formula so you get the results you really want.

I see the formula in the new file... so like so:

Please Login or Register  to view this content.

I also amended the code to include the "Y" at the bottom of each range where the formula is applied.

This version puts in the AR formula, too, and I'm leaving the formulas in so you can see them.

Please Login or Register  to view this content.

Jerry,

I really started laughing 5 minutes ago, because when i saw ur code the first thing that came to my mind was "magic!".

I started to learn VBA in September last year..next to my job... I invested some time in dvds, books and so and this is my first project now. It started out easy but then I really got stucked with this nested loops problem. I ve also used a lot VLOOKUP" and other excel functions but until now I have never used or actually seen "LOOKUP" in a sheet.

I am kind of really amazed, because I think If i have knew early what you can do with this Lookup ... it d save me a lot of time, however its good to practise. I definitely ll watch and read something about this lookup function.

The macro actually works pretty fine there are just three small things:

- the values in AR are missing
- the last value "ok" is missing in AQ
- and the minus sign is missing in the formula

I ve also attached a screenshot and an updated file again.

Could you please have just another quick look at it?

I really appreciate ur help and u code, because it also broaden my perspective of what you can really do with excel/vba. awesome! For today ur my hero

cheers
christian

So we want to include column AR from the start of "X"? So that shuffles some things around...

Please Login or Register  to view this content.

Jerry, hope you re well, thanks again...

so I played around with the macro and the LOOKUP function to get an idea how it works and I nearly found out everything. I hope you dont mind if I have one question and I also found another issue. This problem is like Medusa... once u knocked of a head 3 to 4 new heads are popin up...

I changed two things in the macro:

I ve always added "+1"

Please Login or Register  to view this content.

Please Login or Register  to view this content.

and I ve also used debug.print to get a sense of what the macro is doin:

Please Login or Register  to view this content.

My question now is, as far as I understood, the do loop searches in J for the first X which is now "$J$35" and as long the last X is or the last X found by the loop is not equal to "$J$35", the macro keeps running. I am bit confused now, because after the last X, which was "End of loop X.Address=$J$146" the loop states as next X "$J$35"

It this just the nature of the Do loop? That after it doesnt find another X in J, it automatically jumps back to the first value?

I ve also expanded the sheet a bit while I was playing with the macro. In my example the first value was X and the last value was Y in Range "J", but when the last value in "J" now is X, the macro just keeps running.

In fact sometimes my cells will start with X and end with Y, but sometimes they start with X and end with X for column "J".... and the same is true for the other way around ...so starting with Y and ending with X and starting with Y and ending with Y.

I dont really have an idea how to fix that, I attached the file as "example 2.2 forum_issue.xlsm". maybe you have an idea? the first idea was to let the loop run until the last cell, but I am not so sure if that makes sense.

Thank u so much again.

It this just the nature of the Do loop? That after it doesnt find another X in J, it automatically jumps back to the first value?

In this instance, yes. Don't believe that to always be true, the answer in each macro is determined by what is going on IN the DO/LOOP.

In this Do, the FIND "X" is always looking AFTER the current row of "Y". The FIND function is the one that will search down (I'm using the default, it could be told to search UP with other parameters I omitted). And YES, when it reaches the end of the search range it will jump up to the top and search down from the top again. That's why we memorized the "first" X we found so that if we ever found it a second time, the DO/LOOP would abort because were' done with the data. These are design choices.

I ve also expanded the sheet a bit while I was playing with the macro. In my example the first value was X and the last value was Y in Range "J", but when the last value in "J" now is X, the macro just keeps running.

Not sure I follow that, but the only scenario the macro currently works for is your original request to "start at X-XXX and finish at Y". No other logic is included.

I believe the macro will work as is if you simply add a "Y" at the bottom on the last row of data before you run it.