Macro calculation and post

Thanks for the info. so what do I do next and is this possible to set up code to do this project?

Hi

I use Solver which a friend set up for me. I looked at it and I assume it's required.
Thanks

Hi Carim

I'm so sorry for the delay in getting back to you. I'm only back a few days and I'm really not feeding well but lets push on. I'm not sure where are you getting A1. All 3 cells for Solver are located in A23, B23 and C23. I've copied my notes with the settings from Solver and the answer (1.55) if using the 7.77 problem from above to be solved:

[SIZE=12px]Cell A23 = 2[/SIZE]

[SIZE=12px]Cell B42 = A23+7.77 (7.77 have to be entered by macro) [/SIZE]

[SIZE=12px]Cell C23 = A23/2*B5 (B5 have to entered by macro. Cell L23 with 5 in it gets used to refer to cell A5 and use the adjacent cell B5 number of 12.00)[/SIZE]

Solver – Set Target cell: $A$1

Equal To: Min Value of: 0

By changing cell $A$23

Subject to the constraints $B$23 = $C$23

Answer: 1.55

Hi

I just noticed the A1 you were referring to, I made a mistake on that one. Sorry about that.

Hi

The target cell is B23.
Cell b23 has: =A23+7.77

I've attached my workbook.
Thanks again.

Thanks so much for your help. I'm still not feeling great so if i can get back to you in a few days that would be great.
Thanks again.

Hi Carim

Feeling better today and so tested your software. It works great and a huge thank-you.

Is it possible to get the result from Solver to be posted to the correct cell, in this case J12? The software would have to reference cell L23 as the reference number and then search cells E1 to E20 and H1 to H20 to find a number 5 with any number in the adjacent cell to the right of it. In this case it would have found cell H12 and cell I12 with a 7.77 in it and then place the Solver calculation of 1.55 in cell J12..

Not sure if this is possible and please let me know if it isn't, just a wish list. Thank-you so much.

All three of your questions are related and I can see why it's not clear so please let me explain:

"1. Why is cell L23 ( value 5 ) the reference number to search ?"

Cells D23, F23, H23, J23 and L23 are in numerical order and represent numbers from above in A1 to A20 (see quest. 3 for more info. On this row) E1 to E20 and H1 to H20. Value 5, cell L23 comes from cell H12 because it has a 5 and has an adjacent number to the right of 7.77 in cell L24. If the 7.77 was in cell F2 it would still be placed in cell L24 to be solved.

A friend of mime said that if I setup the numbers (1 to 5 in this example, cells D23, F23, H23, J23 and L23 ) to be Solved in a central place then maybe software could be developed for those cells only verses having to develop software for each cell in the range (5 cells vs 40 in this example) and so it would be a lot easier.

"2. Do you have to run the Solver 40 times for all the ' couples ' in Columns E-F and H-I ?

Right now I only use Solver when there is a number located in the cell range F1 to F20 and I1 to I20.

"3. How and why cells I12 and L24 show the value 7.77 ... which also appears in B23 ?"

In cell B23 I would enter 7.77 (replacing any preexisting number) which comes from cell L24 and it's the number I have to enter manually into Solver to get a calculation. As I mentioned above these cells D23, F23, H23, J23 and L23 are in numerical order and represent numbers from above in A1 to A20. I then go to cell C23 and enter B5 (which comes from the number 5 in cell L23 and matches cell A5 and enter B5. Using Solver the answer is 1.55 and if possible the 1.55 would be placed in cell J12.

There is the whole circle. I hope this clarifies what I’m doing and if not please ask away. Additional details are there will always be a number in one or all the cells D24, F24, H24, J24 and L24.

Thanks again for all your help you have been so patient with me.

Hi

This is exciting! I just didn’t know if it was possible. You have given me a great idea if you can make a change. Bear with me but if you can move the input from I1 to I20 (I see you only selected 1 to 5 which is fine) to cells D24, F24, H24, J24 and L24. Move the Solver solution or yellow section J1 to J20 to cells D25, F25, H25, J25 and L25 or the row below that one whichever is easier for you.

As a test can we put any number you wish into all five cells and have each one Solved with the results placed in whichever cells you choose to put the output.

I think this is possible.

As always thank-you so much.

Good morning

The sheet is wonderful. As a test I put the following numbers on my sheet:

1.11 in cell D24
2..22 in cell F24
3.33 in cell H24
4.44 in cell J24
7.77 in cell L24

I use Solver and the same answer 7.77 came up for each entry in the following cells:D26, F26, H26, J26 and L26. I'm not sure if it's something I did of do we have to make an adjustment? Still it's really exciting to be so close to having it work.