To Generate 500 almost identical VBA Lines?

[daniroy@gmail.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

[Arvi Laanemets]

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
>

[daniroy@gmail.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

[Arvi Laanemets]

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
>

[daniroy@gmail.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

[Arvi Laanemets]

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
>

[rjb]

I should of sent how I did the code. It was done in an Excel spreadsheet
with this formula:
="Range(""y"&A1&""").GoalSeek
Goal:=ActiveSheet.Range(""z"&A1&""").Value,ChangingCell:=Sheets(""FTSE"").Ra
nge(""x"&A1&""")"
Put 10 to 500 in column A and the above formula in column B

<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
>

[daniroy@gmail.com]

thanks a lot everybody, I appreciate your help and I may need you again
in a close future.
The real problem was that the goalseek was unable to run because the
very first row did not have any data to reach for. Thus I did add a
rule to check if first row must be taken into consideration or not.

Here is the final code. Thank you again everybody.


Sheets("FTSE").Select

For i = 10 To 500

If Range("z" & i) <> "" Then


Range("Y" & i).GoalSeek Goal:=Range("Z" & i).Value,
ChangingCell:=Range("X" & i)
Range("Y10").GoalSeek Goal:=706, ChangingCell:=Range("X10")
End If

Next i


End Sub

[Arvi Laanemets]

Hi

Why to recalculate row 10 along with every row?

....
Sheets("FTSE").Select
If Range("Z10")<>"" Then
Range("Y10").GoalSeek Goal:=Range("Z10").Value,
ChangingCell:=Range("X10")
Else
Range("Y10").GoalSeek Goal:=706, ChangingCell:=Range("X10")
End If
For i=11 To 500
If Range("z" & i) <> "" Then
Range("Y" & i).GoalSeek Goal:=Range("Z" & i).Value,
ChangingCell:=Range("X" & i)
End If
Next i
....


Arvi Laanemets


<daniroy@gmail.com> wrote in message
news:1122906082.960281.196530@z14g2000cwz.googlegroups.com...
> thanks a lot everybody, I appreciate your help and I may need you again
> in a close future.
> The real problem was that the goalseek was unable to run because the
> very first row did not have any data to reach for. Thus I did add a
> rule to check if first row must be taken into consideration or not.
>
> Here is the final code. Thank you again everybody.
>
>
> Sheets("FTSE").Select
>
> For i = 10 To 500
>
> If Range("z" & i) <> "" Then
>
>
> Range("Y" & i).GoalSeek Goal:=Range("Z" & i).Value,
> ChangingCell:=Range("X" & i)
> Range("Y10").GoalSeek Goal:=706, ChangingCell:=Range("X10")
> End If
>
> Next i
>
>
> End Sub
>