To Generate 500 almost identical VBA Lines?

Hi

....
For i=10 To 500
Range("y" & i).GoalSeek Goal:=ActiveSheet.Range("z" & i).Value,
ChangingCell:=Sheets("FTSE").Range("x" & i)
Next For
....

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



<daniroy@gmail.com> wrote in message
news:1122882955.038904.117170@g47g2000cwa.googlegroups.com...
> Hi there
>
> I have to create 500 lines of almost identical VBA code. Code is
>
> Range("y10").GoalSeek Goal:=ActiveSheet.Range("z10").Value,
> ChangingCell:=Sheets("FTSE").Range("x10")
> Range("y11").GoalSeek Goal:=ActiveSheet.Range("z11").Value,
> ChangingCell:=Sheets("FTSE").Range("x11")
> Range("y12").GoalSeek Goal:=ActiveSheet.Range("z12").Value,
> ChangingCell:=Sheets("FTSE").Range("x12")
>
> and so on untill
>
> Range("y500").GoalSeek Goal:=ActiveSheet.Range("z500").Value,
> ChangingCell:=Sheets("FTSE").Range("x500")
>
> What is the most logical way to proceed, please ?
>
> All the best
> Daniel
>

Thank you Arvi, isn it suppose to be Next i at the end of the code ? It
is not working with Next For at the end. I receive the following
message: "Compile Error: Expected: variable".

Moreover, when I impliement the code with Next i at the end I receive
the following error message:
"Run-Time error '1004': Goal Seek method of Range class failed

Sorry to be a pain, but help is appreciated !
Regards
DR

Hi

Sorry, my mistake! It really must be
....
Next i


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


<daniroy@gmail.com> wrote in message
news:1122884778.668436.119860@g49g2000cwa.googlegroups.com...
> Thank you Arvi, isn it suppose to be Next i at the end of the code ? It
> is not working with Next For at the end. I receive the following
> message: "Compile Error: Expected: variable".
>
> Moreover, when I impliement the code with Next i at the end I receive
> the following error message:
> "Run-Time error '1004': Goal Seek method of Range class failed
>
> Sorry to be a pain, but help is appreciated !
> Regards
> DR
>

Thanks very much Arvi. Up to now, the formula is:

---

Sub GoalSeek()

Sheets("FTSE").Select

For i = 10 To 400


Range("y" & i).GoalSeek Goal:=ActiveSheet.Range("z" & i).Value,
ChangingCell:=Sheets("FTSE").Range("x" & i)

Next i

End Sub

---


But it is not working, I receive error message ...

To give more colors, I in facts want to add another criteria.
I want to implement the macro if and only if column Z is an integer, I
mean if column Z>0+epsilon.
Have you, or anybody, an idea of what should I do?

thanks for any help
Daniel

Hi

Sub GoalSeek()

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

End Sub


This works for me, when there is a formula in every cell in range
FTSE!X10:X400 - otherwise the error "Reference is not valid!" is returned
when the row without formula is reached. Into column Y on sheet FTSE are
calculated values, for which formulas in column X (formulas refer to column
Y as argument) on same sheet return same values as in according rows in
column Z on currently active sheet. NB! Values in column Z may be on any
sheet in workbook! You select a sheet with return values, start the
procedure - and new values for FTSE!Y10:Y400 are calculated. (And unexcepted
values are returned, when the formula in column X doesn't refer to column Y
at all.)

In your code, as you selected FTSE at start, active sheet was always FTSE.
When this was what you wanted initially, then why did you use ActiveSheet
instead of Sheets("FTSE") - or why didn't you use ActiveSheet everywhere
there (really it doesn't matter, but it is a bad style to mix things in such
way).

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



<daniroy@gmail.com> wrote in message
news:1122888795.571653.85960@f14g2000cwb.googlegroups.com...
> Thanks very much Arvi. Up to now, the formula is:
>
> ---
>
> Sub GoalSeek()
>
> Sheets("FTSE").Select
>
> For i = 10 To 400
>
>
> Range("y" & i).GoalSeek Goal:=ActiveSheet.Range("z" & i).Value,
> ChangingCell:=Sheets("FTSE").Range("x" & i)
>
> Next i
>
> End Sub
>
> ---
>
>
> But it is not working, I receive error message ...
>
> To give more colors, I in facts want to add another criteria.
> I want to implement the macro if and only if column Z is an integer, I
> mean if column Z>0+epsilon.
> Have you, or anybody, an idea of what should I do?
>
> thanks for any help
> Daniel
>

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 ?

tks
Daniel

And how can I skip to run the goalseek (when it will be working) if,
for example, cells Z10 to Z15 are empty ?

regards
Daniel

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 )