To Generate 500 almost identical VBA Lines?

  • Thread starter Thread starter daniroy
  • Start date Start date
D

daniroy

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
 
Hi

....
For i=10 To 500
Range("y" & i).GoalSeek Goal:=ActiveSheet.Range("z" & i).Value,
ChangingCell:=Sheets("FTSE").Range("x" & i)
Next For
....
 
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).
 
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


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 ?
Click to expand...

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).
 
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.
 
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
 
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
 
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
 
Back
Top