Goal Seek VBA Syntax - With / Without Parentheses
Nov 25, 2008
I've been playing around with GoalSeek in VBA because of this thread and found something about the syntax that I don't understand.
If I want to find out whether GoalSeek worked, I can use
Ok = Range("B1").GoalSeek(Goal:=10, ChangingCell:=Range("A1"))
Debug.Print Ok
This requires parentheses around the GoalSeek arguments.
If I just want to do the GoalSeek, then I need
Range("B1").GoalSeek Goal:=10, ChangingCell:=Range("A1")
with no parentheses. Why? The Help says that GoalSeek returns True if it works, and the proforma syntax includes parentheses, but then the only example uses the syntax without parentheses.
View 9 Replies
ADVERTISEMENT
May 30, 2009
Trying to automate Goal Seek using variables for the three arguments. It's working when I define the cells references & values within the code, but cannot figure out how using variables. I'm sure the example below is incorrect, but will give an idea what I need to accomplish:
GoalCell = Worksheets("Parts").Cells(DestRowID + 10, DestColID)
Value = Worksheets("ICA").Cells(CopyRowID + 13, CopyColID).Value
ChangeCell= Worksheets("Parts").Cells(DestRowID + 9, DestColID)
GoalCell.GoalSeek Goal:=Value, ChangingCell:=ChangeCell
View 9 Replies
View Related
Sep 28, 2006
CSS = P divided by S.
PS Required is a number that needs to be added to both S and P to achieve target of 83%
The answer to the below = 5. But i dont know how to get it using a formula
(If 5 is added to S and P both, S will become 36 and P will become 30; 30/36 = 83.33%)
Name S P CSS Target PS required
Adam 31 25 80.65% 83.00% ?
View 9 Replies
View Related
Aug 15, 2008
I need to goal seek cell B1 to zero by changing C1.
Then, I need to goal seek cell B2 to zero by changing C2.
Then, I need to goal seek cell B3 to zero by changing C3.
...................
...................
I need to goal seek cell B100 to zero by changing C100.
I don't want to have to perform 100 different goal seeks. Is there a way to do them all at once?
View 10 Replies
View Related
Apr 6, 2013
Goal seek function
Code:
Worksheets("Sheet1").Range("Polynomial").GoalSeek _
Goal:=15, _ ChangingCell:=Worksheets("Sheet1").Range("X")
Is it possible to modify this such that I can use variables? For example, the variables are 'left term', 'right term' and 'sigma max' .
I want to set the value of the following equation 'left term - right term' to 0 by changing variable 'sigma max' Everything is done on the userform and not in the spreadsheet.
(Note: In spreadsheet format, the above query is equivalent to setting a cell which has a formula to 0 by changing the value in another cell, fairly simple).
View 3 Replies
View Related
Jun 4, 2014
Say employee John Doe has a salary X. Giving him a salary raise Y (euro/dollar) induces a pension premium to be paid by the employer, on that amount.
The premium is (fixed) 17% of the part above a (fixed) treshold value of 3.750. On the part of the raise below the 3.750, the pension premium is (fixed) 4,35%.
E.g. having a salary of 3.600, with a raise of 400.
4,35% of (3.750 - 3.600 = 150) equals 6,525
And 17% of the remaining 250 equals 42,500
So the total premium is 49,025
But, I want to give a raise with a total cost of no more than 400. So, I use goal seek to find a raise that, together with te pension premiums equals 400.
In the example, with 3.600 as a salary to start with, that raise would be 358,10 Because the part below 3.750 stays the same and induces a premium of 4,35% of 150, being 6,525 and the remaining part is 208,10 at 17% is 35,377.
Proof: 358,10 + 6,525 + 35,377 = 400
The question is: is there a possibility to have a formula that calculates the (e.g.) 358,10 based on a salary to start with (e.g. 3.600), an aimed total cost (e.g. 400) and the fixed constants 3.750, 17% and 4,35%?
View 3 Replies
View Related
May 24, 2008
I'm trying to understand how complex numbers are handled/processed in Excel.
As related to my application, an interesting exercise would be to use Goal Seek w/s command to find the roots of the equation:
X^2 + 4 = 0
setting the (rounded) value in cell A2 to 0 by changing A1
A1:: 1+i
A2:: =COMPLEX(ROUND(IMREAL(IMSUM(IMPOWER(A1,2),4)),6),
ROUND(IMAGINARY(IMSUM(IMPOWER(A1,2),4)),6))
Obviously a conventional or direct use of Goal Seek wouldn't work since Excel treats complex numbers as text.
Perhaps, one should use Goal Seek twice in this case:
first: find the coefficient "a" for IMREAL(A2) = 0
second: find the coefficient "b" for IMAGINARY(A2) = 0
and the root would be "a+bi".
View 9 Replies
View Related
Jan 22, 2009
I need to use a Multiple Cell Goal Seek.
I used the code from this site.
Here is the ....
View 9 Replies
View Related
Feb 12, 2010
Is there a way to "copy" Goal Seek acroos multiple rows?
I have used Goal Seek in the first row. However this spreadsheet is several
hundred lines long and using it like this is not practical.
I want to seek a goal value of 30% on the cell that I called “c”, and I want the result of the goal seek to be put in the cell situated on the same row, 6 columns before.
This is the code that I tried to write, registering and saving the “goal seek” operation for one row.
Is it clear enough?
What i wrote:
Sub Macro1()
'
' Macro1 Macro
' Macro recorded 12/02/2010 by Whirlpool Europe Srl
'
' Keyboard Shortcut: Ctrl+w
'
On Error Resume Next
For Each c In ActiveWorkbook
If cvalue < 0.3 Then
Range("c").GoalSeek Goal:=0.3, ChangingCell:=Range offset(c,0,-6)
Next c
End Sub
View 9 Replies
View Related
Oct 18, 2003
I have created the following in an effort to automatically goal seek whenever changes are made to the cell C2.
Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Row = 3 And Target.Column = 2 Then
Range("D19").GoalSeek Goal:=Range("C2").Value, _
ChangingCell:=Range("D3")
End If
End Sub
As far as I know, the Macro is running all of the time? When I use goal seek manually it works fine. Assuming the above is running, when I change the value in C2 nothing happens. Does the fact that I have a three sheet workbook make any difference? I am sort of lost. By changing the value in C2 I want Excel to change the value in D19 to match C2 by adjusting D3.
Application.EnableEvents = False
Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Row = 3 And Target.Column = 2 Then
Range("D19").GoalSeek Goal:=Range("C2").Value, _
ChangingCell:=Range("D3")
End If
End Sub
Application.EnableEvents = True
Doesn't seem to work either. However, I don't really know if other settings in the workbook are correct.
View 9 Replies
View Related
Apr 1, 2013
I wanted to set up a macro to automate goal seek on a range of cells. I've attached a short extract below of the spreadsheet I'm working on (the full one has a few thousand lines of data) which I hope illustrates what I'm trying to do.
jan
feb
mar
apr
may
jun
jul
aug
Avg
New Avg
0
0
0
0
0
0
0
0
0
0
34
20
43
43
43
43
43
43
35
39
[Code] ...........
View 3 Replies
View Related
Jan 27, 2008
I am trying to get Goal Seek to seek a value on each row as such:
Project 1 : Value 1 : Goal Seek using Value 1
Project 2 : Value 2 : Goal Seek using Value 2
etc....
How do I create one macro that will pick up Value 1, 2 etc and then set the Goal Seek to that value automatically, since Value 1, 2, etc will already be available in a cell? Alternativley there could be a macro that just runs that a Goal Seek for all the rows at once?
View 2 Replies
View Related
Nov 8, 2011
I am trying to setup a macro to automate goal seek to calculate the required payment to meet a 12% IRR Lookback at the end of a 10 year period. The problem I am running into is that in multiple scenarios the required return is reached without any excess payment at the 10 year mark so it is returning a negative payment in the goal seek cell. In other words it's adjusting what might be a 13% IRR to a 12% IRR (the minimum return) by showing an expense at the end of the term. I need a way to set it to goalseek with a minimum of 0.
View 3 Replies
View Related
Nov 7, 2012
I'm trying to build a macro that iteratively runs a goal seek, then reduces a certain value (Let's call it "Bogey") in a different cell by a small amount (note that changing Bogey will affect the desired goal seek value), then runs the goal seek again, reduces Bogey by a small amount again, etc etc, and keeps running in this loop, with the restriction on this loop being that a different cell, affected by Bogey--let's call it "Constraint"--doesn't drop below a certain amount.
View 5 Replies
View Related
Nov 20, 2008
Is there a way to use goal seek in VBA that won't trigger a full model calculation?
I would like to (somehow) perform goal seek, but only recalculate range("A1:B50") say on a single worksheet (where both the input value and the final calculated value is included in that range), as opposed to calculating the whole model inbetween each iteration.
Also, all dependencies between the input value and the final calculated result are contained in that range.
View 5 Replies
View Related
Aug 5, 2009
wish to write an VB macro in Excel (2003) that populates a 6 row by 10 column matrix (for a 3 D graph) by inserting values from matrix row column headers in cells in another worksheet and using goal seek to derive a value which will be pasted in the respective cell in the 6X10 matrix
Specific example: Matrix (Row Headers A2 to A7, Column Headers B1 to K1, Cell values are in B2 to K7). Please note row and column headers are numerical values since this is for a 3D graph
Macro to be written
Pick a value (numerical) from row header A3 in Sheet A and paste it in cell B6 in another sheet (Sheet B). Pick anothet value (numerical) from column header D1 in Sheet A and paste it in cell B7 in Sheet B.
Run a goal seek scenario such that cell S10 in sheet B is equal to 50 by changing cell G8 in Sheet B
Copy the value chosen/optimised by Goal Seek from cell G8 in Sheet B and paste it in Cell D3 (which is the relevant cell for the values for intersection of headers A3 and D1) in Sheet A
Macro should populate all cells in the matrix in Sheet A (matrix cells to be populated are B2 to K7) in a similar fashion
The macro should be locked in to the cells which are either being copied from or pasted into so that it still references them even if new columns or rows are inserted.
View 10 Replies
View Related
May 8, 2007
I have the total sales for a company. Then I have four specific segments that fall under those sales. Two out of the four segments I have exact sales figures for. The other two segments I just have the year-end sales and the dollar increase in each quarter. Is there a way to solve in excel for the missing quarters. So each of the four segments would have to add up to the total sales for both the quarter and the year. I've attached my example spreadsheet too. There are two cells missing highlighted in blue but i'm going to try to obtain that data. provided i fill in those two blanks, is there way to back into the rest of this spreadsheet?
View 3 Replies
View Related
Feb 17, 2010
Any function in Excel, that would allow me to have a unit cost price (for example: 0.5432) and then for a table of figures containing the pack sizes to determine the correct unit price that would allow all pack costs to be at 2 decimal places. I have included an example below:
My spreadsheet would look like the following:
Cell A1 (Unit Cost Price) = .5814
Cell A3 (Pack of 75 units) = .5814*75 = 43.605
Cell A4 (Pack of 80 units) = .5814*80 = 46.512
Cell A5 (Pack of 100 units) = .5814*100 = 58.14
and so on
I need some way of making cell A1 change to a value that will cause all cells A3 to A5 to be 2 decimal places or less.
View 3 Replies
View Related
Jul 19, 2006
I´m writting a macro. It works find until a certain point. When I want to change some outputs of the macro without changing the syntax, it display an error mesage while runing the macro. It says Else without If. Which is quite disturbing because the Else was not creating any problem before. Here is my macro before I changed the conditions (this one work nicely)
Sub Copy_Sheet_Beta()
Set wba = ActiveWorkbook
On Error Resume Next
If IsWorkbookOpened("Projekt.xls", "C:Documents and SettingsfrederikSkrivebordRedd Barna") Then
Workbooks("Projekt.xls").Activate 'In case open, just activate "Projekt"
Else
Workbooks.Open Filename:="C:Documents and SettingsfrederikSkrivebordRedd Barnaprojekt.xls"
End If
Set wb = Workbooks("Projekt.xls")
wb.Activate
If Not SheetExists(wba.ActiveSheet. Range("C1").Value) Then
MsgBox "overall doesn't exist!"
Else........................................
View 2 Replies
View Related
Nov 9, 2008
I work in school and and I have to enter test marks of students in excel 2003.
Here is my question:
If student's mark is below 40(e.g. 32), I want to indicate it by (32).
View 4 Replies
View Related
Feb 5, 2014
What does the (3) and (1) mean in the parentheses?
The (3) is sloppy coding instead of using the enumeration (xlUp), which you could actually read. The (1) means the first element of the range, which is wholly redundant; it already refers to a single-cell range.
View 1 Replies
View Related
Apr 2, 2013
I have a long list beginning either with 4 numbers or 4 numbers followed by parentheses and a single number between them. I have this formula to extract the data between parentheses:
Code:
=MID(A7,FIND("(",A7)+1,SUM(FIND({"(",")"},A7)*{-1,1})-1)
What can I do to eliminate a "#VALUE!" result for cells that do not contain parentheses?
View 9 Replies
View Related
Dec 10, 2009
I am using excel 2007:
I have created the following formula (with some help from this forum), but I am getting an error message saying I am missing a left or right parentheses.
View 9 Replies
View Related
Sep 15, 2006
I need to come up with a formula that will pull the data located within parentheses. Here is an example below:
CHRISTINE DALEY (497998)
LINDA DALEY (503094)
STEPHAN DANIEL (100360949)
View 7 Replies
View Related
Oct 20, 2012
I want to do something similar but it is looking for values between parentheses "()" and won't work with the VB suggested. I've seen some other formulas that do find the content within the parentheses but returns #VALUE! if there is not a parentheses in the field. I'd like it to copy the original value of A2 into B2 for those instances. PROBLEM: Database with First Names in A2 where some have "nicknames" entered within () and I need a clean column of values to use. "IF A2 contains () then copy (contents) into B2, else copy A2" is how I'd write in english... not sure how to do correctly in code/vb.
VB:
Sub PartialText()
Dim MyValue$
Dim ChangedValue$
[Code]....
View 5 Replies
View Related
Jan 3, 2013
An externally generated CSV file gives us one column which shows a location, year and then individual names in brackets, like so:
London 2013 (Firstname Lastname)
Delhi 2012 (Firstname Lastname)
I'm wondering if it is possible to separate out the text before the parentheses, so that I can show "London 2012" (for example) in another cell. The number of characters changes from row to row, so using the LEFT and RIGHT functions doesn't quite do the job.
View 4 Replies
View Related
Jul 15, 2002
I have been able to format single cells to display negative percents (Budget to Actual hours), but I cannot copy the formatting to cells with positive percents without eliminating the format style I want. [I need to display, with the parenthesis, (13.6%)for negative results, but say, 18.6% for positive results.] When I copy the correctly formatted cell (13.6%) to another cell with a positive result, it sets the display to general formating.
As I have over 25 rows of data to compare against 62 projects and 12 programs, with each value potentially changing from one analysis to the other, I am looking for a method to automatically change the "look" of the results. I have looked at conditional formatting, but have had no indication this will do what I am looking for.
View 3 Replies
View Related
Oct 11, 2007
I tried to use the find and replace function to delete different numbers from different cells (all in one column) but the problem is that I want to delete different kind of numbers.
Let me give you an example:
Starting point:
Cell A1: Bookrunner: Barclays Capital(368.726m)
Cell A2: Bookrunner: DBS(368m)
Cell A3: Mandated Arranger: Commonwealth Bank of Australia (Singapore)(367.163m)
Cell A4: Mandated Arranger: DnB NOR ASA (Singapore)(367.163m)
I want to reach here:
Cell A1: Bookrunner: Barclays
Cell A2: Bookrunner: DBS
Cell A3: Mandated Arranger: Commonwealth Bank of Australia (Singapore)
Cell A4: Mandated Arranger: DnB NOR ASA (Singapore)
(I don’t want the information in “()” like “(Singapore)” to be deleted! Only the “()” which contains numbers)
I used the “find and replace” function to delete ever single number, but I think it’s a little unprofessional to do so!
View 8 Replies
View Related
Jun 2, 2009
I'm trying to nest if statements that also include "and" and "isblank" factors. The following formula isn't working, and I'm not sure if it's because of my use of isblank or lack/placement of parentheses.
View 3 Replies
View Related
May 22, 2014
Any formula for removing text within multiple sets of parentheses?
I would like to take something like this: Compared with placebo, dimethyl fumarate was shown to be effective in the treatment of patients with MS in the phase 3 DEFINE (Gold R et al. N Engl J Med. 2012; 367:1098-1107) and CONFIRM (Fox R et al. N Engl J Med. 2012;367:1087-1097) trials. Common adverse effects associated with dimethyl fumarate that were observed in these trials included flushing and GI events, as well as decreased lymphocyte counts and elevated liver aminotransferase levels.
and have it read: Compared with placebo, dimethyl fumarate was shown to be effective in the treatment of patients with MS in the phase 3 DEFINE and CONFIRM trials. Common adverse effects associated with dimethyl fumarate that were observed in these trials included flushing and GI events, as well as decreased lymphocyte counts and elevated liver aminotransferase levels.
I wish to automate this task so formulas (if possible) are preferred. Totally cool with a multi-formula, many-columned solution - as long as this task is automated.
NB: Each cell will contain different text, so the sets of parentheses will appear in different places. The number of parenthetical sets may also vary from 1-5.
View 9 Replies
View Related
