Fabio has attended:
Excel VBA Advanced course
Excel VBA GoalSeek
Can I use the function GoalSeek in VBA without having to refer to cells in a spreadsheet, but instead just using the variables and formulas within the code?
Refering to cells the VBA would be:
Sheets("Calculations").Range("D22").GoalSeek Goal:=50, ChangingCell:=Sheets("Calculations").Range("A22")
But I would like to change the value of a variable which contains the formula in the code instead of using the code in the spreadsheet.
Something like:
ScaledTemperature = RawTemperature * TemperatureGain
I would like to find the RawTemperature by changing the ScaledTemperature using GoalSeek purelly in the code without using the spreadsheet.
I know that for this formula I could just work the formula backwards but this is just a simplistic example I'm giving in this question.
RE: Excel VBA GoalSeek
Hi Fabio
I've tried two different ways using cells and range names.
But it doesn't seem possible not by assigning a variable to the Goalseek method.
Cell B1 is the Raw temp and contains the formula = B2/1.5
Cell B2 is a blank cell for the Scaled temp
Sub Goalseek()
' Goalseek with cells
Sheets("Sheet1").Cells(1, 2).Goalseek Goal:=50, ChangingCell:=Sheets("Sheet1").Cells(2, 2)
MsgBox "The Scaled temperature for 50 degrees is " & _
Sheets("Sheet1").Cells(2, 2).Value
End Sub
Sub Goalseek2()
' Goalseek with range names
Sheets("Sheet1").Range("Raw").Goalseek Goal:=50, ChangingCell:=Sheets("Sheet1").Range("Scaled")
MsgBox "The Scaled temperature for 50 degrees is " _
& Sheets("Sheet1").Range("Scaled").Value
End Sub
Another approach might be to defining a range variables.
Let me know if you have any success, thanks.
Regards
Doug Dunn
Best STL
RE: Excel VBA GoalSeek
Hi Doug,
Thanks for the quick reply.
Using the formula refering to cells is fine. I was just wondering if there is any way we can use goalseek purelly in formulas we write in the VBA code. i.e.
Sub GoalSeekTest
B = 2
C = 5
A = B * C 'would show A=10
'Then a function to find A by changing C
Goalseek A to the value of 50 by changing C
Msgbox C
End Sub
or VBA does not support this function without changing cell values?
RE: Excel VBA GoalSeek
Hi Fabio
I carn't see a way of using GoalSeek without refering to cells.
If I come across a way I will let you know.
Regards
Doug
