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]

  • Hello and Welcome to the Forum ...:smile:


    Just out of curiosity ... :wink:


    Have you tried to turn on your macro recorder and go once through your process ... to get an initial "VBA Translation" of your actions ...?

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

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

  • Hello Angela,


    There are several very basic points to clarify ...beforehand ...


    1. Which Excel version are you running ?


    2. Are the macros allowed in your Excel Options ?


    3. Do you see the Developer Tab on your ribbon ?

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

  • Well ... we are heading in the right direction .. :wink:


    If you select the Developer Tab .... you will get these choices ...


    [ATTACH=JSON]{"data-align":"none","data-size":"medium","data-attachmentid":1208544}[/ATTACH]


    And in the first block there is Record Macro ...


    Select it ...


    and go through your process ...


    Once you are done, click on Stop recording ...


    That's it ...


    Hope this will help

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


    Code
    1. 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]

  • First of all ... Congratulations on your first macro ... !!! :wink:


    You will agree with me that the huge mountain you had imagined ... was only a tiny hill ... !!! :smile:

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

  • And your macro can be improved a little ...


    An initial proposal would be as follows :


    Code
    1. Sub Inputs()
    2. Range("F1").FormulaR1C1 = "=RC[-1]+7.77"
    3. Range("G1").FormulaR1C1 = "=RC[-2]/2*R[4]C[-4]"
    4. Range("M11").Value = 1.55
    5. Range("L11").Value = 9.32
    6. Range("L22").Value = 0
    7. End Sub


    Please note the macro's name has changed to give an indication of the contents ...
    and all the Select instructions have disappeared ... since there is no need for the cursor to actually move ...:wink:


    Hope this will help

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

  • 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

  • Hello,


    Not sure to understand your latest question ...:smile:


    If you have totally understood the concepts in the macro Inputs() ...


    Nothing should prevent you from adapting this macro to another set of data ...

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

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

  • Hello,


    Below is a macro to be tested... to determine if it is in line with your objective :



    Hope this will help

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

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

  • Hello Angela,


    Glad to hear you are back ...


    Before getting into the Solver issue ...


    Have you tested the macro Inputs2 ...?

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

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

  • Hi,


    The idea behind this macro is quite simple ...


    It just allows to determine if the Solver Add-in can be used or not ...


    Indeed, the Solver does require ALL data to be available .. . before you can run it to find the expected results ...


    Hope this clarifies

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