Results 1 to 2 of 2
Thread: Goal Seek within VB
2013-04-11, 09:36 #1
- Join Date
- Dec 2012
- Thanked 0 Times in 0 Posts
Goal Seek within VB
I am using Goal seek in VB, but instead of actually returning the result in a cell I would like to return the result in a message box so the user can enter it in the starting cell.
Iím unable to upload workbooks so hope the baove makes sense:
If Range("L58") - "0.000001" > Range("L25") Then
Range("L58").GoalSeek Goal:=Range("L25").Value, _
L58 and L25 are%
J46 is a net column that then has various calculation added to it to then calculate the L58 %. Goal seek works on this basis.
Thanks in advance
2013-04-11, 17:31 #2
- Join Date
- Jan 2004
- Thanked 109 Times in 94 Posts
Hopefully someone will show that I'm wrong, but I think that the way that goal seek works precludes what you are trying to do in the way you describe.
The goal seek function writes a test value in the target cell, then recalculates the workbook, then uses the new value to compute what the next test value should be - and so on until it has iterated to the desired result - or can't get there, in which case it gives you a warning. So the process of writing into the target cell is integral to the way that goal seek functions.
Two possibilities I can think of, offhand:
1. My favourite: it sounds clumsy but is actually simple to code. I can think of several variations on this theme - hopefully it will stimulate your creative juices :-)
a. Disable screen updates
a. Write a copy of the workbook to file with another name
b. Do your goal seek in the copy and store the answer in a VBA variable
c. Delete the copy file
d. Enable screen updates
e. Report the stored solution value in your message box.
2. Most often, although not always easy, it is possible to do a direct calculation of the target cell instead of using goal seek.
Its an unusual request - perhaps if you can describe what you are trying to achieve overall, someone might come up with another way altogether to achieve it ?
Last edited by MartinM; 2013-04-11 at 17:33.