Under Solver, the changing cells box only alllow 200 adjustable cells. I had problem that my adjustable cells is more than 200. Is there anyway I can increase it ? maybe using VB.
View 2 RepliesI have 50 share returns for 2009 (A2:A51). I am solving for optimal portfolio by allocating weights to my 50 shares in 2009. in solver, i am changing cells B2:B51, but the criteria is that if any of the 2009 returns is greater than 75 then the weight on that share should be 0. i tried to write an if function in column C of the changing cells but solver overrides the command when i solve.
View 4 Replies View RelatedI'm trying to use solver to find the minimum value of a cell by changing 5 separate cells. These cells are subject to a set of constraints that are formulae. On top of that I have a large number of these solve's to run (The spreadsheet is creating randomly distributed data to form a Monte Carlo simulation). Therefore I am referencing the formulas using the ActiveCell, Offset() function. For some reason when running this code none of the values are changed. Also I changed the UserFinish to false so I could see what solver was doing at each solve, It stops at the correct cell (one after the final cell) but without me having to confirm it each time.
Here is the ....
Need to adjust the length of a countif formula from a separate cell
something like this ?
=COUNTIF($BB$23:$BBH21),">0")
H21 being the cell i will change value to indicate rows
I'm trying to automate the solver using a vb program. I have found this example which is just about spot on for my needs:
View 4 Replies View RelatedI want to use solver program. But when solver "changes cells" i want it to trigger my pivot tables in the workbook. So i added the code to my worksheet:
Private Sub Worksheet_Change(ByVal Target As Range)
ThisWorkbook.RefreshAll
End Sub
So when a change occurs, all my pivot tables will get refreshed and my data will change. Is solver able to trigger this event while solving an optimization problem?
how do i change cells J31 to J58 to row C3 to C30 on another sheet
View 4 Replies View Relatedl want to change picture according to the cell. For example. İf cell is "car", it will be 1.Picture. İf cell is "plane", it will be 2.picture. But Pictures will not be in the excel file folder. Picture was copied into excel file. I've added a sample file.
View 2 Replies View RelatedWhat my code needs to do: It needs to verify data in column rows say for this example columns A to C and infinite rows. It needs to verify the data in this order A1, B1, C1, A2, B2, C3 etc. (One of the part I have problems to do) It also needs to add to a row/cell "1" when certain conditions. (In the same line as the last checked data in the column "D" say for exemple last data that was checked is "A3" well the 1 must be written in "D3". The conditions are simple the first Data the macro encounters must be higher than 0.25 and all following data must be higher than 6, whenever the next data is lower than 6 or empty add 1 to the corresponding D row and restart at the 0.25 cell "checking".
View 2 Replies View Relatedthe following i have 1500 rows and around 15 columns to change. format says 1.49-2
I need it to say 01:49.2
when I highlight it and go into format cells. and change it to min:ss.0 it changes to the following 48:28.8. i have included a file for you to see
I have a worksheet full of names in column A that are "First Name Last Name", is there a VBA code that I can write so when a user inputs the name in the "First Name Last Name" format it automatically changes the format of the name to "Last Name, First Name"?
View 3 Replies View RelatedI have a question concerning highlighted cells. If I am using highlighted cells to show different headings and sections of a model, how could I change the color of a particular header and apply to all of them at once?
I.e. I have a header that is blue and sub headings that are yellow. I have this for 100 different tables. Is there a quick way to say, find cells similar to my blue header, and apply changes to all of them? I'd prefer not to ctrl + click each header to change the color.
I need to a macro to find any cells with the value 0.00e+00 (which is in scientific format - because thats the format i get from the software download) and change those cells to 00E010 and text format.
View 8 Replies View RelatedI have button in the Excel sheet.When I click the button the following code got triggered.
Sub Button1_Click() 'YieldCurveGet
Dim arrayData As String
Dim CurveString As String
'arrayData = "Test,Test1....." '(for eg)
arrayData = WorksheetFunction.RTD("OrcExcel.RTDServer", "", "YieldCurveGet", "AUD yield curve", "AUD", Null, Null, Null, Null, Null, Null, Null, Null, Null, Null, Null, Null, Null, Null, Null, Null, Null, Null, Null, Null, Null, Null, Null, Null, Null)
CurveString = Split(arrayData, ",")
i = -1
For x = 1 To (UBound(CurveString) / 2)
i = i + 1
range("C" & x) = CurveString(i)
i = i + 1
range("D" & x) = CurveString(i)
Next x
End Sub
After triggered get invoked I need to set value of "arrayData " from VBA to Excel.Iam not sure what's going wrong.I cannot see the value in the Excel.
I'm trying to build this model on Excel which has a graph in it. The graph uses several variables. Is it possible to create an adjustable percentage bar using VBA which would change these variables? look at the attached picture to understand what I mean.
View 4 Replies View RelatedI wish to be able to add adjacent cells as follows, assuming I have a number in a1. (in B1) =a1-4.
If the answer is zero I would like to change it to -4 and then continue the process for more cells to read 16 ; 12 ; 8 ; 4 ; -4 ; -8 ; -16 etc.
I am running a research experiment in triplicate. Upon generation of data for all three, I create an averaged value of the three. I then take this value and do more calculations with it on a separate sheet. I wanted to know if there was a way in which I could quickly generate formulas based on a changing reference cell.
Example Data:
V1A 28
V2A 26
v3A 30
AvA 28
V1B 20
V2B 22
V3B 18
AvB 20
Vxy represents the value of y sample, x triplicate. V1A being sample A, first triplicate. AvX is the average of the three values.
I want to be able to generate a formula that references AvA which can be applied to the cell immediately below to reference AvB. A simple example would be to square the average.
i have one row of values ie c4="-121" d4="122" e4="123" f4="124"
and h4="test" like this .
if h4="right" (i change the value test to write) i want to change the
c4,d4,e4,f4 signs positive to negative
ie c4=+121 d4=-122 e4=-123 f4=-124
i write sample code like this
I have a worksheet where I want the to change the collor of a range of cells in accordance to the value in another range of cells.
So if the value is "Gron" in cells C4:C27 then I want cells J4:J27 to return the color green. If, however, the value is "Rod" in cells C4:C27 then I want cells J4:J27 to return the color red.
I have a value list box in C1 with 4 values "Yes", "No" , "Pen" & "Con"
If a user selects Yes in C1 for example I need J1 & K1 to populate with Yes (or No if they selected No from C1)
but if they have selected or later change the value in C1 to Pen or Con for J1 & K1 to remain empty.
In spreadsheet 1 I have a bunch of data, no formulas or anything, but data I need. I created spreadsheet 2 to take parts of S1 and have set links to cells in S1 so that when the data there is changed S2 gets updated automatically for me.
My problem is that when I add or delete rows or columns to S1 it's messing up my links in S2, not showing the right data. Issue with my linking that will tie the cells together when rows or columns are added or deleted.
I've created a user form to enter some values in a few adjacent cells. When the user clicks on a button, I want several other cells to be loaded with special formulas that refer to the new values. This happens on a row by row basis. First, a working formula in that cell which is AB5 is:
Code:
=IFERROR((IF(AK5
I have no knowledge of VB programming and the conditional formatting seems limited. I want to change to the color of cells to yellow if the cell value is greater than 80 but less than 99, and if it is 100 or greater to fill red. Is this possible and if so can it be applied to a range of cells?
View 3 Replies View RelatedI am trying to create a model that has a mixture of conditional formatting and data validation formulas within it, but have run into a problem in one area. Essentially, cell E10 is a Validation which pulls a drop down list of names. One of the options in this list is "Other".
I want to create a VBA code to make sure that cell E12 will get rid of any input, turn grey (color index = 15), and potentially lock (if possible!) if the "Other" is chosen by the user in cell E10. If any other value is chosen for cell E10, Cell E12 needs to remain unlocked and white (color index=2).
I have been asked to look at moving a very large set of pricing sheets from Excel 2000 to Excel 2003. In the progress of this I have found that the functionality for a UDF in a cell to change/recalculate any other cells has been removed in Excel XP, whereas it was available in Excel 2000. The amount of work required to refactor the code is very large, and before starting on it it would be good to find out if there is a workaround.
The Sheet works in the following way:-User enters values, clicks a button to run a Macro,-Macro calculates all the ranges required to return a price-In many of the cells it is calculating are Functions which go and look at a large number of cells to calculate the price, sometimes writing to other Cells and sometimes having to recalculate other cells-As it is such a large sheet we can't simply calculate all cells before we tell it to price, as they are not all needed and so aren't calculated unless needed for performance reasons.It would be a lot of work to move the functionality from the Functions to the initial macro call, though this would solve the problem. Is there any other workaround?Macro security is set to low.The Errors I receive are the following:The UDF will hit a line like Range("DataRange").Calculate and this will raise a "Calculate method of Range class Failed" error. If it hits a line like Range("DataRange").Value = 1 this will raise a "Application-defined or object-defined error"If it hits a line like ActiveWorkbook.Names.Add Name:="This_Name", RefersToR1C1:="=Sheet1!R8C2" then it also raise a "Application-defined or object-defined error".Any of these run from a UDF in Excel 2000 works.I have a sample workbook if this isn't clear
I have a cell, D5, which is the sum of three other cells, A5 B5 and C5. (all currently empty). Cells A1 through C4 are filled with various numbers.
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?
I have a table that lists staff according to where they are located, all adjusted using drop down lists, problem is that some locations have 5 staff and some up to 40, so when I set up a macro to print the area, those with 5 consultants look very small because the page area is so big. how can I adjust the print area dynamically so it only prints to 1 row below the last line with text?
View 9 Replies View RelatedI currently have a spreadsheet that refers to a different spreadsheet, with different expenses each month, with the months going across from A,B,C,D etc.
Because the formulae on the expenses sheet refers to cells such as O13 O15 O27 etc, at the start of the next month the 'O' has to be changed to a 'P' to give P13 P15 P27
This doesnt sound much but there are over 10 different spreadsheets that are all currently changed manually. Is it possible to have a function referring to a cell containing 'O' which when changed to 'P' changes all the formulae to refer to the cell next to the original.
EG Instead of O13 it would be [variable]13
Essentially this will mean that a master sheet will be able to change all the excel spreadsheets in one go.
Say I have this data in worksheet
A
B
C
D
E
14/1/2012
1
2
3
4
15/1/2012
5
6
7
8
And this in worksheet 2
=A2
=B2
=C2
=D2
=E2
=A2
=C2
=D2
=E2
=B2
=A2
=D2
=E2
=B2
=C2
Now what I want to be able to do is when I add a new row to worksheet 1, in this case row 3, then the references in worksheet 2 either to automatically (or manually do it in one simple go) increase by one and now refer to the new row. So the references now become =A3, =B3, =C3 etc.
When I enter this : =now(). it gives the local date and time in my PC in that exact cell. assume it is in (A2). when I enter in Any cell in the spreedsheet the sam formula =now(). then the previous formula (A2) is changed to the new date and time.
View 5 Replies View Related