Link to home
Start Free TrialLog in
Avatar of MCARSONNBG
MCARSONNBGFlag for United States of America

asked on

EXCEL 2007 MACRO PROBLEM

I am inexperienced with Excel Macros and I do not understand what the problem is with the attached recroded Macro.  What statement(s) do I nedd to add for this to run properly?

Thank you
Doc1.docx
SOLUTION
Avatar of patrickab
patrickab
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of MCARSONNBG

ASKER

Thank  you Patrick,

Sorry about the screen shot.  I will check into the link you provided.  How do I upload the code in Excel format - by attaching the actual Excel spreadsheet in which the code is contained.

Michael
Michael,

>How do I upload the code in Excel format - by attaching the actual Excel spreadsheet in which the code is contained.

Yep, that's exactly what you need to do - attach the file by clicking on the 'file' link under the 'Comment' box. Not only do we see the code but we also get to see the problem you are wanting to solve.

Patrick
Hi Patrick,

The excel spreadsheet is attached and I would appreciate any thoughts you have on ther fix.  I will review and eduicate myself on the process at a later time based on the link you provided.

Regards,

Michael
BUDGET-2011---2014---2--loan-gro.xlsm
Michael,

I'm afraid you have not set up the worksheet correctly to be able to use Solver. The cells in row 72 need to be formulae - NOT just a value. Equally if the cells in row 72 are, for example, =BV50-BV70 then that is no good either as you have already specified in the Solver constraints that BV50 and BV70 must equal one another.

I am not all all clear what you want to maximise nor what cells you are wanting alter to achieve that maximum. Do please explain as until we have sorted out just what's wanted we can't make any progress in automating Solver.

Patrick
Patrick,

The solver function should contain the following for each column containing data beginning with AV through BR:

Set Target  Col X Row 72  Equal to  MAX 0
By changing Cell  Col X Row 40
Subject to Constraint - Col X Row 50 = Col X Row 70

Can I increase the points value of this question?  I am new to this.

Thank you for your assistance.

Michael
 

Michael,

You are not dealing with the issue that I have raised. Please re-read my comment ID:34481251.

It is no use just repeating the Solver set up when I have already said that the worksheet is not correctly set up to use Solver. Please change Col x Row 72 to formulae. Until it is a formula it cannot work.

Do by all means increase the points you are offering - if that's what you would like to do.

Patrick
Avatar of Saqib Husain
MCARSONNBG,

Open the Visual basic window and go to

Tools > References.

You should be able to find an item called Solver. Ideally it should be somewhere near the top. If not, try to find it in its alphabetical position somewhere down the list (you might have to scroll down for it). Make sure that it is checked and then click on OK. If it is already checked then uncheck it and click ok. Then go back and check it again. Also try restarting excel after that if it does not work.

Saqib
Patrick,

I do not follow your line of questioning nor do I care for your current attitude toward my problem.

I will look for other solutions.

Thank you
Saqib,

When I attempt to follow your instructions I recevie the following error message-

Name conflicts with existing module, project, or object library.

Any suggestions?

Thank you
can you give me a screen shot before you click OK?
Thank you,

Before and after screenshots are attached.

Michael
AFTER-SELECTING-SOLVER-AND-OK.docx
I AM NOT SURE THE BEFORE CAME THROUGH.  I HAVE ATTACHED IT AGAIN.

MICHAEL
PRIOR-TO.docx
MCARSONNBG,

>I do not follow your line of questioning nor do I care for your current attitude toward my problem.

So be it. My attitude is to solve the problem. I thought that's why you asked the question in the first place. It all depends on whether you want the problem solved or not. I have indicated what you need to do to assist in finding a solution. It is up to you as to whether you accept or ignore my comments.

I have indicated very clearly that the cells in row 72 need to be formulae and I have explained why.

Patrick
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ssaqibh,

You solved my problem!  Thank you

Patrick, your article was helpful, but otherwise your questions, comments, etc were confusing.  And you need a better attitude toward the process, in my humble opinion.

Regards,

Michael
MCARSONNBG,

Let me see if I can explain the problem in a different way.

Let's take column BR as the example. The sum of the cells BR39:49 in cell BR50 =  $306,749. Likewise the sum of the cells BR53:67 in cell BR70 = $306,749 - both ignoring subtotals.

In the Solver constraints you have specified that BR50 must equal BR70. It is impossible to vary just cell BR40 and yet keep BR50 equalling BR70. There is nothing for Solver to do, nor that it can do as you have already provided the solution in the figures given. Zero is the maximum for cell BR72.

Patrick


MCARSONNBG,

>And you need a better attitude toward the process, in my humble opinion.

I am a volunteer here and if my comments are ignored, as indeed you have ignored them, it is me that should be impatient not you. Do by all means tell me that my comments make no sense or that they are illogical, but please don't just ignore them. I am always happy to explain and indeed have been doing that here on EE for many years.

I have automated Solver many times and I can assure you it is not as straightforward as at first sight. I don't guarantee that I can provide a solution for you but I'll have a good go provided you are willing to work with me - and that is obviously your choice.

Patrick
MCARSONNBG,

>You solved my problem!  Thank you

By the looks of it you have your solution - that's good.

Patrick

Patrick,

You are wrong.  The solver add-in works perfectly fine with the imputs as I laid them out.  The only issue I was having was how to make the solver add-in work properly within a MACRO so I don't have to manually rerun the solver add-in for each column..  The article you provided me with pointed me to the SolverReset code which helped.  Everything was solved by ssaqibh's comments and answers.
Frankly speaking, Michael, Patrick's link does address your problem and that is why I had not posted earlier. When I saw this discussion taking a different direction I chose to butt in. Anyways all's well that ends well. Happy solving and thanks for the points.

Saqib
I apologize to all then.  I will assume the communication breakdown is my problem, being a novice to MACROS, and I'll go from there.  The problem has been solved and thank you both.

Michael
Michael,

The important thing is that the problem has been solved.

Thank you for the points.

Patrick