Excel/GoalSeek

Advertisement


Question
Hello,

I use Windows 7, Office 2013.

Manually entered into Cell C12 is either the value Call or Put.

If I desire to run GoalSeek to calculate a value for Call, using the following GoalSeek values,

     Set cell:  H29 (formula)
     To value:  C11 (manual entry)
     By changing cell:  C33 (manual entry)

and I use this code:

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Address = "$C$11" Then
     Range("H29").GoalSeek Goal:=Range("C11"), ChangingCell:=Range("C33")
  End If
End Sub

Further, for calculating a value for Put, I use these GoalSeek values,

     Set cell:  H31 (formula)
     To value:  C11 (manual entry)
     By changing cell:  C33 (manual entry)

and I use this code:

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Address = "$C$11" Then
     Range("H31").GoalSeek Goal:=Range("C11"), ChangingCell:=Range("C33")
  End If
End Sub

What I need is to combine the two codes into one. In other words, if Cell C12 equals Call, then run the Call code, but if Cell C12 equals Put, then run the Put code. However, if Cell C12 doesn’t equal either Call or Put, then do nothing.

Thank you,

Ted Richardson

Answer
Hi Ted,

Apologies for the delayed response.
You can try using the Select Case commands in vba macros.

Case 1 can be call, case 2 can be put and so on.

If you can share the file, I'll modify the macro and send you the end product with both working.
Please feel free to send the file to gulthemacroguy@gmail.com and make sure you mention AllExperts in the subject line.

Hope this helps,
Gulshan.
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


GULSHAN PURSWANI

Expertise

I can answer questions related to the following topics: 1. MS Excel - Creating and Linking Formulae, Running Pivot Tables, Vlookup etc. 2. Macros / VBA - Creating Macros to do specific jobs. Importing / Exporting / Validating Data in excel through Macros.

Experience

I've been working on excel for over 10 years and on VBA macros for over 3 years now.

Education/Credentials
Bachelor of Commerce, Chartered Accountant from The Institute of Chartered Accountants of India

©2016 About.com. All rights reserved.