Go Back  Xtreme Visual Basic Talk > Legacy Visual Basic (VB 4/5/6) > VBA / Office Integration > Excel > Using goal seek to make a table


Reply
 
Thread Tools Display Modes
  #1  
Old 07-23-2007, 02:18 PM
AKK AKK is offline
Newcomer
 
Join Date: Jul 2007
Posts: 13
Default Using goal seek to make a table


Here is what I need to do.

On one worksheet ("UserInput"), users provide a desired output value. This output value lives in cell (9, 2).

On a different worksheet, there is a data table. The x-values are determined by a macro which works fine. The y-values are determined by a goal seek macro -- at least, they're supposed to be.

Here's the code:

Dim ctrCounter As Integer
Dim dblmin As Double
Dim dblmax As Double
Dim intsteps As Integer
Dim dblXValue As Double

dblmin = Cells(2, 6).Value
dblmax = Cells(2, 8).Value
intsteps = Cells(2, 10).Value

'This part determines the x-values in the data table.
For ctrCounter = 0 To intsteps
dblXValue = ((dblmax - dblmin) / intsteps) * ctrCounter + dblmin

Sheets("DataTable").Cells((ctrCounter + 4), 1).Value = dblXValue

Next

For ctrCounter = 0 To intsteps

'This next part establishes what formula goal seek should use, and where the
'different values are coming from; it should place an initial "guess" value
'in cell (9,3). Thish part works fine.

Sheets("UserInput").Cells(9, 3).Value = 0.5 * Sheets("DataTable").Cells(ctrCounter + 4, 2).Value * (Sheets("DataTable").Cells(ctrCounter + 5, 1)) ^ 2

'Next is the goal seek. It uses the value in cell(9, 2) as the goal, adjusting
'the y-values in the data table so that they provide the correct value for
'attaining the desired output value in cell (9, 2). This part works fine if you
'set it up so that you are only trying to goal seek for a specic cell in the
'data table, but once you put the ctrCounter variable in there and try to
'have it find the values for the whole y-column, you get "Reference Is Not
'Valid."

Sheets("UserInput").Cells(9, 3).GoalSeek Goal:=Sheets("UserInput").Cells(9, 2).Value, ChangingCell:=Sheets("DataTable").Cells(ctrCounter + 5, 2)

Next

End Sub

Can anyone see what the problem is? I haven't been able to figure it out.
Reply With Quote
  #2  
Old 07-23-2007, 07:13 PM
AKK AKK is offline
Newcomer
 
Join Date: Jul 2007
Posts: 13
Default

Alright -- I am beginning to suspect that the reason this is not working is because the goal-seek macro is getting into a time race with the line that calculates an initial guess. I think the goal-seek line begins to run before the calculations in the previous line are finished. Maybe.

So, a new question -- does anyone know of a way to force the macro to wait for the first line to complete before running the second? I found this web page

http://support.microsoft.com/kb/147392

but it's totally greek to me. Can anyone help?

Thanks!
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
 
 
-->