So I wrote the macros for my model to run for various scenarios and the model runs fine. But now I need to add an if statement where if the toggle cell I gave the name "GoalSeeker" has "Yes" selected, I want the model to goalseek the growth rate instead of using the assigned growth rate (cell I labeled as "Growth_rate_new").
Here's the problem... the if stmt. in the macro isn't picking up the differentiation and not doing the goalseek. Can anyone tell me why it's wrong? I'm pretty new at this.
Here's the code I'm wrote:
If GoalSeeker = "Yes" Then
Sheets("ABC").Select
Range("C21").Select
ActiveCell.GoalSeek Goal:=0, ChangingCell:=Range("H13")
Else
Sheets("<span><a href="/finance-dictionary/what-is-internal-rate-of-return-IRR"><abbr>IRR</abbr></a></span>").Select
Range("Growth_rate_new").Select
Selection.Copy
Sheets("ABC").Select
Range("H13").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End If
C21 is the cell that I am goalseeking to 0
H13 will be the growth rate used
Thanks
Change If GoalSeeker = "Yes" Then to If Range("GoalSeeker").Value = "Yes" Then
That's the quick fix. Your macro is fugly. I will fix it in a few minutes.
If Range("GoalSeeker").Value = "Yes" Then
Worksheets("ABC").Range("C21").GoalSeek Goal:=0, ChangingCell:=Worksheets("ABC").Range("H13")
Else
Worksheets("ABC").Range("H13").Value = Sheets("IRR").Range("Growth_rate_new").Value
End If
Thanks dacarez much appreciated!
Yeah I know, the code I'm writing is pretty inefficient... I kind of just been learning how to write it after recording the action...
Is there a way for the macros to recognize change in worksheet names? Seems like the references are absolute... so if I change worksheet name to XYZ from ABC, the code won't pick it up right?
Nevermind, I just reread what you are trying to do.
Always nice to see bankers getting VBA-enabled, definitely gives you more options on the fringe.
Right, those names are hardcoded. You can do one of two things 1 - declare constant variables at the top of your module with sheet/range names that you can just change once and it will flow through 2 - put something in the sheet that identifies the sheet and then write a function that returns that sheet.
e.g. 1 - Const aSheet as String = "ABC" Const iSheet as String = "IRR"
and then in your code: Worksheets(aSheet).Range("A1")....
2 - Say that Cell A1 has identifier text of ABC for sheet currently called ABC and XYZ for another sheet
Function FindSheet(strIdent as String) As Worksheet Dim cSheet as Worksheet For Each cSheet in ThisWorkbook.Worksheets If cSheet.Range("A1").Value = strIdent Then Set FindSheet = cSheet Exit For End If End FunctionSo that in your code in other places, instead of referring to Worksheets("ABC"), you could do:Dim ABCSheet as Worksheet Set ABCSheet = FindSheet("ABC")
(you use Set for Object assignment in VBA, and you want to save that reference because the For loop is a bit time-intensive)
You could just do an absolute reference to the sheet number. For example, even though you may rename Sheet1 to ABC, you can still refer to that sheet as Sheet1.Select, or whatever you want to do.
Well, of course you can make it clean by introducing a const variable at the begining like ABCSheet = Sheet1.Name to make it more readable, along with other things. I just figured I'd point out a simple thing that people often overlook.
Repudiandae ipsam consequatur animi unde. Eum et deleniti dolores ut est. Enim minus voluptatem perspiciatis dolorem rerum asperiores.
Enim nisi repellendus nobis alias qui officia exercitationem. Quas vel repellat recusandae ex dolorum. Quasi suscipit et ea molestiae dolorem.
Omnis ut facilis optio tempore tempore quis maxime. Eos quasi aut aut fuga aut. Consequuntur nisi repellendus enim maiores dolor modi aut.
Illum eum aut et quia. Illum doloribus dolorem sunt est aut illo odio. Sed maiores aut quo ipsum et et. Distinctio iusto et fugiat aut quo omnis. Est praesentium dolorem earum ullam veritatis eos.
See All Comments - 100% Free
WSO depends on everyone being able to pitch in when they know something. Unlock with your email and get bonus: 6 financial modeling lessons free ($199 value)
or Unlock with your social account...