To Generate 500 almost identical VBA Lines?

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
>

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

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
>