Macro calculation and post

Currently I use Solver to do calculations and then post the result myself. Can I set up some VBA code to do 3 things (look, calculate and post)? I have looked and looked on the net for a solution and nothing. I have a lot of these to do (I have attached a very small sample spreadsheet) so a VBA program would be a real time saver. Any input/comments would be appreciated.
[ATTACH=JSON]{"data-align":"none","data-size":"full","title":"EXCEL SHEET SOLVER.xlsx","data-attachmentid":1208518}[/ATTACH]
First, I would need code to search and determine if there are any numbers in the following cells: D24,F24,H24,J24,L24. If any, I need the program to do a calculation, for each cell, using Solver and then enter the answer in the appropriate cell by referencing the cell above it. Using my example of cell I24 with 7.77 in it (I hope my description of the calculation below is okay, if not please advise) I would need a macro to do the calculation which is as follows:

Cell A23 = 2

Cell B42 = A23+7.77 (7.77 have to be entered by macro)

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)

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

The code would then have to search and determine where the 5 (cell L23) gets used as a reference number to refer to any cell within the range of E1:E20,H1:H20 with a 5 found and has a number in the adjacent cell to the right. The 1.55 would be placed in cell J12 and added to the amount in cell I12 (original figure was 7.77 + 1.55 = 9.32).

After each calculation all cells D24,F24,H24,J24,L24 would be blank.

Thank-you for all your help.

[ATTACH=JSON]{"data-align":"none","data-size":"full","title":"excel spread.jpeg","data-attachmentid":1208519}[/ATTACH]

Hi Carim

Thanks for your response. I have no experience with the record function but have been on the net for quite a few hours searching for examples on how to record. All I can find are simple examples (adding two cells etc.) that does nothing for this problem What I really need is very specific instruction on how to use record for this problem, especially Solver. I don't think this is a simple but I could be wrong, Do you know of any sites to help. Maybe I just need a record for dummies book. Just how to proceed is the issue.

I'm using an older version, 2007 Excel on Windows 7 software. Yes macros are allowed and I have installed the developer tab.

Just tried it and I'm liking this! One question, how do you record a range of cells, in my example how do I record cells D24,F24,H24,J24,L24 to see there any numbers in it? My record only gets one cell.

This the code produced:

Sub SHEET2 ()Range("F1").Select[/SIZE][/COLOR][COLOR=#000000][size=12]ActiveCell.FormulaR1C1 = “=RC[-1]+7.77”[/SIZE][/COLOR][COLOR=#000000][size=12]Range(“G1”).Select[/SIZE][/COLOR][COLOR=#000000][size=12]ActiveCell.FormulaR1C1 = “=RC[-2]/2*R[4]C[-4]”[/SIZE][/COLOR][COLOR=#000000][size=12] [/SIZE][/COLOR][COLOR=#000000][size=12]Range(“M11”).Select[/SIZE][/COLOR][COLOR=#000000][size=12]ActiveCell.FormulaR1C1 = “1.55”[/SIZE][/COLOR][COLOR=#000000][size=12]Range(“L11”).Select[/SIZE][/COLOR][COLOR=#000000][size=12]ActiveCell.FormulaR1C1 = “9.32”[/SIZE][/COLOR][COLOR=#000000][size=12]Range(“L22”).Select[/SIZE][/COLOR][COLOR=#000000][size=12]ActiveCell.FormulaR1C1 = “0”[/SIZE][/COLOR][COLOR=#000000][size=12]Range(“M22”).Select[/SIZE][/COLOR][COLOR=#000000][size=12]End Sub[/SIZE][/COLOR][COLOR=#000000][size=12]

I tried it and it works but only the cell used in the example. I would assume you would enter the other cells in Range L22 line but how how would the code select the right cell for say cell D24 and use the cell above it, 1? That's so much for your help [/SIZE][/COLOR]

Hi
I'm still here and the example from above works great!!!!. I was playing with the code but I can't get another example to work. What if their was a 5.00 in cell D24 and the original example? How would that work? Thanks so much for helping me.
Angela

I could have numbers in all cells: [SIZE=12px]D24,F24,H24,J24,L24 that need to be calculated using Solver. Can we get the code to check each cell and if a number is found use Solver to figure it out and put the result on my sheet? That would save me a huge amount of time. [/SIZE]

Thank-you Carim

I'm off to visit my sister for a week but will test this out when I get back.
.
Thanks again fro your help.

Hi Carim

I'm back. It was fun but I'm trying to get this project back on track. I was thinking while I was away is it even possible to use VBA code to do what I listed below?

1) First a search for any numbers in the following cells: D24,F24,H24,J24,L24. Numbers will always be found in 1 or more cells. Cell format is 00.00.

2) For every found number move that number into Solver to do a calculation and post the results.

Thanks for your response as always.

Hi

I’m back again and I hope you’re doing well. I tried the code and ran into some problems but I fixed it and the code works!!!! You have to be patient with me and I’ll get there.

The message comes up “All Data is NOT available for Solver...”