Goal seek in a Macro

[Wizards]

Hi,

I'm running a reasonably involved spreadsheet modelling multiple cost inputs for several products.

One of the items that the Business team want is the capacity to run a macro with a pre-determined margin which will then update a specified initial cost input.

Is it possible to record a macro to do this or is it necessary to do it through VBA?

I did try to record it initially but couldn't get it to work so I edited the code. I thought the edit would actually take care of it but I continue to get a result of zero every time I run the macro.

I've copied the VBA code below.

I had thought that entering "Goal:=Range("H3").Value" would ensure the value for the "to value" field in Goal Seek would update correctly each time. However it seems to continue to pick up a null value and therefore a zero result for the Macro.

Is there something I'm missing in the Macro?
Is it possible to actually use goal seek in a recorded Macro?
I am sending the result of the macro to a new sheet (part of the macro). Would this be causing a problem?

Sheets("Summary").Select
Sheets("Summary").Copy Before:=Sheets(1)
Sheets("Summary (2)").Select
Sheets("Summary (2)").Name = "Margin Output"
Range("D25").Select
Cells.Replace What:="'Margin Output'!", Replacement:="'Summary'!", LookAt _
:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Rows("5:20").Select
Selection.Delete Shift:=xlUp
Rows("27:27").Select
Selection.Delete Shift:=xlUp
Sheets("Margin Output").Select
Range("A1").Select
With Selection.Font
.ThemeColor = xlThemeColorDark1
.TintAndShade = -4.99893185216834E-02
End With
ActiveCell.FormulaR1C1 = "85%"
Range("D9").Select
Range("D9").Select
Selection.Copy
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Selection.Style = "Currency"
Range("D9").Select
ActiveCell.FormulaR1C1 = "=R[-8]C[-3]"
Range("D9").Select
ActiveCell.FormulaR1C1 = "=R1C1"
Range("D9").Select
Selection.AutoFill Destination:=Range("D9:H9"), Type:=xlFillDefault
Range("D9:H9").Select
Sheets("Margin Output").Select
Range("D25").GoalSeek Goal:=Range("H3").Value, ChangingCell:=Range("A1")
End Sub

[Hawkeye16]

I will take a look, I use goalseek in a few of my work macros, I will see if there are any differences. Please do use code tags around your code though. (# button)

It may help if you attach a desensitized file for us to look at. The goalseek syntax and everything looks ok to me. The only thing I am not sure about with a quick glance at the workbook is it seems you are changing the formaula in D25 to look at the Summary sheet instead of the sheet you are changing the cell on to get a different result (Margin Output).

Does the formula in D25, when the goalseek is performed, use the cell A1 on the margin output tab, and is there a value in H3?

[Wizards]

Hi Hawkeye,

Just to confirm that there is a value in H3 and the formula in D25 does use the cell A1 in the Margin Output tab.

Going over the code again I wonder about the code:

End With
ActiveCell.FormulaR1C1 = "85%"


Is this picking up an absolute value which will contradict the variable value which can be entered in cell "H3"?

If so, how would I alter that code?

[Hawkeye16]

Your 85% is going into cell A1 since that is the active cell at that time, but then you copy in the value (not formula) of what was in D9 into A1 right after that so the 85% is not doing anything from what I can tell.

What are you wanting to start the goalseek at, 85%? if so take out this code

Please Login or Register  to view this content.

If you want to start the goalseek at the value from D9 (what it is currently doing) then you can just take out the line setting A1 to 85%. Perhaps the value copied from D9 to A1 is what is causing your goalseek error... no way for us to tell without a sample worksheet though.