Excel VBA Multiple Automatic Goal Seek Required

BobBridges · #1 06-21-2014, 09:02 AM

I haven't checked whatsup's formula, but I agree with him that some formula would work and be faster than using GoalSeek. But for the sake of explaining VBA, I'll continue with your original problem, because that's worth knowing even if the formula is better this time.

I'll do this in steps. The first step is to create a module where you can put the "common code", the VBA statements that every worksheet will call. Here's how you do it in Excel 2010 (and if you're using some other version let me know):

1) Start in the VBA editor.

2) Right click anywhere inside the red box in the below screen shot.

3) Select "Insert", then "Module". An empty module will be created in the Modules section. (You see in the screen shot that I already have one created.)

4) Optional: I named mine "mGoalSeek"; you can leave yours named "Module1" or whatever VBA names it by default, or you can change it to your own name by typing over the Name in the Properties window. Let me know if you have trouble with this and I'll show you another screen shot.

The new module is ready for you to type in, creating any new subroutines or functions.

Next I'll describe how to write a subroutine that your individual worksheets can call....in a separate post.

champaben · #2 06-21-2014, 09:11 AM

Quote:

Originally Posted by BobBridges

I haven't checked whatsup's formula, but I agree with him that some formula would work and be faster than using GoalSeek. But for the sake of explaining VBA, I'll continue with your original problem, because that's worth knowing even if the formula is better this time.

I'll do this in steps. The first step is to create a module where you can put the "common code", the VBA statements that every worksheet will call. Here's how you do it in Excel 2010 (and if you're using some other version let me know):

1) Start in the VBA editor.

2) Right click anywhere inside the red box in the below screen shot.

3) Select "Insert", then "Module". An empty module will be created in the Modules section. (You see in the screen shot that I already have one created.)

4) Optional: I named mine "mGoalSeek"; you can leave yours named "Module1" or whatever VBA names it by default, or you can change it to your own name by typing over the Name in the Properties window. Let me know if you have trouble with this and I'll show you another screen shot.

The new module is ready for you to type in, creating any new subroutines or functions.

Next I'll describe how to write a subroutine that your individual worksheets can call....in a separate post.

Thanks for your input and time, I have figured the problem out.

BobBridges · #3 06-21-2014, 09:29 AM

Step two: Calling a common module from each of the worksheets.

You already have a start because you have a Worksheet_Change subroutine written in one of your worksheets:

Code:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

Dim r As Range

Set r = Range("F2,M2")
If Intersect(Target, r) Is Nothing Then Exit Sub
Application.EnableEvents = False
Range("J2").GoalSeek Goal:=Range("M2"), ChangingCell:=Range("G2")
Application.EnableEvents = True
End Sub

I'll show you how to change that around so this subroutine calls another one. Here's a subroutine that I named DoGoalSeek with all those statements copied into it almost unchanged:

Code:

Sub DoGoalSeek(Target)

Dim r As Range

Set r = Range("F2,M2")
If Intersect(Target, r) Is Nothing Then Exit Sub
Application.EnableEvents = False
Range("J2").GoalSeek Goal:=Range("M2"), ChangingCell:=Range("G2")
Application.EnableEvents = True
End Sub

This code I write into the new module you just created. Now it can be called by any other subroutine in your workbook, like this:

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
DoGoalSeek Target
End Sub

Here's what happens: When you change any cell in a worksheet, Excel runs the Worksheet_Change subroutine for that worksheet (if any). The only thing that subroutine does, as I've modified it above, is call DoGoalSeek and pass it the Target object (which is a pointer to the cell that was changed).

DoGoalSeek takes the Target object passed to it and does everything that used to be in your Worksheet_Change subroutine. When it's done, it passes control back to Worksheet_Change, which returns control back to you.

Now you can paste the same three-line subroutine into Sheet2, and into any other price sheet you have in your workbook, and each one will call the same logic every time a cell is changed. Then when you have to change the way DoGoalSeek works—and we will change it, in several ways—you can change it in just one place instead of in every worksheet.

Next post: What we have to do to DoGoalSeek to make it work for any row, not just row 2.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
automatic entering of multiple words	cliff@tauruscommercial.ca	Word	5	06-03-2014 06:06 PM
set daily goal for actual workdays, excluding weekends holidays and days off	Brian Reilly	Excel	1	01-24-2014 11:56 PM
Excel 2007 - formula or macro/vba code required	wrighty50	Excel Programming	3	05-13-2012 02:24 PM
Adding a goal line to a chart	uhlersa	Excel	1	01-16-2012 08:18 PM
Normal.dot Help? Fatally Corrupted; No Fix. Please Assist.	atm0073	Word	2	09-21-2009 04:02 AM