Hi,

I'm currently trying to make a code for implementation of a modular arithmetic as constraint for Solver. I'm having a production optimization problem where I need to get the solution in multiples of a given number x from the user input and I have no idea how to code this so it will be accessible for Solver.

Thankful for all help.

I'm currently trying to make a code for implementation of a modular arithmetic as constraint for Solver. I'm having a production optimization problem where I need to get the solution in multiples of a given number x from the user input and I have no idea how to code this so it will be accessible for Solver.

Thankful for all help.

- Relational contraints in Solver (i.e. can't pursue project A & B at same time)
- ListBox MultiSelectMulti implemented as dropdown in LinkedCell
- Solver with variable as constraint
- Solver constraints shall be OR // Solver target cell
- Ratios in Excel Solver
- How can I set >= and
- Limiting Constraints in Solver
- Loop Formula in Solver
- Macro for 4 constraints in autofilter
- What does error message "too many adjustable cells" mean in solver
- Multiple Solver Constraints in Solver
- How to create constaints in solver
- Modular Arithmetic
- Using VBA to create a variable "adjustable cells" array in Solver
- Excel solver binary variables
- Macro for 4 constraints in autofilter
- The number of workbooks open as data in VBA
- Graphic symbols as selections in a list box?
- Looping Through A Range - Use Result As Criteria In A Filter
- Paste Visible Formulas Cells as Values in Filtered Table
- Display negative number as zero in vba
- Using SumIF on dates stored as text in pivot table
- VBA: Copying a Multiple Selection but paste as Values in sam
- Negative Number As Zero In Calculations

Project 2 can't be pursued unless Project 1 is

Project 3 can't be pursued unless Project 1 is

Project 4 can't be pursued unless Project 2 is

Project 4 can't be pursued unless Project 3 is

If Project 6 is funded, so MUST Project 7 (and vice-versa)

Projects 5 and 6 cannot be funded simultaneously

I currently have the data set up like this:

Show# Invest (Yes=1,No=0) ROI Amount Invested Total Return

1 1 7% 0

2 1 9% 0

3 1 4% 0

4 1 25% 0

5 1 21% 0

6 1 14% 0

7 1 8% 0

8 1 16% 0

The list box looks in A1:A41 (one word per cell). The ListBox is MultiSelectMulti and display all selections in B1 (in the same sheet).

I've added code in the VBA for the sheet containing the ListBox that tells it to separate selections by comma, and remove selections from LinkedCell if unchecked in the ListBox.

BUT...

Right now the ListBox is open and placed close to the ListFillRange and the LinkedCell. This is where I built it and this is where the problem begins.

BECAUSE...

The ListBox is built to appear by clicking a dropdown arrow in a selected cell, and then insert the selected values into this cell. Like a normal datavalidation drop down, but with multiple selections separated by comma.

It is supposed to be implemented to every single cell in an entire column. This column is in another workSheet.

HOW..

Do I apply the ListBox to multiple separate cells in another worksheet as a drop down?

My VBA skills are limited, but i'm a fast learner.

Thanks

I have a complex problem, see if you have time to help me solve that.

I am now doing a scheduling problem. I was given an staff requirement for every 15 mins, for example, 00:00-00:15 have a number, 00:15-00:30 have another......However, the job done by the staff can be within 30 mins , meaning that the work done by the staff in 00:00-00:15 can have a feasible period 00:00-00:30 to finish, while 00:15-00:30 can be finished in 00:15-00:45. This pattern will therefore lead staff requirement become variable, say in 00:15-00:30, we dunno the exact no. of staff needed now. Hence, for each period, I have a staff requirement as variable. After I run this, however, I want to create a plan to cover those staff requirement. Hence I use those variable as constraint now and apply those common scheduling method. However, everytime I run that, the cost which has a minimize objective give different solution each time (different cost).

I wanna sort out what happen and any solution can help

You may need to see the attach file to think a while for what I am doing

Thx

Cheers and this would be great help

Bong

I have a question regarding the constraints in solver.

I want a sum of percentages to add to one. There are 50 parts alltogether. They all shall be between 3% and 5%, or zero.

Is there a way to make Solver take into account those constraints, do I need to use VBA?

On another matter for the sake of convenience I want to create a sheet, where the user can enter the desired target value and just click a button, to run a makro that solves. But for this makro I can just set the target cell to a specific value, and the button will - of course - always just solve for this. As I cannot link the target cell to a cell in the sheet where the user might enter the desired value, I wonder if there is another way to achieve this.

Thanks in advance.

Say I have a group of variable cells F5:F10. How can I set a constraint for those variables that will keep them between say 4 and 8.

I need to set a formula as an Objective Function in Solver. The formula is not straight forward as I need to use For Loop to calculate the value in the diagonal of a nxn matrix. For example, I want to find the total of cell(1,2)+cell(2,3)+cell(3,4)+...+ cell(n-1,n)+cell(n,1) and this total will be the objective function in Solver. How do I write the coding in VBA for SolverOk SetCell? Your help would be much appreciated!

Thanks!

Not sure if this is possible, or if I have tackled this the right way so any

help greatly appreciated! I am (trying to) write a macro that will use

multiple constraints (in 1 column) in autofilter, this is so that I don't

have to use the advanced filter option. The result of the macro should return

rows that contain "19" or "20" or "act" or "regul" in the 3rd field, and hide

the rows that don't. My macro that is not working at the moment:

'

Sheets("Agr-Tra").Select

Selection.AutoFilter Field:=3, Criteria1:="=regul*", Operator:=xlOr

Selection.AutoFilter Field:=3, Criteria1:="=act*", Operator:=xlOr

Selection.AutoFilter Field:=3, Criteria1:="=19*", Operator:=xlOr

Selection.AutoFilter Field:=3, Criteria1:="=20*"

End Sub

This is returning 0 rows with the above criteria, which is incorrect as it

should be over 300 rows.

Much appreciated

Gus

numbers. Basically I want to find the sum of 4311.20 in a list of numbers .

My target cell has the formula =sumproduct(actuals,ones) where actuals is

the name of the cells to use for the solution, and ones is for the binary

number.

In solver I am selected the target cell with the formula, value = to

4311.20, by changing cells has the word "ones" for the column with ones in

it, and the constraints are ones=binary. I get the error message "Too many

adjustable cells".

What I've been trying to do is use solver to say: Make D5 equal 200, do it by manipulating only A5 B5 and C5, and make it subject to the constraint that A5 must equal a value selected from A1:A4, and B5 must equal a value from B1:B4, and C5 ...etc. I have deliberately set it up so that there is only one solution.

I was doing fine until trying to create the constraints. How can I make a constraint that says "this cell" must equal "one of the following cells"? And if I can't do that, is there an alternate method of achieving the same result?

Thanks,

-Simon

into an IT project does not exceed a certain % of the total capital budget

for a 5 year period.

here it is...

I want to solve a modular arithmetic problem in Excel (so i have been told

by a math professor).

Specifically, I want to solve for t (time) in the present value formula.

However, i (interest) is not constant, otherwise I'd use the log function.

I want (i) to increase, let's say every 2nd, 3rd, or 4th, year, which makes

this problem a modular arithmetic problem.

I've set up a table to determine the value of (t) given my variable (i) but

I was looking for a more direct approach. Any ideas anyone?

I am creating a certain optimization system in Excel that uses Solver. Around 60 optimizations are carried out using Solver in combination with VBA. During these optimizations, the adjustable cells array varies. Therefore:

I would like to use VBA to determine the array used in Solver as the adjustable cells.

In other words: If cell A1 returns a value of 5, the number of rows used in the array of adjustable cells in Solver is 5. When cell B1 returns a value of 2, the number of columns used in the array of adjustable cells in Solver is 2.

Could anybody help me please?

I am having a problem in getting excel solver to solve a linear programming problem.

Suppose i have binary variables as follows:

0 1 1 0 1 0 1

I want to track the change between each variable. eg if there's a change of value from one variable to the next then i want to times it by 15 ie (x2-x1)*15.

The problem is i want a positive 15 not negative 15 in some cases.

I tried using absolute value function but it's not linear so i cant use solver with simplex method to solve it.

Note: I can only use solver simplex method to solve this.

The above problem is an extract of a bigger problem with more constraints in solver.

If you have any more questions feel free to ask.

Thanks

Not sure if this is possible, or if I have tackled this the right way so any

help greatly appreciated! I am (trying to) write a macro that will use

multiple constraints (in 1 column) in autofilter, this is so that I don't

have to use the advanced filter option. The result of the macro should return

rows that contain "19" or "20" or "act" or "regul" in the 3rd field, and hide

the rows that don't. My macro that is not working at the moment:

'

Sheets("Agr-Tra").Select

Selection.AutoFilter Field:=3, Criteria1:="=regul*", Operator:=xlOr

Selection.AutoFilter Field:=3, Criteria1:="=act*", Operator:=xlOr

Selection.AutoFilter Field:=3, Criteria1:="=19*", Operator:=xlOr

Selection.AutoFilter Field:=3, Criteria1:="=20*"

End Sub

This is returning 0 rows with the above criteria, which is incorrect as it

should be over 300 rows.

Much appreciated

Gus

Any idea will be welcome!

Thanks!

I am trying to learn how to loop so am starting with a simple example (see attached), hopefully to apply to a larger spreadsheet. I would like to loop through a named range called "Buildings" and use each result as criteria in a filter on another sheet within the workbook.

E.g. The named range has values B1, B2 and B3. I would like to use each of the values in the range to filter the data on the sheet called "data". Each result will be copied and pasted to another workbook and called B1.xls, B2.xls etc. In this case I will have 3 new workbooks with the information for each of the Buildings.

I would like to do this all in VBA.

If somebody can help me I would be grateful.

Thanks

in the sample worksheet, I need help with a VB code to copy the formulas in column D, to paste them as values in the same cells (without removing autofilter by clicking on menu Data>Filter>Autofilter, then edit>copy>paste special>values).

Thanks in advance for the help.

This is driving me crazy - I have to show negative numbers as zero in my VBA program. I can do this in the worksheet by using Format, Cell, Number, Custom and choosing the format #,#00;"0"

However, when I use NumberFormat in my VBA code, it already has to have quotes around it like this:

VB:So when I try to add my quotes to display the negative number as zero like thisWorksheets("Fees4").Range("c44:f46").NumberFormat = "#,#00;0"If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

VB:it doesn't like it - any suggestions?Worksheets("Fees4").Range("c44:f46").NumberFormat = "#,#00;"0""If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

Sorry I couldn't find an answer in the help so I'm starting a new thread.

I have a pivot table and the top row are dates, unhelpfully stored as text values in the form "Feb-05", "Mar-05".

Further down in the table I have another row running parallel to the date row that tracks the mandays allocated to a particular project (each project has it's own row).

I need to sum the mandays left to spend for the year for each project, so in June I need to sum the mandays corresponding to the months June through December, for example.

A SUMIF seems pretty logical but since my dates are stored as text in the pivot table i can't get the criteria to work.

I have limited flexibility with the spreadsheet as i'm trying to help a colleague and he can't change much in the pivot table.

I realize i could use some ugly 12-deep nested IF formula but was hoping for a more elegant solution.

Please advise.

Thanks. :-)

I hope someone can help me with a code to handle this problem I faced.

Column AW (Order Qty) and column DZ (Order By) are the vlookup formulas that display the result.

The PartNumbers are being filtered that show only selected rows.

How can I selectively copy certain formula cells and paste as values in the same cells again ?

I heard of the "CopyMultipleSelection subroutine" but how to paste in same multiple cells as values only ?

Thanks so much in advance.

columnA columnAW columnDZ

PartNum. Ord.Qty OrderBy

row228 100-344 223 SIEMENS

row610 10-236W 173 SONY

row997 2202246 454 HITACHI

row2169 2244W 680 PHILIPS

row9907 33490 998 DELL

Example:

A1 = -3

A2 = A1 * 5

I would like the resultant to be "0", not "-15"