Free Microsoft Excel 2013 Quick Reference

Goal Seek VBA/Macro & Data Table

Hi there,

Ive set up a Macro that calculates the Cash Flow yield when i change certain assumptions. It works fine. I would now like to integrate it into a data table that i show several characteristics of a bond. I'm referencing the yield result of the goal seek in the data table. I'm of course getting only one solution to all 10 scenarios i have set up (4.81%).

Does anyone know how to set up a data table where you can reference the result of a goal seek? I can't figure this one out. Let me know if you need further information.

Thanks
Darius


I am trying to use Goal Seek in a Pivot Table but Excel won't let me, any
suggestions on how to get it to? Thanks,
TJ

I am using Goal Seek to compare and optimize against an identical table I manually assign pickers.
My VBA code is

	VB:
	
     ' OptimizePicks Macro
     ' Optimize Using Goal Seek
     '
     ' Keyboard Shortcut: Ctrl+Shift+O
    Range("G20").Select 
    Range("G20").GoalSeek Goal:=Range("$G$16").Value, ChangingCell:=Range("E20") 
    Range("G21").Select 
    Range("G21").GoalSeek Goal:=Range("$G$16").Value, ChangingCell:=Range("E21") 
    Range("G22").Select 
    Range("G22").GoalSeek Goal:=Range("$G$16").Value, ChangingCell:=Range("E22") 
    Range("G23").Select 
    Range("G23").GoalSeek Goal:=Range("$G$16").Value, ChangingCell:=Range("E23") 
    Range("G24").Select 
    Range("G24").GoalSeek Goal:=Range("$G$16").Value, ChangingCell:=Range("E24") 
    Range("G25").Select 
    Range("G25").GoalSeek Goal:=Range("$G$16").Value, ChangingCell:=Range("E25") 
    Range("G26").Select 
    Range("G26").GoalSeek Goal:=Range("$G$16").Value, ChangingCell:=Range("E26") 
    Range("G20").Select 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
This works, but I need some kind of error checking if the cell value is zero because then with goal seek I get ########### in the cells with zero because my code is hard coded. What do I need to enter for if the cell is 0 or blank, it skips it and go on to the next. I attach a sample to make it easy. Any help would be greatly appreciated. Thanks

Does any one know of an add-in or macro that will allow the goal seek ability
on a data point to work in excel 2007 the way it did in the prior versions
of excel?

The question is stated as:

You need to plot the series of x,y points that solve the equation
exp(x+y)=(x^2)/y - y^2
For the range of x values shown below. Set up the Excel worksheet to solve this problem using Goal Seek for each row. Write a VBA macro program that uses a FOR/NEXT loop that can be associated with the command button to solve the entire problem. You can use the extra columns if desired.

NOTE: The problem also has screen shot of excel looking like this:

[SOLVE FOR Y] <------Button
A.....B...C...D
4.....x....y
5.....1
6.....2
7.....3
8.....4
9.....5
10...6

Currently What I have is:

Option Explicit

Sub SolveFory()

dim x as double

dim y as double

size = range(B5:B10).cells.count

For B=1 To size

Range(B5:B10).cells(B)=0

Next B

End Sub

I know i'm missing the equation, but im not sure how to implement it. Also I'm not very good with VBA so im not really sure how much of what I already have is right. I could really use some help on this cause i don't understand it that well.

Thanks!

Brandon

Can someone please tell me what the VBA would be for the following question:

You need to plot the series of x,y points that solve the equation
exp(x+y)=(x^2)/y - y^2
For the range of x values shown below. Set up the Excel worksheet to solve this problem using Goal Seek for each row. Write a VBA macro program that uses a FOR/NEXT loop that can be associated with the command button to solve the entire problem. You can use the extra columns if desired.

NOTE: THe problem also has screen shot of excel looking like this:

[SOLVE FOR Y] <------Button
A B C D
4 x y
5 1
6 2
7 3
8 4
9 5
10 6

I appreciate the help!
Brandon

Hi

I have written several macros that conatin the procedure "Calculate". I have set my Excel global calculation option to "Automatic except for data tables" however, the data tables in my workbook still calculate when these macros run. Note: the global calculation option remains on "Automatic except for data tables" when I break the code so this doen't seem to be the problem. I need all sheets in the workbook to calculate but don't need the data tables to calculate. What am I doing wrong?

Extract from code:

For Row = 106 To (n + 105)
Calculate
a = Range("Probability_Project_IRR_ungeared_Value")
Cells(Row, 2) = a
b = Range("Probability_Project_Equity_IRR_Value")
Cells(Row, 5) = b
c = Range("Probability_Project_Equity_IRR_Value")
Cells(Row, 8) = c
d = Range("Probability_Project_Equity_IRR_Value")
Cells(Row, 11) = d
e = Range("Probability_Project_Equity_IRR_Value")
Cells(Row, 14) = e
f = Range("Probability_Project_Equity_IRR_Value")
Cells(Row, 17) = f
g = Range("Probability_Project_Equity_IRR_Value")
Cells(Row, 20) = g
Next Row

Any help would be greatly appreciated. Thanks.

This is sort of an amendment to my previous thread.

I figured out that GOAL SEEK is the solution to my problem. Now I need to find a way to apply it automatically to specific ranges.

Here's the scenario. In the screenshot, you notice several columns with headings of "28#", "32#", "35/80" and "50#".

I want to be able to enter a number in any of the cells in columns AJ, AM, AP or AS (from row 8 to row 35) and have a code in the sheet automatically do a GOAL SEEK to change the corresponding row in column I (in rows 8 to 35) to match the number in cell AF8 by changing the corresponding row next to the cell where the original number was entered.

For instance, if the sheet in the screenshot is blank, and I enter 1295.58 in cell AP15, I want GOAL SEEK to automatically run (according to the specifics above) and change cell AO15 to make I15 = AF8.

If I enter 272.50 in cell AJ8, I want GOAL SEEK to automatically run (according to the specifics above) and change cell AI8 to make I8 = AF8.

This should be a pretty simple code, I just don't have the knowledge of how it should be stated.

Any help is appreciated!

http://img.photobucket.com/albums/v6...EXCELIMAGE.jpg

I currently have the below macro set up so that when data in my spreadsheet changes the macro automactically runs in 3 cells. But it only does this when i go to the sheet i have set up the macro in. How do you make it where it is global so that i don't have to go into the sheet where the macro is located to get it to run?

Private Sub Worksheet_Activate()
Range("I18").GoalSeek Goal:=Range("C3").Value, ChangingCell:=Range("f4")
Range("p18").GoalSeek Goal:=Range("C3").Value, ChangingCell:=Range("f7")
Range("h18").GoalSeek Goal:=Range("C3").Value, ChangingCell:=Range("f2")
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Range("I18").GoalSeek Goal:=Range("C3").Value, ChangingCell:=Range("f4")
Range("p18").GoalSeek Goal:=Range("C3").Value, ChangingCell:=Range("f7")
Range("h18").GoalSeek Goal:=Range("C3").Value, ChangingCell:=Range("f2")
End Sub

Also, as you can tell this is a Goal Seek macro. This macro calculates the Yield of cash flows that change when different assumptions are used. What i would like to do with this is have a data table reference the 3 results from above so that i can add these yields to each of my 10 scenarios in my data table to look something like this:

HTML Code:
Data Table
                     Yield1        Yield 2     Yield 3
Scenario 1    
Scenario 2
Scenario 3
I have the data table already setup like this with other non goal seek items and it works great, but i should would like to add yield.

Thanks

One of the guys I work with came up with an interesting question.

He has a Data Table and he'd like to have a goal seek run before each Data Table element is used in the calculation. I tried a simplified example. I created a simple Data Table and made the results dependent on a value that I was Goal Seeking. I created a macro to run the Goal Seek and I created a Sheet Change event to trigger the Goal Seek macro when the elements in the Data Table were substituted in the cell in question. The sheet change event did not trigger so I assumed that this was similar to a formula result change not triggering a sheet change event.

My friend would prefer to keep this as simple as possible and would like to keep the Data Table approach as that is what the client had to start with and he would rather not change that. I have indicated that this is probably not possible and that he should plan on some VBA code to loop through the elements in the Data Table and call the Goal Seek code within each iteration.

Any ideas? Thanks.

Dear all,

I would certainly be grateful if someone is able to provide me with some guidance as to how to proceed with the following problem. I am afraid that I am not currently familiar with VBA but that it is certainly on my to do list!

Basic model / probelm overview:

I am rebuilding the front end to a very detailed and complex financial model that basically uses a goal seek to calculate a price in one of the selected following hrs/kms/ltrs/tonnes. By turning off certain model inputs and using the goal seek the model calculates a second kind of price (however this price can only be per hour, and thus a per hour selection must be made before goal seeking).

In view of the above I am seeking to put together a two variable data table that allows the second price to be displayed. This by process requires reference to the two relevant variable drop downs and subsequently the goal seeking result for each possibel outcome. I am afraid that I am not sure how to automate or even calculate this. My efforts thus far are below:

Data Table Inputs Unit RatetonneStandby Rate OnlyNo2Data Table$ 1.61 YesNotonne1.61 1.61 hrs1.61 1.61 km1.61 1.61 ltr1.61 1.61 1.61 1.61

where the dark blue function $1.61 references the output cell of the goal seek.

and the Unit rate tonne links to a drop down on another sheet.

If any one is able to offer any advice and or guidance I would certainly be very thankful.

Regards,
Chris

I am fairly new to VBA so this is probably fairly simple! I am looking to create an automated goal seek macro which calculates a tariff cost based on a upfront capital costs (I have a cashflow model with variable inputs). I have capex values (named capex_1, capex_2...capex_12) and want to generate tariff costs (cap_fee_1, cap_fee_2...cap_fee_3) based on a desirable rate of return (named req_rate_of_retun)

I then want my results to appear in a table at the bottom of my inputs sheet.

capex_1 cap_fee_1
capex_2 cap_fee_2
etc.

So far I have got this (which I recorded and then amended)...


	VB:
	
Range("D17").Select 
ActiveCell.FormulaR1C1 = "=capex_1" 
Range("D18").Select 
Sheets("Cashflows").Range("D4").GoalSeek Goal:=Range("req_rate_of_return"), ChangingCell:=Range( _ 
"D10") 
Range("D10").Select 
Selection.Copy 
Range("cap_fee_1").Select 
ActiveSheet.Paste 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I now just need to repeat this for capex_2, cap_fee_2 etc. etc. Currently my best option is to copy and paste the code 12 times and manually change the capex and capacity fee names - is there a quicker way to do this?

I have attached a simplified version of my sheet to illustrate what I mean.

Many thanks for your help.

Alice

Hello everyone,

At the moment I am busy doing a research for my studies. In this research I need to make use of the Goal Seek function in Excel:

Set cell A1 (a formula) equal to cell B1 (input) by changing cell C1 (number)

However, I have to do this for 1000 individual cases in the same sheet but with empty rows between them (In other columns there is data on these rows). To explain my problem further I attached an example file of what I mean.

example.xls

I made the following VBA code (used on the example file):


	VB:
	
[COLOR=#0000ff]Sub[/COLOR] goalseek() 
[COLOR=blue]For Each[/COLOR] cll [COLOR=blue]In[/COLOR] Range("E5:E12") 
[COLOR=blue]If[/COLOR] cll.Value  "" [COLOR=blue]Then[/COLOR] cll.goalseek Goal:=ā€¯$C$1ā€¯, ChangingCell:=cll.Offset(, -3) 
[COLOR=blue]Next[/COLOR] cll 
[COLOR=blue]End Sub[/COLOR] 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
However, I get an error on the following part and I do not know what the problem is


	VB:
	
[COLOR=#0000ff]Then[/COLOR] cll.goalseek Goal:=ā€¯$C$1ā€¯, ChangingCell:=cll.Offset(, -3) 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Can somebody help me?

Regards,

Eureka

I need some help for VBA macro for Ecxel: just to extract data.
There is one workbook with multiple sheets; I just want extract some data from this sheets and put this data into separate worksheet, as table.
i've attached the sample.

Data which need be extracted is inside red frames.
The only problem is that, probably, some the location of some data may differes a little: i.e. in cells I35, I38, I39 -
due formattings problem it can migrate around this cells(near ~I36, I39, I40). So I just want that macro show empty cell in result table(not error) if find this incorrect (empty) cell in some workshets.

Thought it looks not difficult, i still can no solve this myself.
Thanks.

Hello
My problem is pretty much illustrated on the attached example.
All I need is a Macro/VBA button that will consolidate 2 different Tables that are on 2 different Sheets. Let’s call them Table1 and Table2.
The end result must be a Table3 (on a created Sheet3) that contains all the columns from Table1 that were also found on Table2. And same goes for the rows.
The example better illustrate the main issues that I have when dealing with my data. Please ask me any question if you have any trouble understanding it!
I have tried something like:
VLOOKUP(columnSheet2;Sheet1;Match(columnSheet2;Sheet1);FALSE)
Along with
HLOOKUP(rowSheet2;Sheet1;Match(rowSheet2;Sheet1);FALSE)
But I am no VBA/Macro genius! And I have been pondering on this issue for a few days! So I ask for your help!

Unfortunately the attachment didn't work! So I am posting my file as a GoogleDocs...here is the URL:
https://spreadsheets.google.com/ccc?...thkey=CNXyztwJ

Thanks in advance!

Hi

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.

Can any body help?

Thanks,

Chris

Hi,

Having thoroughly (I think) searched the internet for solutions I'm thoroughly stumped.

I'm using Excel 2003 and Access 2003

I have a VBA Macro that selects, trims and then copies a range of data in my spreadsheet. It then creates a new database named according to the value in cell C2 in the spreadsheet. What I am ultimately trying to do is then insert/paste the copied data into a new table (called SURVEY) in the newly created Access database.

I can get, as you will see, the newly created Access database open but when I manually paste the data in (Edit>Paste), I don't get the option 'Does the first row of your data contain column headings?', which my data does not and the data then pastes into the new table incorrectly. If I have to manually paste the data, I have to close access down and re-open it before I get that option.

My knowledge of VBA / Macros is limited; I'm taking a 'throw myself in the deep end and feel my way around' approach but will apply myself to learning the ins and outs of any solutions proposed.

Desired outcomes: One of two :-
Either - the last bit of code to automatically insert the selected data into a new Access table
Or - A way of ensuring that my manually pasted data isn't treated as if the first row contains column headings.

Code I am using:
(disclaimer: I've written very little of this myself, the majority is cannibalised from the results of my internet searches)

Code:
 
Sub InsertInto()
    Dim dbConnectStr As String
    Dim Catalog As Object
    Dim cnt As ADODB.Connection
    Dim dbPath As String
    
    For Each CELL In [B1:C360]
    CELL.Value = WorksheetFunction.Trim(CELL)
    Next CELL
    
    Range("A1:I360").Select
    Selection.Copy
    'Set database name here
    dbPath = "D:Uploaded" & Range("C2") & ".mdb"
    dbConnectStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbPath & ";"
    'Create new database
    Set Catalog = CreateObject("ADOX.Catalog")
    Catalog.Create dbConnectStr
    Set Catalog = Nothing
    'Open Access and make visible
    Set oApp = CreateObject("Access.Application")
    oApp.Visible = True
    'Open Access database as defined by LPath variable
    oApp.OpenCurrentDatabase dbPath
End Sub
Thank you in advance for any help you're able to give.

Hi,

I have a macro that does mulitple Goal Seeks. The cells that the goal seek function calls are filled in using a vlookup function that returns numbers based on values corresponding to dates. However these cells will on occassion return "#VALUE!" because the information to look up is not in my table of dates. This is okay. My problem is the goal seek macro fails when the cells contain "#VALUE!" . I was wondering if I could use an if statement to work around this like below....

If Cells("AF8").Value "#VALUE!" Then

Range("AF9").Select
Range("AF8").GoalSeek Goal:=Range("AF4"), ChangingCell:=Range("AF9")

End If

This gives me an error at the If statement. Any ideas. Thanks for any help.

TW

Hi guys and girls,

In order to impress my CFO, who can't figure it out, I put myself in your hands, since I can't figure it out either, but he doesn't know that...

Here's what I want to do:

We have built a building for let's say 1,000,000. We want to know what the rent is that we have to ask in order to make the 1,000,000 as well, earn it back.
So I need the interest rate (5%), the number of years in which I want to earn it back (10), and the yearly increase in rent (2,5%).

I have made a table saying
year 1: rent / (1+interest rate) = net present value (NPV)
year 2: (rent + increase) / (1+interest rate)^2 = NPV
and so on for the next 8 years.

So I made me a nice little VBA

Code:
where B5 is the rent I have to charge. 

Now here's the question, coz this is too limited
Can I automate the goal seeking thing where all things are variable? I want to be able to change the years, the interest rate and the increase in rent, which in the end comes back with the rent I have to charge.

I can't figure it out. Can anyone of you wizzards?

Greetz, Elmacay

I'm using Goal seek within some VBA to automate a "what if" kind of scenario. When I do this manually everthing works fine, but when I use the VBA code it crashes occasionally - i.e. Goal seek cannot find a solution (even though it does find a solution using the same data when I run it manually).

Has anybody else experienced this or can anybody identify a foolproof strategy to get Goal seek to work using VBA?

Ta very much ...

I am looking for a way to automate the what-if-analysis tool found in Excel 2007.

I am looking for a macro that opens Goal seek (Data tab>What if analysis>Goal seek) Inputs Set cell as E5, To value as 0 and By changing cell as D2. I then want this to run, storing the goal seek value of cell D2 into cell H5.

I want the process to then repeat for the next cell down i.e. E6, keeping To value as 0, and By changing cell as D2, storing the results in the next cell down i.e. H6.

I have little experience writing VBA scripts so any assistance you could provide would be appreciated.

Regards

Thank you, but what I need is vba code for a macro. to explain better. i am importing data from web query to sheet1. It has up to twenty different data tables. The problem I have is each table can very from 4 to 20 rows. I need a macro to run that will copy and paste each table into fixed template on sheet2 whether there are 4 or 15 rows per table. ex:

sheet1-- sheet2

A B C-- A B C

1 R p p-- 1 R p p

2 H p p-- 2 H p p

3 H p p-- 3 H p p

4 H p p-- 4 H p p

5 H p p-- 5 H p p

6 R o o-- 6

7 H o o-- 7

8 H o o-- 8

9 H o o-- 9

10 H o o-- 10

11 H o o-- 11

12 H o o-- 12

13 H o o-- 13

14 R g g-- 14

15 H g g-- 15

16 H g g-- 16 R o o

17 H g g-- 17 H o o

18 H g g-- 18 H o o

19 H g g-- 19 H o o

20 R t t -- 20 H o o

21 H t t-- 21 H o o

22 H t t-- 22 H o o

23 H t t-- 23 H o o

24 H t t-- 24

25 R j j-- 25

26 H j j-- 26

25

etc. i hope this makes more sense and somebody can help me out. thank you!

I'm getting better at Macro's but I'm stumped and need some expert help.

I have a document created by someone else that I'm trying to modify. There are three steps, the 1st and 2nd I can do manually but would love a macro to do it automatically.

First step - Cell F51 is a formula but needs to be changed to a value - 20, but it is not the full column. The next step is F99, then F147 all the way to F37347.

Next step - I need to make all values in rows G:K = the cell in F, but only on the rows that were just changed to the number 20 (rows 51, 99, 147, up to 37347).

Once that is done I need to goal seek the value in cell O53 to match the value in P53 by changing the value in F51 that I just changed to 20, so it will now change again (20 is just an arbitrary number that is close to the result that I often see in column O.

Since this is a huge spreadsheet with a lot of data I don't want to sort it, I'm afraid it won't get back to where it was, and it will take way too long to do manually. I can filter it - fortunately the creater turned the cells that I need to change in column F red - not sure if that helps or not...

Any help you can give will save me LOTS of time.

Dear experts.
I do not think formula will help in this case and as I am not a VBA expert I will appreciate any help.
I need macro which will filter data table with conditions chosen from drop down lists and then copy resulted table into the second worksheet under the drop down lists.
Unfortunately I am an absolutely VBA beginner so can't do it on my own.
Is this even possible - please help.
Please note: I know I can apply filter without macro but I don't want to do this in that case because of character of report I would like to make.

I hope there will be somebody who will help me with that...
Many thanks.

PS. file attached.

Here is what I need to do.

On one worksheet ("UserInput"), users provide a desired output value. This output value lives in cell (9, 2).

On a different worksheet, there is a data table. The x-values are determined by a macro which works fine. The y-values are determined by a goal seek macro -- at least, they're supposed to be.

Here's the code:

Dim ctrCounter As Integer
Dim dblmin As Double
Dim dblmax As Double
Dim intsteps As Integer
Dim dblXValue As Double

dblmin = Cells(2, 6).Value
dblmax = Cells(2, 8).Value
intsteps = Cells(2, 10).Value

'This part determines the x-values in the data table.
For ctrCounter = 0 To intsteps
dblXValue = ((dblmax - dblmin) / intsteps) * ctrCounter + dblmin

Sheets("DataTable").Cells((ctrCounter + 4), 1).Value = dblXValue

Next

For ctrCounter = 0 To intsteps

'This next part establishes what formula goal seek should use, and where the 
'different values are coming from; it should place an initial "guess" value
'in cell (9,3). Thish part works fine.

Sheets("UserInput").Cells(9, 3).Value = 0.5 * Sheets("DataTable").Cells(ctrCounter + 4, 2).Value *
(Sheets("DataTable").Cells(ctrCounter + 5, 1)) ^ 2

'Next is the goal seek. It uses the value in cell(9, 2) as the goal, adjusting
'the y-values in the data table so that they provide the correct value for
'attaining the desired output value in cell (9, 2). This part works fine if you
'set it up so that you are only trying to goal seek for a specic cell in the
'data table, but once you put the ctrCounter variable in there and try to
'have it find the values for the whole y-column, you get "Reference Is Not 
'Valid."

Sheets("UserInput").Cells(9, 3).GoalSeek Goal:=Sheets("UserInput").Cells(9, 2).Value,
ChangingCell:=Sheets("DataTable").Cells(ctrCounter + 5, 2)

Next

End Sub
Can anyone see what the problem is? I haven't been able to figure it out.