To Generate 500 almost identical VBA Lines?

Hi


<daniroy@gmail.com> wrote in message
news:1122895287.302266.47610@o13g2000cwo.googlegroups.com...
> you are right Arvi, it is a bad mix ... so here is the "proper" code
>
> Sub GoalSeek()
>
>
> For i = 10 To 400
>
> Sheets("FTSE").Select
>
> Range("Y" & i).GoalSeek Goal:=Sheets("FTSE").Range("Z" & i).Value,
> ChangingCell:=Sheets("FTSE").Range("X" & i)
>
> Next i
>
>
> End Sub
>
> But it is still not working as I receive the following error message
> "GoalSeek method of Range class failed"
>
> Idea ?

No!
But ... maybe the sheet FTSE is protected?

And check in object browser, is GoalSeek function the member of class
Excel.Range (it must be).


--
Arvi Laanemets
( My real mail address: arvil<at>tarkon.ee )

No, the sheet is not protected, and GoalSeek is indeed part of the
Range Class ... I am lacking of solutions ....

I seem to recall that if the GoalSeek input cell's value is generating an
error in the target cell *before* the value gets changed by code, the
GoalSeek command generates an error before it executes. I had to seed the
input cell with a valid start before executing the GoalSeek command.



"daniroy@gmail.com" wrote:

> No, the sheet is not protected, and GoalSeek is indeed part of the
> Range Class ... I am lacking of solutions ....
>
>

Hi

Start from beginning.

Create a new workbook with a sheet FTSE
Create a procedure
---
Sub GoalSeek()

For i = 10 To 400
Sheets("FTSE").Range("X" & i).GoalSeek
Goal:=Sheets("FTSE").Range("Z" & i).Value, ChangingCell:=Sheets("FTSE").
Range("Y" & i)
Next i

End Sub
---
Into cell X10 on FTSE enter the formula
=Y10+1
Copy the formula into range X10:X400
Close VBA-editor and start the procedure - when all was right, then you
get -1 into range Y10:Y400
When it worked until this, replace the formula in FTSE!X10 with your own
one, and start the procedure again.
When you get an error, try with parts of your formula - for case the problem
is in your formula.
When you get the procedure working with formula, enter test value into cell
Z10, and run the procedure again - maybe the problem is there
So step-by-step you can allocate your problem and fix it. When it is fixed
for row 10, you can copy the formula into range X10:X400, and enter (Copy
from original workbook) the rest of test values


--
Arvi Laanemets
( My real mail address: arvil<at>tarkon.ee )


<daniroy@gmail.com> wrote in message
news:1122901623.167261.253040@g49g2000cwa.googlegroups.com...
> No, the sheet is not protected, and GoalSeek is indeed part of the
> Range Class ... I am lacking of solutions ....
>