Nested Loop

Hi all,

I am trying to write write a VBA program which uses a bi-linear interpolation calculator (references another worksheet) to populate the range R4:R18. I wish to take each value in the range Q4:Q18 and follow the following process....

1) Take the first cell in range Q4:Q18 and paste into cell B25. This then calculates the interpolated value in cell B22
2) Let cell R4 = cell B22.
3) Repeat process for each cell in Range Q4:Q18 which will output in the adjacent range R4:R18

I am sure there is a much more efficient way than what I have been trying i.e. pasting values etc. I have my VBA attempt in the editor window.......Hope someone out there can help me

Regards,
Jackson

Untested but, in principle:

Please Login or Register  to view this content.

Some quick comments:
Is used "i" as index so it is certainly an integer and in next statement it is a range !!

Please Login or Register  to view this content.

There is some selections which takes time

Please Login or Register  to view this content.

can be changed to

Please Login or Register  to view this content.

Formula in B22 =FORECAST($C$7,B20:B21,$C$16:$C$17)
could be prepared in the macro to reduce access time

Finally to go really faster you have to work in memory (with tables ) and avoid any Pseudo range

Can you pepare a display showing the final result to check the macro

Thanks for the rep.



If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

You're welcome.

Simple and elegant!

That's me