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

Thread Tools Display Modes
Old 07-23-2007, 01:18 PM
AKK AKK is offline
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


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

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


End Sub

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

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


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

Reply With Quote

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


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.
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
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..