Updating a formula to include the last cell with a value.

[burlywood66]

I'm trying to modify a formula to run down to the last cell with a value. If you open the attached spreadsheet, a sumproduct in cell D1 is currently running from A1:B6. This may change to any table size between columns A and B (where row 6 currently has the last set of values). I simply want to create a macro where the sumproduct formula will run from A1:BX where X is the last row there is a value.

Let me know if you have any questions.

If you are curious, I am trying to use this for the solver add-in. I can go into more details on that if need be.

Thanks in advance.

Solver.xlsx

[AB33]

Try the attached.

[Alf]

I understood you problem a bit different from what did AB33 so my macro works in another way.

You enter the target value in cell F3, and then run macro "SolverRun".

Macro checks the range with values in column A then "updates" the "SUMPRODUCT" formula in cell D3, clears the corresponding range in column B and launches solver.

Instead of entering target value in solver I've used the setup where solver should minimize the value in cell H3.

Before running this macro you must set a reference to solver in the "Visual Basic" window. Go to Tools -> References and tick box marked Solver.

If not found click "Browse" and locate file "Solver.xlam" most likely found at "Microsoft Office\Office 14\Library\Solver\solver.xlam"

Alf