Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  HOME    TRACKER    Visual Basic

Goal Seek In VB

I had a calculation performed on a spreadsheet which I transfered to Visual Basic, the spreadsheet performed calculation using a value derived using the goal seek function.

The code is now something as below:

TargetValue = 1500
arbval = -100000 ' this is intial value assumed as the goal seek value

Do While TotFlo <= TargetValue
Totflo = 0
For i = 1 To 365
SixFlow(i) = calculation performed using arbval and other variables
Totflo = Totflo + SixFlow(i)
Next i
arbval = arbval + 0.001


as you can see i increase arbval by 0.001 on each loop to put into the caculation trying to copy goal seek.

Can anybody suggest some code that would allow to decrease the processing time, arbval will be between -100,000 and 100,000

thanks in advance

View Complete Forum Thread with Replies

See Related Forum Messages: Follow the Links Below to View Complete Thread
Goal Seek
Can I perform any function in VBA such as GOAL SEEK in excel?
Thanks in advance!

Please Help! Goal Seek VB Problem In EXCEL
I hope someone can help me. I am using the goal seek function to create a set of values at given interval and from a given starting point. The values that I am setting usually contain a polynomial equation but today I need to use a logarithmic equation. The code that I have written, see below, usually works quite well but now using the Ln function it contains an error. Does anyone have any idea what the mistake is? Is it something to do with negative numbers and logarithmic funtions?

Many thanks

Sub AutomateGoalSeekWithLBound()

' AutomateGoalSeek Macro
' This macro automates goal seek, enabling the user to select a range and incrementally goal seek with
' user defined increments and user defined lower bound parameter
Dim eVersion As String
eVersion = WhatVersion

Dim myLBound As String, myUBound As String, myRange As Range, myIncrement As String
Set myRange = Selection
Dim myCounter As Double

myLBound = InputBox("Enter lower bound parameter", "Lower Bound Parameter", 100)
If myLBound = "" Then GoTo myErrorHandler
'myUBound = InputBox("Enter Upper Bound Parameter", "Upper Bound Parameter", 10000) this isn't used just ignore
myIncrement = InputBox("Enter goal seek increment" & vbCrLf & vbCrLf & "Note for 1% increment = 0.01", "Increment", 100)
If myIncrement = "" Then GoTo myErrorHandler

myCounter = myLBound

If CDbl(eVersion) > 9 Then
For Each myCell In myRange
myCell.Offset(0, -1).ClearContents
myCell.GoalSeek Goal:=myCounter, ChangingCell:=myCell.Offset(0, -1).Range _

myCounter = myCounter + myIncrement

Next myCell
Edit by moderator: You can use [vb] tags to format your code. Click 'Edit' or 'Reply' on this post to see how tags work, or read all about tags here.

Number Formatting (When Using Goal Seek)
I am working on a project which requires the use of the goal seek function. I have that portion working properly but as you can imagine the output of the goal seek is usually a number with many characters after the decimal. Is there anyway I can round these values down to the nearest whole number? My code is as follows:

Private Sub CommandButton3_Click()
    Select Case Range("D365")
        Case Is < 100000
            Range("B22").GoalSeek Goal:=0.4, ChangingCell:=Range("B20")
        Case Is >= 100000
            Range("B22").GoalSeek Goal:=0.35, ChangingCell:=Range("B20")
        End Select
End Sub


How To Move Goal Seek Into Insert Function?
That's the main objective. I need to write a function that does exactly the same as the Goal Seek but I need it as a function so I can insert it and simply copy to sifferent cells.
Would be very greatful!

Is There A Goal Seek Function In VB? Please Help With Mathematical Problem.
I'm making an application that need to use a function like 'goal seek' in Excel. Here's the problem example:

Price: 100.000
Down Payment: 20% -> 20.000
Payment per month: 8.000 (assume a 10 month period)
Total Down Payment: 28.000 (20% of 100.000 + 8.000 first payment)

Now I want the Total Down Payment to be 30.000, so I need to change the Down Payment of 20% to another value. In Excel, I can use the Goal Seek Function to do this very easily, and the Down Payment results in 22,2222%.

How do I do it in VB?


My Goal Is To Run A Pps From VB....
and capture the end of the powerpoint show as an event.

I have this working, kinda (see below), but the problem is the code is messy!!!!! The "Do Events" inside the "Do Loop" hoses the memory on the seems to work, but there must be a better way.

Any Ideas?

VB Code:
Private Sub Form_load()    strLocalPPTPath = "D:vbPPT est.ppt"End Sub Sub AutomatePPT()    Dim oPPTApp As Object    Dim oPPTPres As Object    Dim PresPath As String        On Error GoTo PPTFinished        Set oPPTApp = CreateObject("PowerPoint.Application")        With oPPTApp        Set oPPTPres = .Presentations.Open(strLocalPPTPath, , , False)        oPPTPres.SlideShowSettings.Run        Do While .SlideShowWindows.Count > 0            DoEvents        Loop    End With    PPTFinished:    Set oPPTPres = Nothing    DoEvents    oPPTApp.Quit    DoEvents    Set oPPTApp = Nothing    debug.print "The Powerpoint has ended"    End Sub

Help With Seek Bar.
Hey guys,
I have a slider and it's supposed to display the position of the song that is playing, it does that but I want to be able to click somewhere along the slider and the song jump to that position. I am using MMControl.
This is what I have so far....

Private Sub MMControl1_StatusUpdate()
sld_Seek.Max = MMControl1.Length
sld_Seek.Value = MMControl1.Position
End Sub

So could anyone please help! Oh and if anyone has a playlist for MMControl could you post that aswell.

Seek? Seek What?!
Im using:

Open sName For Binary As fID
Get #fID, , ActualBuffer

I can use seek to find out the current position in the file, but how do I modify the current position?

I.e. how do I move the current byte pointer back by a certain number of positions?

Seek Bar
Hey, does anyone have a seek bar, I am using MMControl and I would like one that when you click on the seek bar it will skip through to that part of the song.

this is the code i used to search my database for a matching record, but when i click on find and the box pops up i type in a work request number and click ok...then it tells me "workRequest" is not an index in this table.

how do i index this?

thanks inadvance annie

VB Code:
Private Sub cmdFind_Click()   prompt$ = "Please enter the Work Request Number you wish to find."  'get string to be used in the work request field search  SearchStr$ = InputBox(prompt$, "Work Request Search")      switch.Recordset.Index = "workRequest"    switch.Recordset.Seek "=", SearchStr$       If switch.Recordset.NoMatch Then        switch.Recordset.MoveFirst     End If    End Sub

Seek Or Not Seek?
Has anyone used the Seek method within ADO? If so could you show me an example of its use? I'm not following the help file on this.

Thanks, Richard

DAO Seek
how can i use seek function to DAO.?

Help With Seek
Hi all

Does anybody know why this is happening

I have a function which opens a connection to a database and then opens a recordset

Function OpenSnapShot() As Boolean
On Error Resume Next 'set error trap

Set cnn = New ADODB.Connection
With cnn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Open "c:
End With

Set rsPLU = New ADODB.Recordset
With rsPLU
.ActiveConnection = cnn
.CursorLocation = adUseClient
End With

If (Err <> 0) Then
OpenSnapShot = False
OpenSnapShot = True
End If

End Function

This opens fine
The problem is when I try to use seek

rsPLU.Index = "PLU_CODE"
rsPLU.Seek "92935", adSeekFirstEQ

When the I hit the rsPLU.Index part I get the following error.

The operation requested by the application is not supported by the provider.

I had a look on the miscrosoft site and it said you need to set the recordsets cursorlocation to clientside, which I did and it still doesn't work.

Does anybody have any ideas.


I have created index in the sql table. How do I invoke the index name in VB. I am using ADODB. Which provider supports the 'SEEK' command in VB with SqlServer.

Sundar Raman S K

Use Seek
who can i used seek and find ferst or find last with data control
to search my data base about number or name by textboxe


Hello all,
I wonder if someone could help me with this problem I am facing.

1- I am interfacing with Access97 Database.
2- Referencing (Microsoft ActiveX Data Object 2.1 Library)

The documentation I have read indicates that the provider I am using should be able to support the (Index) and (Seek) method of the recordset declared. Below is the code I am using and some comments.

Private Sub Command1_Click()

'recordset and connection variables
Dim rstEmployees As ADODB.Recordset
Dim Cnxn As ADODB.Connection
Dim strCnxn As String
Dim strSQLEmployees As String

Dim strID As String
Dim strPrompt As String
strPrompt = "Enter an EmployeeID (e.g., 100 to 1500)"

' Open connection
Set Cnxn = New ADODB.Connection
strCnxn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=E:Db_testdb.mdb;" & _
"user id=admin;password=;"
Cnxn.Open strCnxn

' open recordset server-side for indexing
Set rstEmployees = New ADODB.Recordset
rstEmployees.CursorLocation = adUseServer
strSQLEmployees = "EmpNum"
rstEmployees.Open strSQLEmployees, strCnxn, adOpenKeyset, adLockReadOnly, adCmdTableDirect

' Does this provider support Seek and Index?

if I put this statement :
rstEmployees.Index = "PrimaryKey"

I get this error.

(Runtime: Error '3251'
Object or provider is not capable of referencing requested operation)

'The if clause is skipped in here. The support method indicated, that it could not support (index) or (seek). However, the documentation it mentioned that it does.

If rstEmployees.Supports(adIndex) And rstEmployees.Supports(adSeek) Then
rstEmployees.Index = "PrimaryKey"
End If

' clean up
Set rstEmployees = Nothing
Set Cnxn = Nothing

End Sub


I like to know if there are faster way to get a field in a table using ADO.
I´m now using filter like this

Dim rs as adodb.recordset
Dim db as adodb.connection
Dim sReplace as String

rs.Open "SELECT UK, " & m_sLanguage & " from _ language", db, adOpenForwardOnly
rs.Filter = "UK = '" & sFind & "'"
sReplace = tv_ADO(m_sLanguage) & ""

I´m wondering how I can use the seek method or any other faster way??

Seek In File
To seek to a specific position in a text file what methods are available.

I know there is Get where you can specify the position but are there any others. In C++ you use seekg(position) to do this.

How To Change The Seek Bar
Hello Everyone,

I'm working on a MP3 Player, and we want to change the how the MediaPlayer Control's Seek Bar Looks. We have made our own buttons, which have a very different look from the windows standard controls, please check out the attached .gif to see what I mean.

Thanks In Advance!

Seek Function
here is my problem - i am using a data source, connected to an excel spreadsheet. i have to use excel, and need to search through the data. As it is in 'table' format, i cannot use findfirst method, just seek.
The problem i have is that it requires indexes to do a seek on a table, and i have no idea how to create an index for an excel spreadsheet.
Can any of you help me out?


Word Seek?????
Alright, this is hard to explain, but I am trying to make a "bot" like AIM's SmarterChild for ex. What I want to do (instead of writing each command) when the user types in a command, I want to program to find "something like it" for ex.

User Types: Hello there!

The computer will recognize "hello" and respond with something like "How are ya?"

I donno how else to explain this, if anyone knows what the hell I'm talking about, give a shout out....

Which Is The Best Seek Or Find
I have a huge SQL Table which contains 157,000 Rows. Find is very very very very slow. Any body tell what to do so find a row near the end of table.

Thanks best reguards

Seek Method
I am trying to use the seek method on SQL Server... The connection is through MS SQL Server OLE DB Provider. Is there another provider that supports Seek ?

SEEK Problem
This code give me error all the time: Current provider doesn`t support the neceessary interface for Index functionaly

Private Sub Command1_Click()
Private Sub Command1_Click()
Dim I As String
I = Text2.Text
Adodc1.Recordset.Seek I
MsgBox I
End Sub
I will be Glad to see Yours suggestoins

Thank you

Seek And Move
Hi guys, I have a little problem here: I have a form in vb6 connected to an acces2k db. There's a datacombo that loads the employees names. But I also open a bunch of other tables to get datas from. What I would like to do is for the user to be able to select a name and the recordset to move accordingly.

this is how the datacombo loads up:

With Me.dbcmbEmployee
Set .DataSource = adoPrimaryRS
.BoundColumn = "EmployeeID"
.DataField = "EmployeeID"

Set .RowSource = EmplRecSet
.ListField = "Name"
End With

being adoprimaryrs the main recordset and emplrecset the one I get the real names from based on the employeeID


ADO Seek Method
I am new to ADO , with DAO after seek i used to check if seek is success or not with "IF NOMATCH Then" but with ado nomatch doesnt work. what can i use to check if matching record has been found or not?


Seek Function
With file I/O I need to go to the beginning of a file... does this mean I have to just close it and reopen it each time? or is there a way to return to the beginning of the file again? This is using VB File I/O and not the FSO.

the seek function was my first guess, and in the object browser it says "Sets or returns the current read/write position within an open file"

but it is a function that only accepts a file handle parameter, so how you would SET the position using this doesnt make sense.. and further.. the MSDN says the seek function only gets.. not sets the position. Same as the LOC function.

apparently you can do this
Seek (iFileHandle), position
I guess it is just like all the other VB file I/O functions that don't work right with intellisense

Help With The Seek Function?
How do i use the seek function to get the text at e.g lets say line 35 of a text file? i was told i need to use the seek function for this, but i dont know how and i dont have any MSDN.

DAO Seek Problem
I am trying to use the Seek method to find a specific record in an Access XP database. I am using VB 6 and DAO.

At the module level I declared the variables for the database and the table. The recordset, DYNSTPLANS is opened as a dynaset when the MDI parent form is loaded. The criteria for search is a listbox. The field Plan_name is indexed.

This is the code I've written:

Dim Criteria As String

Criteria = lstPlan
DYNSTPLANS.Index = "Plan_name"
DYNSTPLANS.Seek "=", Criteria

If .NoMatch Then
MsgBox "Record not found"
End If
End With

It gives me error 3251, "Operation is not supported for this type of object", and highlights the line DYNSTPLANS.Index = "Plan_name". It also does not like the next line.

What am I doing wrong?

Thanks, Lee

Seek And Index

Did anyone ever use the Index and Seek methods of an ADO recordset? I get the error 3251 - Current Provider doesn't support the neccessary interface for Index functionality.

This is how I set the recordset, I tried a lot of different settings, nothing works.

VB Code:
Set rsPayment = New Recordset    rsPayment.CursorLocation = adUseServer    rsPayment.Open "Payments", db, adOpenKeyset, adLockOptimistic, adCmdTableDirect    rsPayment.Index = "PrimaryKey"

Seek In DAO Matches ? In ADO
What matches "seek" ,used for searching for a record in DAO, in in ADO ?

And plz provide a simple example if possible

Thanx in advance

I Need Help With The SEEK() Method

I need help using the seek method in VB 6.0 on a MS Access 97 database. I know the code below is wrong but I don't know why. If someone can please teach me how to use the seek method that would be cool. I am trying to check to see if the variable userName is equal to the database column User and would like to also compare the var password to the db column pass. Please help me out. Thanks in advance!


Private Sub login_Click()
userName = txtUser.Text
password = txtPass.Text
If userName <> "" Then
Data1.Recordset.Seek("=", user=userName)
If Data1.Recordset.NoMatch = True Then
MsgBox "You have entered an invalid username or password."
If optReg.Value = True Then
Load register
If optPrg.Value = True Then
Load DBAdmin
End If
End If
End If
MsgBox "Please enter a user name."
End If
End Sub

Seek In Oracle
I am using the Seek fuction to search for a value in an access table - however now I need to switch the table to Oracle.

Is there any function or stored procedure that can act like the seek function - I don't think I can use the find function as it does not take multiple keys.

I can't use select statements as they are too slow.

Surely some else has had this problem!!


Need Help!!! How To Use SEEK Method In ADO.
I want to use "SEEK method" in ADO object with my field (primary key-index) is string type but I cannot use SEEK for fast find. It's more difficult than DAO.
Thanks for your help.

How Can I Seek Array?
I suggest you use the VB Dictionary Object to store your data, because it can give you a better seek performance, yet you can add/remove any item in the dictionary at anytime.

Help Using The Seek And Put Statements
there is a text file containing several lines of text, how do i use the seek statement to obtain the writing position for a certain place corrosponding to the string character i want??

this is a test ok
second line

forth line, and the last line.

how do i get the above to appear like the below using the
seek and put statment??

this is a test ok
second line

forth line, and the las[new string entry]t line.

thanks in advance

Seek Method On Ado
Is Any one know how to use seek method on Ado ?????


hok sun

Seek Method
Goodmorning all.
I have MS Access database connected to a VB Form using ADO.

The Access query has the following fields ID(Primary Key), FleetNumber, RegistrationNumber, PlantDescription, AccountNumber, DataEntry (Yes/No).

The user needs to type into a text box,ether a Plant or Registration number. The form should then display the fields for that record i.e all the fields shown above.

Using Seek (dao) Partial Key
I have an .mdb file open with a table called Title, there is an index called Composer.
This index has two fields in it, Composer / Title

Am i able to do a seek on this index for only the Composer.

if i use rs.Seek Lk, adSeekAfterEQ    '''' not working ????

Appreciate any help

ADO Seek Command
I am trying to use ADO's seek command against data that I returned from a Sybase SQL server via a stored procedure.

When I issue the seek command, I get a VB error that says "Object or provider is not capable of performing requested operation".  The provider I'm using is MSDASQL.

Any suggestions?

Seek Method Of DAO
I have connected with Access Database with DAO and I use seek method to find record which contains part of search string.

e.g. My search string is "bicy" and the word I' m looking for is "bicycle"

I use the follwoing code:

Dim strsearch as string

rs.Index = "DescriptionItem"
rs.Seek "=", strsearch

Results: rs.NoMatch = true

My code could'n found record "bicycle"

Can you help me?

Best regards

Michael Sakellis

someone can explain me how the seek metod is applicable at SQL Server 7 or 2000.
if is possible write an example.

I'm afraid for my english.
Thanks at all

Seek Command
hello frnds..

I'm trying to sort my records using this

dim rs as recordset

rs.index = "rollno" ' field Name

is this correct bcz it is giving me as error

and = ???

plz. let know how to search a record...


i have a database with 3 forms covering different data... i wont to create a main search that will search all 3 tables ta once and return the corresponding a new table with all the info from the table related to the search any ideas, or examples of this.....

thanks in advance

Seek For A Record With RDO
Hi )

Does anybody know how to locate a record with RDO programming ? When I use Visual FoxPro, I use the functin seek() or "locate for", whether there is an index or not. But how with VB when using RDO ?

The researche should, at best, locate the nearest match for the user entry.

Thank you ;-)

Duke1000 :-)

Seek For A Record With RDO
Hi )

Does anybody know how to locate a record with RDO programming ? When I use Visual FoxPro, I use the functin seek() or "locate for", whether there is an index or not. But how with VB when using RDO ?

The researche should, at best, locate the nearest match for the user entry.

Thank you ;-)

Duke1000 :-)

Using Mcicommand To Seek

ive been working on a school project, i am makig a multimedia player.

i am at the point where i want to like let the user change the position of where the file is, or seeking

i am trying this to seek but it wont work

Call mciSendCommand(MMControl1.DeviceID, MCI_SEEK, Slider1.Value, 0&)

slider1 is the seek bar that the max is then len of the wave file

if you could help me with that , please respond

thank you,


Seek Method
I want to use the Seek method (rather than the find method) with an Access database. I get an error message stating that the provider is not capable of performing the operation. Doesn't access support the seek method? If not, why?

Seek To Position In A File?
How do you seek to a position in a file. In C++ you can use the tellg() function but i don't see a similar one for vb, is there?


Copyright 2005-08, All rights reserved