Goal Seek Macro in VBA Giving Nonsensical Answers

[nshukla]

Hi everyone,

I have been looking at this code for way too long and cannot figure out the issue, so any insight would be appreciated!

I am trying to create a goal seek in VBA that will look at the baseline production of people at a given year and change a value to hit a target production of people in that same year.

The code works perfectly until we get to the goalseek piece in the Macro and TargetFound keeps coming up as false.

I have pasted the code below and added a photo of the sheet that the macro should be working on:

For Each cell In ActiveSheet.Range("C13:" & Cells(13, Cells(13, Columns.Count).End(xlToLeft).Column).Address)
If Trim(cell.Value) <> "" Then
cadreName = Left(Trim(cell.Value), InStrRev(cell.Value, " ") - 1)
cadreColRef = Split(cell.Address, "$")(1)

If (Trim(ActiveSheet.Range(cadreColRef + "21")) <> "") Then
targetMethod = LCase(Trim(ActiveSheet.Range(cadreColRef + "21")))

Select Case targetMethod
Case "manual entry"
targetValRow = "25"
Case Else
targetValRow = "23"
End Select

If (Trim(ActiveSheet.Range(cadreColRef + targetValRow)) <> "" And IsNumeric(Trim(ActiveSheet.Range(cadreColRef + targetValRow)))) Then
originalScaleUp = ActiveSheet.Range(cadreColRef + "41").Value

TargetFound = Sheets("Assumptions Tab").Range(cadreColRef + "27").GoalSeek(Goal:=Sheets("Assumptions Tab").Range(cadreColRef + targetValRow).Value, ChangingCell:=Sheets("Assumptions Tab").Range(cadreColRef + "41"))

 

[StephenCrump]

Welcome to the Forum!

It's impossible to tell what's going on based on an incomplete code fragment and a picture. It's also not clear whether ActiveSheet is meant to be Sheets("Assumptions Tab") or another sheet?

My guess though is that you've accidentally set your goal to zero (Goal:=Sheets("Assumptions Tab").Range(cadreColRef + targetValRow).Value), hence there is no solution.

If you put in a breakpoint and test: ?Sheets("Assumptions Tab").Range(cadreColRef + targetValRow).Address is this pointing to the right cell? Or perhaps to a blank cell?

 

[nshukla]

Hi @StephenCrump Thank you for the warm welcome!

The active sheet is the assumptions sheet. Unfortunately, I can't link the full workbook due to sensitive data, but happy to talk through it. In the photo, Row 27 is the "set cell" in goal seek and Row 23 is what I want to get to by manipulating Row 41.

I tried the breakpoint and all the addresses in the goal seek code are pointing to the correct cell, but the TargetFound variable is still coming up as False even though there is data in those cells.

Would greatly appreciate any other ideas or help!

 

[StephenCrump]

Without seeing the rest of your code, and the layout (using XL2BB: XL2BB - Excel Range to BBCode rather than an image) it's only speculation ....

With the code in break mode and all the cell references correct, is the Goal Seek working? If not, why not? Try it from Excel - it should act the same way. Is the problem with the Goal Seek rather than the code?

If it does work, is there something later in your code that's setting TargetFound to FALSE, e.g. it's not at all clear why your code loops through row 13?