# 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?

• Hello,

Looking at you worksheet ...

For the Solver engine to operate .... you do need to set a Target cell ...which contains a formula ...

Have you already used the Solver ...?

If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner

• Hi

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

• Hi,

No problem ...

But do we agree the Target cell A1 ... should be containing a Formula ... for the Solver to operate ...

Do you mind posting your file ...

If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner

• 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

• Hi Angela

Thanks for clarifying ...

If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner

• Hi

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

• Hi,

No problem ...:wink:

1. Which cell is the Target cell in your model ?

2. Do you have a formula in this Target cell ?

3. Why don't you attach your excel file ?

Things could be sorted out pretty quickly ...:wink:

If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner

• Hi

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

I've attached my workbook.
Thanks again.

## Files

• Hi Angela,

Thanks for the clarification ... :wink:

Attached is your Test file ...

Hope this will help

## Files

If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner

• 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.

• You are welcome ... :wink:

Hope you feel better pretty soon ...:smile:

If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner

• 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.

• Hi Angela,

Glad to hear you are feeling better ...:smile:

In order to determine if what you are asking for could be feasible ...

Could you kindly clarify a few mysterious things ::duh:

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

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

3. How and why cells I12 and L24 show the value 7.77 ... which also appears in B23 ? (you know I LOVE the Number 7 :hyper2:)

If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner

• 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 a lot for your explanation ... !!! :smile:

Give me a bit of time ...

1. to read carefully all the details ...

2. to digest these elements and all their consequences ...

3. to evaluate how everything could be incorporated into your macro ...

Cheerio

:wink:

If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner

• Hello Angela,

Attached is your file Version 2 ...

Despite your very clear explanations ... still have the strange feeling I might be missing important things ...

In this Version ... just input 7.77 in cell I12 ... and Run the Macro ...

All your usual manual adjustments are being taken care of by the macro ...

Let me know what the next automation step should be ... :wink:

## Files

If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner

• 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.

• Hi Angela,

With your latest explanation ,,, it seems to me all data located in the ranges E1:E20 and H1:H20 ... should be out the way ...

In the attached Version 3 ... just input 7.77 in cell L24 ... or 7 in cell J24 .... :wink:

## Files

If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner

• 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.

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!