From a fixed cell to a variable

[[email protected]]

hello all, and once again I am looking for help on a expression writing
issue.
I had the following code working just fine thanks to the help I
received here a few months ago.

Call_Ask =
"INDEX(ESX!$L$10:$L$600,MATCH(1,(ESX!$F$10:$F$600='ID'!D4)*(ESX!$G$10:$G$600='ID'!$R$2)*(ESX!$I$10:$I$600=""Call""),0))"

But (ESX!$F$10:$F$600='ID'!D4)
Cannot refer at D4 anymore but a number which can be between 4 and 13.
I am defining this exact line number using following code;

For i = 4 To 13
If Range("B" & i).Value = "Synthetic" Then Line1 = i

I thus tried to write the new code as

Call_Bid = "INDEX(ESX!$K$10:$K$600,MATCH(1,(ESX!$F$10:$F$600='ID'!""D&
Line1")*(ESX!$G$10:$G$600='ID'!$R$2)*(ESX!$I$10:$I$600=""Call""),0))"

Which obviously did not work. I am dumb but I am still missing the
logic when introducting variables into cell references...

Many thanks if anybody can help
Regards,
Daniel

[Tom Ogilvy]

Call_Ask =
"INDEX(ESX!$L$10:$L$600,MATCH(1,(ESX!$F$10:$F$600='ID'!DXXX)*(ESX!$G$10:$G$600='ID'!$R$2)*(ESX!$I$10:$I$600=""Call""),0))"

for i = 4 to 13
If Range("B" & i).Value = "Synthetic" Then
Call_Ask = Replace(Call_Ask,"XXX",i)
exit for
Next i

--
Regards,
Tom Ogilvy


"[email protected]" wrote:

> hello all, and once again I am looking for help on a expression writing
> issue.
> I had the following code working just fine thanks to the help I
> received here a few months ago.
>
> Call_Ask =
> "INDEX(ESX!$L$10:$L$600,MATCH(1,(ESX!$F$10:$F$600='ID'!D4)*(ESX!$G$10:$G$600='ID'!$R$2)*(ESX!$I$10:$I$600=""Call""),0))"
>
> But (ESX!$F$10:$F$600='ID'!D4)
> Cannot refer at D4 anymore but a number which can be between 4 and 13.
> I am defining this exact line number using following code;
>
> For i = 4 To 13
> If Range("B" & i).Value = "Synthetic" Then Line1 = i
>
> I thus tried to write the new code as
>
> Call_Bid = "INDEX(ESX!$K$10:$K$600,MATCH(1,(ESX!$F$10:$F$600='ID'!""D&
> Line1")*(ESX!$G$10:$G$600='ID'!$R$2)*(ESX!$I$10:$I$600=""Call""),0))"
>
> Which obviously did not work. I am dumb but I am still missing the
> logic when introducting variables into cell references...
>
> Many thanks if anybody can help
> Regards,
> Daniel
>
>

[[email protected]]

Many thanks Tom, I remember you already helped me some time ago!
I tried your solution, but without a lot of chance I am afraid.
When I arrive to
Synthetic_Value =3D Application.Evaluate(Call_Ask)
I am just receiving an error message.

At the moment, the code is

Sub ATEST()

Dim Call_Ask As String
Dim Synthetic_Value As Single

Call_Ask =3D "INDEX(ESX!$L$10:$L$600,MATCH(1,(ESX!$F$10:$F$600=3D'Implied
Dividend'!DXXX)*(ESX!$G$10:$G$=AD600=3D'Implied
Dividend'!$R$2)*(ESX!$I$10:$I$600=3D""Call""),0))"

For i =3D 4 To 13
If Range("B" & i).Value =3D "Synthetic" Then
Call_Ask =3D Replace(Call_Ask, "XXX", i)
Exit For
End If
Next i

Synthetic_Value =3D Application.Evaluate(Call_Ask)

End Sub

Moreover, tell me if I am wrong but the For / Next i cycle should not
be written at the top of the code?

kind regards
Daniel

Tom Ogilvy wrote:
> Call_Ask =3D
> "INDEX(ESX!$L$10:$L$600,MATCH(1,(ESX!$F$10:$F$600=3D'ID'!DXXX)*(ESX!$G$10=
:$G$600=3D'ID'!$R$2)*(ESX!$I$10:$I$600=3D""Call""),0))"
>
> for i =3D 4 to 13
> If Range("B" & i).Value =3D "Synthetic" Then
> Call_Ask =3D Replace(Call_Ask,"XXX",i)
> exit for
> Next i
>
> --
> Regards,
> Tom Ogilvy
>
>
> "[email protected]" wrote:
>
> > hello all, and once again I am looking for help on a expression writing
> > issue.
> > I had the following code working just fine thanks to the help I
> > received here a few months ago.
> >
> > Call_Ask =3D
> > "INDEX(ESX!$L$10:$L$600,MATCH(1,(ESX!$F$10:$F$600=3D'ID'!D4)*(ESX!$G$10=
:$G$600=3D'ID'!$R$2)*(ESX!$I$10:$I$600=3D""Call""),0))"
> >
> > But (ESX!$F$10:$F$600=3D'ID'!D4)
> > Cannot refer at D4 anymore but a number which can be between 4 and 13.
> > I am defining this exact line number using following code;
> >
> > For i =3D 4 To 13
> > If Range("B" & i).Value =3D "Synthetic" Then Line1 =3D i
> >
> > I thus tried to write the new code as
> >
> > Call_Bid =3D "INDEX(ESX!$K$10:$K$600,MATCH(1,(ESX!$F$10:$F$600=3D'ID'!"=
"D&
> > Line1")*(ESX!$G$10:$G$600=3D'ID'!$R$2)*(ESX!$I$10:$I$600=3D""Call""),0)=
)"
> >
> > Which obviously did not work. I am dumb but I am still missing the
> > logic when introducting variables into cell references...
> >
> > Many thanks if anybody can help
> > Regards,
> > Daniel
> >=20
> >

[[email protected]]

Many thanks Tom, I remember you already helped me some time ago!
I tried your solution, but without a lot of chance I am afraid.
When I arrive to
Synthetic_Value =3D Application.Evaluate(Call_Ask)
I am just receiving an error message.

At the moment, the code is

Sub ATEST()

Dim Call_Ask As String
Dim Synthetic_Value As Single

Call_Ask =3D "INDEX(ESX!$L$10:$L$600,MATCH(1,(ESX!$F$10:$F$600=3D'Implied
Dividend'!DXXX)*(ESX!$G$10:$G$=AD600=3D'Implied
Dividend'!$R$2)*(ESX!$I$10:$I$600=3D""Call""),0))"

For i =3D 4 To 13
If Range("B" & i).Value =3D "Synthetic" Then
Call_Ask =3D Replace(Call_Ask, "XXX", i)
Exit For
End If
Next i

Synthetic_Value =3D Application.Evaluate(Call_Ask)

End Sub

Moreover, tell me if I am wrong but the For / Next i cycle should not
be written at the top of the code?

kind regards
Daniel

Tom Ogilvy wrote:
> Call_Ask =3D
> "INDEX(ESX!$L$10:$L$600,MATCH(1,(ESX!$F$10:$F$600=3D'ID'!DXXX)*(ESX!$G$10=
:$G$600=3D'ID'!$R$2)*(ESX!$I$10:$I$600=3D""Call""),0))"
>
> for i =3D 4 to 13
> If Range("B" & i).Value =3D "Synthetic" Then
> Call_Ask =3D Replace(Call_Ask,"XXX",i)
> exit for
> Next i
>
> --
> Regards,
> Tom Ogilvy
>
>
> "[email protected]" wrote:
>
> > hello all, and once again I am looking for help on a expression writing
> > issue.
> > I had the following code working just fine thanks to the help I
> > received here a few months ago.
> >
> > Call_Ask =3D
> > "INDEX(ESX!$L$10:$L$600,MATCH(1,(ESX!$F$10:$F$600=3D'ID'!D4)*(ESX!$G$10=
:$G$600=3D'ID'!$R$2)*(ESX!$I$10:$I$600=3D""Call""),0))"
> >
> > But (ESX!$F$10:$F$600=3D'ID'!D4)
> > Cannot refer at D4 anymore but a number which can be between 4 and 13.
> > I am defining this exact line number using following code;
> >
> > For i =3D 4 To 13
> > If Range("B" & i).Value =3D "Synthetic" Then Line1 =3D i
> >
> > I thus tried to write the new code as
> >
> > Call_Bid =3D "INDEX(ESX!$K$10:$K$600,MATCH(1,(ESX!$F$10:$F$600=3D'ID'!"=
"D&
> > Line1")*(ESX!$G$10:$G$600=3D'ID'!$R$2)*(ESX!$I$10:$I$600=3D""Call""),0)=
)"
> >
> > Which obviously did not work. I am dumb but I am still missing the
> > logic when introducting variables into cell references...
> >
> > Many thanks if anybody can help
> > Regards,
> > Daniel
> >=20
> >

[[email protected]]

Many thanks Tom, I remember you already helped me some time ago!
I tried your solution, but without a lot of chance I am afraid.
When I arrive to
Synthetic_Value =3D Application.Evaluate(Call_Ask)
I am just receiving an error message.

At the moment, the code is

Sub ATEST()

Dim Call_Ask As String
Dim Synthetic_Value As Single

Call_Ask =3D "INDEX(ESX!$L$10:$L$600,MATCH(1,(ESX!$F$10:$F$600=3D'Implied
Dividend'!DXXX)*(ESX!$G$10:$G$=AD600=3D'Implied
Dividend'!$R$2)*(ESX!$I$10:$I$600=3D""Call""),0))"

For i =3D 4 To 13
If Range("B" & i).Value =3D "Synthetic" Then
Call_Ask =3D Replace(Call_Ask, "XXX", i)
Exit For
End If
Next i

Synthetic_Value =3D Application.Evaluate(Call_Ask)

End Sub

Moreover, tell me if I am wrong but the For / Next i cycle should not
be written at the top of the code?

kind regards
Daniel

Tom Ogilvy wrote:
> Call_Ask =3D
> "INDEX(ESX!$L$10:$L$600,MATCH(1,(ESX!$F$10:$F$600=3D'ID'!DXXX)*(ESX!$G$10=
:$G$600=3D'ID'!$R$2)*(ESX!$I$10:$I$600=3D""Call""),0))"
>
> for i =3D 4 to 13
> If Range("B" & i).Value =3D "Synthetic" Then
> Call_Ask =3D Replace(Call_Ask,"XXX",i)
> exit for
> Next i
>
> --
> Regards,
> Tom Ogilvy
>
>
> "[email protected]" wrote:
>
> > hello all, and once again I am looking for help on a expression writing
> > issue.
> > I had the following code working just fine thanks to the help I
> > received here a few months ago.
> >
> > Call_Ask =3D
> > "INDEX(ESX!$L$10:$L$600,MATCH(1,(ESX!$F$10:$F$600=3D'ID'!D4)*(ESX!$G$10=
:$G$600=3D'ID'!$R$2)*(ESX!$I$10:$I$600=3D""Call""),0))"
> >
> > But (ESX!$F$10:$F$600=3D'ID'!D4)
> > Cannot refer at D4 anymore but a number which can be between 4 and 13.
> > I am defining this exact line number using following code;
> >
> > For i =3D 4 To 13
> > If Range("B" & i).Value =3D "Synthetic" Then Line1 =3D i
> >
> > I thus tried to write the new code as
> >
> > Call_Bid =3D "INDEX(ESX!$K$10:$K$600,MATCH(1,(ESX!$F$10:$F$600=3D'ID'!"=
"D&
> > Line1")*(ESX!$G$10:$G$600=3D'ID'!$R$2)*(ESX!$I$10:$I$600=3D""Call""),0)=
)"
> >
> > Which obviously did not work. I am dumb but I am still missing the
> > logic when introducting variables into cell references...
> >
> > Many thanks if anybody can help
> > Regards,
> > Daniel
> >=20
> >

[Tom Ogilvy]

Where it appears is dependent on the the functionality you seek. If there
are several cells in row 4 to 13 that will contain the word synthetic and you
want to evaluate the formula for each one:

Sub ATEST()

Dim Call_Ask As String
Dim Synthetic_Value As Variant

Call_Ask = "INDEX(ESX!$L$10:$L$600,MATCH(1,(ESX!$F$10:$F$600='Implied
Dividend'!DXXX)*(ESX!$G$10:$G$Â*600='Implied
Dividend'!$R$2)*(ESX!$I$10:$I$600=""Call""),0))"

For i = 4 To 13
If lcase(Range("B" & i).Value) = "synthetic" Then
Call_Ask = Replace(Call_Ask, "XXX", i)
Synthetic_Value = Application.Evaluate(Call_Ask)
if not iserror(Synthetic_Value) then
msgbox Range("B" & i) & " " & Range("D" & i) & _
" " & Synthetic_Value
else
msgbox "Row: " & i & " Error Returned: " & Call_Ask
end if
End If
Next i

End Sub

--
Regards,
Tom Ogilvy

End Sub


"[email protected]" wrote:

> Many thanks Tom, I remember you already helped me some time ago!
> I tried your solution, but without a lot of chance I am afraid.
> When I arrive to
> Synthetic_Value = Application.Evaluate(Call_Ask)
> I am just receiving an error message.
>
> At the moment, the code is
>
> Sub ATEST()
>
> Dim Call_Ask As String
> Dim Synthetic_Value As Single
>
> Call_Ask = "INDEX(ESX!$L$10:$L$600,MATCH(1,(ESX!$F$10:$F$600='Implied
> Dividend'!DXXX)*(ESX!$G$10:$G$Â*600='Implied
> Dividend'!$R$2)*(ESX!$I$10:$I$600=""Call""),0))"
>
> For i = 4 To 13
> If Range("B" & i).Value = "Synthetic" Then
> Call_Ask = Replace(Call_Ask, "XXX", i)
> Exit For
> End If
> Next i
>
> Synthetic_Value = Application.Evaluate(Call_Ask)
>
> End Sub
>
> Moreover, tell me if I am wrong but the For / Next i cycle should not
> be written at the top of the code?
>
> kind regards
> Daniel
>
> Tom Ogilvy wrote:
> > Call_Ask =
> > "INDEX(ESX!$L$10:$L$600,MATCH(1,(ESX!$F$10:$F$600='ID'!DXXX)*(ESX!$G$10:$G$600='ID'!$R$2)*(ESX!$I$10:$I$600=""Call""),0))"
> >
> > for i = 4 to 13
> > If Range("B" & i).Value = "Synthetic" Then
> > Call_Ask = Replace(Call_Ask,"XXX",i)
> > exit for
> > Next i
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "[email protected]" wrote:
> >
> > > hello all, and once again I am looking for help on a expression writing
> > > issue.
> > > I had the following code working just fine thanks to the help I
> > > received here a few months ago.
> > >
> > > Call_Ask =
> > > "INDEX(ESX!$L$10:$L$600,MATCH(1,(ESX!$F$10:$F$600='ID'!D4)*(ESX!$G$10:$G$600='ID'!$R$2)*(ESX!$I$10:$I$600=""Call""),0))"
> > >
> > > But (ESX!$F$10:$F$600='ID'!D4)
> > > Cannot refer at D4 anymore but a number which can be between 4 and 13.
> > > I am defining this exact line number using following code;
> > >
> > > For i = 4 To 13
> > > If Range("B" & i).Value = "Synthetic" Then Line1 = i
> > >
> > > I thus tried to write the new code as
> > >
> > > Call_Bid = "INDEX(ESX!$K$10:$K$600,MATCH(1,(ESX!$F$10:$F$600='ID'!""D&
> > > Line1")*(ESX!$G$10:$G$600='ID'!$R$2)*(ESX!$I$10:$I$600=""Call""),0))"
> > >
> > > Which obviously did not work. I am dumb but I am still missing the
> > > logic when introducting variables into cell references...
> > >
> > > Many thanks if anybody can help
> > > Regards,
> > > Daniel
> > >
> > >
>
>

[[email protected]]

I am out of the loop, I dont understand why it is still not working.
There is only one, and will always be only one cell in row 4 to 13
that will contain the word "synthetic" (without brackets) and I thus
want to evaluate the formula only for that one...

using this code I can read an "Error 2015" error in the local windows
for "Synthetic_Value"]
I know I am a pain... many thanks for your help...

_________________________
Sub ATEST()

Dim Call_Ask As String
Dim Synthetic_Value As Variant

Call_Ask =3D "INDEX(ESX!$L$10:$L$600,MATCH(1,(ESX!$F$10:$F$600=3D'Implied
Dividend '!DXXX)*(ESX!$G$10:$G$=AD600=3D'Implied Dividend
'!$R$2)*(ESX!$I$10:$I$600=3D""Call""),0))"

For i =3D 4 To 13
If LCase(Range("B" & i).Value) =3D "synthetic" Then Call_Ask =3D
Replace(Call_Ask, "XXX", i)
Synthetic_Value =3D Application.Evaluate(Call_Ask)
Next i
End Sub
______________


Tom Ogilvy wrote:
> Where it appears is dependent on the the functionality you seek. If there
> are several cells in row 4 to 13 that will contain the word synthetic and=
you
> want to evaluate the formula for each one:
>
> Sub ATEST()
>
> Dim Call_Ask As String
> Dim Synthetic_Value As Variant
>
> Call_Ask =3D "INDEX(ESX!$L$10:$L$600,MATCH(1,(ESX!$F$10:$F$600=3D'Implied
> Dividend'!DXXX)*(ESX!$G$10:$G$=AD600=3D'Implied
> Dividend'!$R$2)*(ESX!$I$10:$I$600=3D""Call""),0))"
>
> For i =3D 4 To 13
> If lcase(Range("B" & i).Value) =3D "synthetic" Then
> Call_Ask =3D Replace(Call_Ask, "XXX", i)
> Synthetic_Value =3D Application.Evaluate(Call_Ask)
> if not iserror(Synthetic_Value) then
> msgbox Range("B" & i) & " " & Range("D" & i) & _
> " " & Synthetic_Value
> else
> msgbox "Row: " & i & " Error Returned: " & Call_Ask
> end if
> End If
> Next i
>
> End Sub
>
> --
> Regards,
> Tom Ogilvy
>
> End Sub
>
>
> "[email protected]" wrote:
>
> > Many thanks Tom, I remember you already helped me some time ago!
> > I tried your solution, but without a lot of chance I am afraid.
> > When I arrive to
> > Synthetic_Value =3D Application.Evaluate(Call_Ask)
> > I am just receiving an error message.
> >
> > At the moment, the code is
> >
> > Sub ATEST()
> >
> > Dim Call_Ask As String
> > Dim Synthetic_Value As Single
> >
> > Call_Ask =3D "INDEX(ESX!$L$10:$L$600,MATCH(1,(ESX!$F$10:$F$600=3D'Impli=
ed
> > Dividend'!DXXX)*(ESX!$G$10:$G$=AD600=3D'Implied
> > Dividend'!$R$2)*(ESX!$I$10:$I$600=3D""Call""),0))"
> >
> > For i =3D 4 To 13
> > If Range("B" & i).Value =3D "Synthetic" Then
> > Call_Ask =3D Replace(Call_Ask, "XXX", i)
> > Exit For
> > End If
> > Next i
> >
> > Synthetic_Value =3D Application.Evaluate(Call_Ask)
> >
> > End Sub
> >
> > Moreover, tell me if I am wrong but the For / Next i cycle should not
> > be written at the top of the code?
> >
> > kind regards
> > Daniel
> >
> > Tom Ogilvy wrote:
> > > Call_Ask =3D
> > > "INDEX(ESX!$L$10:$L$600,MATCH(1,(ESX!$F$10:$F$600=3D'ID'!DXXX)*(ESX!$=
G$10:$G$600=3D'ID'!$R$2)*(ESX!$I$10:$I$600=3D""Call""),0))"
> > >
> > > for i =3D 4 to 13
> > > If Range("B" & i).Value =3D "Synthetic" Then
> > > Call_Ask =3D Replace(Call_Ask,"XXX",i)
> > > exit for
> > > Next i
> > >
> > > --
> > > Regards,
> > > Tom Ogilvy
> > >
> > >
> > > "[email protected]" wrote:
> > >
> > > > hello all, and once again I am looking for help on a expression wri=
ting
> > > > issue.
> > > > I had the following code working just fine thanks to the help I
> > > > received here a few months ago.
> > > >
> > > > Call_Ask =3D
> > > > "INDEX(ESX!$L$10:$L$600,MATCH(1,(ESX!$F$10:$F$600=3D'ID'!D4)*(ESX!$=
G$10:$G$600=3D'ID'!$R$2)*(ESX!$I$10:$I$600=3D""Call""),0))"
> > > >
> > > > But (ESX!$F$10:$F$600=3D'ID'!D4)
> > > > Cannot refer at D4 anymore but a number which can be between 4 and =
13.
> > > > I am defining this exact line number using following code;
> > > >
> > > > For i =3D 4 To 13
> > > > If Range("B" & i).Value =3D "Synthetic" Then Line1 =3D i
> > > >
> > > > I thus tried to write the new code as
> > > >
> > > > Call_Bid =3D "INDEX(ESX!$K$10:$K$600,MATCH(1,(ESX!$F$10:$F$600=3D'I=
D'!""D&
> > > > Line1")*(ESX!$G$10:$G$600=3D'ID'!$R$2)*(ESX!$I$10:$I$600=3D""Call""=
),0))"
> > > >
> > > > Which obviously did not work. I am dumb but I am still missing the
> > > > logic when introducting variables into cell references...
> > > >
> > > > Many thanks if anybody can help
> > > > Regards,
> > > > Daniel
> > > >=20
> > > >
> >=20
> >

[Tom Ogilvy]

You didn't write your code the same way I did. You single line IF statement
construct tries to do the evaluate regardless of whether the line contains
snythetic or not.

When I set up data that would actually have a line that has all the
criteria, the altered code worked fine for me:

Sub ATEST()

Dim Call_Ask As String
Dim Synthetic_Value As Variant

Call_Ask = "INDEX(ESX!$L$10:$L$600,MATCH" & _
"(1,(ESX!$F$10:$F$600='Implied Dividend'!" & _
"DXXX)*(ESX!$G$10:$G$600='Implied Dividend'!" & _
"$R$2)*(ESX!$I$10:$I$600=""Call""),0))"

For i = 4 To 13
If LCase(Range("B" & i).Value) = "synthetic" Then
Call_Ask = Replace(Call_Ask, "XXX", i)
Synthetic_Value = Application.Evaluate(Call_Ask)
End If
Next i
MsgBox Synthetic_Value
End Sub

If you copy this out of the posting, inspect it and make sure there are no
extra dashes ("-") embedded in the code, particularly in the formula.
Somehow these sometimes get embedded.

--
Regards,
Tom Ogilvy


"[email protected]" wrote:

> I am out of the loop, I dont understand why it is still not working.
> There is only one, and will always be only one cell in row 4 to 13
> that will contain the word "synthetic" (without brackets) and I thus
> want to evaluate the formula only for that one...
>
> using this code I can read an "Error 2015" error in the local windows
> for "Synthetic_Value"]
> I know I am a pain... many thanks for your help...
>
> _________________________
> Sub ATEST()
>
> Dim Call_Ask As String
> Dim Synthetic_Value As Variant
>
> Call_Ask = "INDEX(ESX!$L$10:$L$600,MATCH(1,(ESX!$F$10:$F$600='Implied
> Dividend '!DXXX)*(ESX!$G$10:$G$Â*600='Implied Dividend
> '!$R$2)*(ESX!$I$10:$I$600=""Call""),0))"
>
> For i = 4 To 13
> If LCase(Range("B" & i).Value) = "synthetic" Then Call_Ask =
> Replace(Call_Ask, "XXX", i)
> Synthetic_Value = Application.Evaluate(Call_Ask)
> Next i
> End Sub
> ______________
>
>
> Tom Ogilvy wrote:
> > Where it appears is dependent on the the functionality you seek. If there
> > are several cells in row 4 to 13 that will contain the word synthetic and you
> > want to evaluate the formula for each one:
> >
> > Sub ATEST()
> >
> > Dim Call_Ask As String
> > Dim Synthetic_Value As Variant
> >
> > Call_Ask = "INDEX(ESX!$L$10:$L$600,MATCH(1,(ESX!$F$10:$F$600='Implied
> > Dividend'!DXXX)*(ESX!$G$10:$G$Â*600='Implied
> > Dividend'!$R$2)*(ESX!$I$10:$I$600=""Call""),0))"
> >
> > For i = 4 To 13
> > If lcase(Range("B" & i).Value) = "synthetic" Then
> > Call_Ask = Replace(Call_Ask, "XXX", i)
> > Synthetic_Value = Application.Evaluate(Call_Ask)
> > if not iserror(Synthetic_Value) then
> > msgbox Range("B" & i) & " " & Range("D" & i) & _
> > " " & Synthetic_Value
> > else
> > msgbox "Row: " & i & " Error Returned: " & Call_Ask
> > end if
> > End If
> > Next i
> >
> > End Sub
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> > End Sub
> >
> >
> > "[email protected]" wrote:
> >
> > > Many thanks Tom, I remember you already helped me some time ago!
> > > I tried your solution, but without a lot of chance I am afraid.
> > > When I arrive to
> > > Synthetic_Value = Application.Evaluate(Call_Ask)
> > > I am just receiving an error message.
> > >
> > > At the moment, the code is
> > >
> > > Sub ATEST()
> > >
> > > Dim Call_Ask As String
> > > Dim Synthetic_Value As Single
> > >
> > > Call_Ask = "INDEX(ESX!$L$10:$L$600,MATCH(1,(ESX!$F$10:$F$600='Implied
> > > Dividend'!DXXX)*(ESX!$G$10:$G$Â*600='Implied
> > > Dividend'!$R$2)*(ESX!$I$10:$I$600=""Call""),0))"
> > >
> > > For i = 4 To 13
> > > If Range("B" & i).Value = "Synthetic" Then
> > > Call_Ask = Replace(Call_Ask, "XXX", i)
> > > Exit For
> > > End If
> > > Next i
> > >
> > > Synthetic_Value = Application.Evaluate(Call_Ask)
> > >
> > > End Sub
> > >
> > > Moreover, tell me if I am wrong but the For / Next i cycle should not
> > > be written at the top of the code?
> > >
> > > kind regards
> > > Daniel
> > >
> > > Tom Ogilvy wrote:
> > > > Call_Ask =
> > > > "INDEX(ESX!$L$10:$L$600,MATCH(1,(ESX!$F$10:$F$600='ID'!DXXX)*(ESX!$G$10:$G$600='ID'!$R$2)*(ESX!$I$10:$I$600=""Call""),0))"
> > > >
> > > > for i = 4 to 13
> > > > If Range("B" & i).Value = "Synthetic" Then
> > > > Call_Ask = Replace(Call_Ask,"XXX",i)
> > > > exit for
> > > > Next i
> > > >
> > > > --
> > > > Regards,
> > > > Tom Ogilvy
> > > >
> > > >
> > > > "[email protected]" wrote:
> > > >
> > > > > hello all, and once again I am looking for help on a expression writing
> > > > > issue.
> > > > > I had the following code working just fine thanks to the help I
> > > > > received here a few months ago.
> > > > >
> > > > > Call_Ask =
> > > > > "INDEX(ESX!$L$10:$L$600,MATCH(1,(ESX!$F$10:$F$600='ID'!D4)*(ESX!$G$10:$G$600='ID'!$R$2)*(ESX!$I$10:$I$600=""Call""),0))"
> > > > >
> > > > > But (ESX!$F$10:$F$600='ID'!D4)
> > > > > Cannot refer at D4 anymore but a number which can be between 4 and 13.
> > > > > I am defining this exact line number using following code;
> > > > >
> > > > > For i = 4 To 13
> > > > > If Range("B" & i).Value = "Synthetic" Then Line1 = i
> > > > >
> > > > > I thus tried to write the new code as
> > > > >
> > > > > Call_Bid = "INDEX(ESX!$K$10:$K$600,MATCH(1,(ESX!$F$10:$F$600='ID'!""D&
> > > > > Line1")*(ESX!$G$10:$G$600='ID'!$R$2)*(ESX!$I$10:$I$600=""Call""),0))"
> > > > >
> > > > > Which obviously did not work. I am dumb but I am still missing the
> > > > > logic when introducting variables into cell references...
> > > > >
> > > > > Many thanks if anybody can help
> > > > > Regards,
> > > > > Daniel
> > > > >
> > > > >
> > >
> > >
>
>

[[email protected]]

Dear Tom, many thanks for your help and sorry not to have been able to
come back to you earlier as the morning as been a bit busy for me.

The code is fine, it is running smoothly except it is unable to
evaluate Call_Ask

I am using the follwing code. I removed the MsgBox, and if I do not
want to learn you what you are teaching me, may the problem is on
ESX!$F$10:$F$600=3D'Implied Dividend'!DXXX
especially 'Implied Dividend'!DXXX, no?
many thanks, once again, Tom.
Regards
Dan

_______________

Sub ATEST()

Dim Call_Ask As String
Dim Synthetic_Value As Variant
Call_Ask =3D "INDEX(ESX!$L$10:$L$600,MATCH(1,(ESX!$F$10:$F$600=3D'Implied
Dividend'!DXXX)*(ESX!$G$10:$G$600=3D'Implied Dividend
'!$R$2)*(ESX!$I$10:$I$600=3D""Call""),0))"

For i =3D 4 To 13
Worksheets("Implied Dividend").Select
If LCase(Range("B" & i).Value) =3D "synthetic" Then Call_Ask =3D
Replace(Call_Ask, "XXX", i)
Synthetic_Value =3D Application.Evaluate(Call_Ask)
Next i
End Sub

__________________

Tom Ogilvy wrote:
> You didn't write your code the same way I did. You single line IF statem=
ent
> construct tries to do the evaluate regardless of whether the line contains
> snythetic or not.
>
> When I set up data that would actually have a line that has all the
> criteria, the altered code worked fine for me:
>
> Sub ATEST()
>
> Dim Call_Ask As String
> Dim Synthetic_Value As Variant
>
> Call_Ask =3D "INDEX(ESX!$L$10:$L$600,MATCH" & _
> "(1,(ESX!$F$10:$F$600=3D'Implied Dividend'!" & _
> "DXXX)*(ESX!$G$10:$G$600=3D'Implied Dividend'!" & _
> "$R$2)*(ESX!$I$10:$I$600=3D""Call""),0))"
>
> For i =3D 4 To 13
> If LCase(Range("B" & i).Value) =3D "synthetic" Then
> Call_Ask =3D Replace(Call_Ask, "XXX", i)
> Synthetic_Value =3D Application.Evaluate(Call_Ask)
> End If
> Next i
> MsgBox Synthetic_Value
> End Sub
>
> If you copy this out of the posting, inspect it and make sure there are no
> extra dashes ("-") embedded in the code, particularly in the formula.
> Somehow these sometimes get embedded.
>
> --
> Regards,
> Tom Ogilvy
>
>
> "[email protected]" wrote:
>
> > I am out of the loop, I dont understand why it is still not working.
> > There is only one, and will always be only one cell in row 4 to 13
> > that will contain the word "synthetic" (without brackets) and I thus
> > want to evaluate the formula only for that one...
> >
> > using this code I can read an "Error 2015" error in the local windows
> > for "Synthetic_Value"]
> > I know I am a pain... many thanks for your help...
> >
> > _________________________
> > Sub ATEST()
> >
> > Dim Call_Ask As String
> > Dim Synthetic_Value As Variant
> >
> > Call_Ask =3D "INDEX(ESX!$L$10:$L$600,MATCH(1,(ESX!$F$10:$F$600=3D'Impli=
ed
> > Dividend '!DXXX)*(ESX!$G$10:$G$=AD600=3D'Implied Dividend
> > '!$R$2)*(ESX!$I$10:$I$600=3D""Call""),0))"
> >
> > For i =3D 4 To 13
> > If LCase(Range("B" & i).Value) =3D "synthetic" Then Call_Ask =3D
> > Replace(Call_Ask, "XXX", i)
> > Synthetic_Value =3D Application.Evaluate(Call_Ask)
> > Next i
> > End Sub
> > ______________
> >
> >
> > Tom Ogilvy wrote:
> > > Where it appears is dependent on the the functionality you seek. If =
there
> > > are several cells in row 4 to 13 that will contain the word synthetic=
and you
> > > want to evaluate the formula for each one:
> > >
> > > Sub ATEST()
> > >
> > > Dim Call_Ask As String
> > > Dim Synthetic_Value As Variant
> > >
> > > Call_Ask =3D "INDEX(ESX!$L$10:$L$600,MATCH(1,(ESX!$F$10:$F$600=3D'Imp=
lied
> > > Dividend'!DXXX)*(ESX!$G$10:$G$=AD600=3D'Implied
> > > Dividend'!$R$2)*(ESX!$I$10:$I$600=3D""Call""),0))"
> > >
> > > For i =3D 4 To 13
> > > If lcase(Range("B" & i).Value) =3D "synthetic" Then
> > > Call_Ask =3D Replace(Call_Ask, "XXX", i)
> > > Synthetic_Value =3D Application.Evaluate(Call_Ask)
> > > if not iserror(Synthetic_Value) then
> > > msgbox Range("B" & i) & " " & Range("D" & i) & _
> > > " " & Synthetic_Value
> > > else
> > > msgbox "Row: " & i & " Error Returned: " & Call_Ask
> > > end if
> > > End If
> > > Next i
> > >
> > > End Sub
> > >
> > > --
> > > Regards,
> > > Tom Ogilvy
> > >
> > > End Sub
> > >
> > >
> > > "[email protected]" wrote:
> > >
> > > > Many thanks Tom, I remember you already helped me some time ago!
> > > > I tried your solution, but without a lot of chance I am afraid.
> > > > When I arrive to
> > > > Synthetic_Value =3D Application.Evaluate(Call_Ask)
> > > > I am just receiving an error message.
> > > >
> > > > At the moment, the code is
> > > >
> > > > Sub ATEST()
> > > >
> > > > Dim Call_Ask As String
> > > > Dim Synthetic_Value As Single
> > > >
> > > > Call_Ask =3D "INDEX(ESX!$L$10:$L$600,MATCH(1,(ESX!$F$10:$F$600=3D'I=
mplied
> > > > Dividend'!DXXX)*(ESX!$G$10:$G$=AD600=3D'Implied
> > > > Dividend'!$R$2)*(ESX!$I$10:$I$600=3D""Call""),0))"
> > > >
> > > > For i =3D 4 To 13
> > > > If Range("B" & i).Value =3D "Synthetic" Then
> > > > Call_Ask =3D Replace(Call_Ask, "XXX", i)
> > > > Exit For
> > > > End If
> > > > Next i
> > > >
> > > > Synthetic_Value =3D Application.Evaluate(Call_Ask)
> > > >
> > > > End Sub
> > > >
> > > > Moreover, tell me if I am wrong but the For / Next i cycle should n=
ot
> > > > be written at the top of the code?
> > > >
> > > > kind regards
> > > > Daniel
> > > >
> > > > Tom Ogilvy wrote:
> > > > > Call_Ask =3D
> > > > > "INDEX(ESX!$L$10:$L$600,MATCH(1,(ESX!$F$10:$F$600=3D'ID'!DXXX)*(E=
SX!$G$10:$G$600=3D'ID'!$R$2)*(ESX!$I$10:$I$600=3D""Call""),0))"
> > > > >
> > > > > for i =3D 4 to 13
> > > > > If Range("B" & i).Value =3D "Synthetic" Then
> > > > > Call_Ask =3D Replace(Call_Ask,"XXX",i)
> > > > > exit for
> > > > > Next i
> > > > >
> > > > > --
> > > > > Regards,
> > > > > Tom Ogilvy
> > > > >
> > > > >
> > > > > "[email protected]" wrote:
> > > > >
> > > > > > hello all, and once again I am looking for help on a expression=
writing
> > > > > > issue.
> > > > > > I had the following code working just fine thanks to the help I
> > > > > > received here a few months ago.
> > > > > >
> > > > > > Call_Ask =3D
> > > > > > "INDEX(ESX!$L$10:$L$600,MATCH(1,(ESX!$F$10:$F$600=3D'ID'!D4)*(E=
SX!$G$10:$G$600=3D'ID'!$R$2)*(ESX!$I$10:$I$600=3D""Call""),0))"
> > > > > >
> > > > > > But (ESX!$F$10:$F$600=3D'ID'!D4)
> > > > > > Cannot refer at D4 anymore but a number which can be between 4 =
and 13.
> > > > > > I am defining this exact line number using following code;
> > > > > >
> > > > > > For i =3D 4 To 13
> > > > > > If Range("B" & i).Value =3D "Synthetic" Then Line1 =3D i
> > > > > >
> > > > > > I thus tried to write the new code as
> > > > > >
> > > > > > Call_Bid =3D "INDEX(ESX!$K$10:$K$600,MATCH(1,(ESX!$F$10:$F$600=
=3D'ID'!""D&
> > > > > > Line1")*(ESX!$G$10:$G$600=3D'ID'!$R$2)*(ESX!$I$10:$I$600=3D""Ca=
ll""),0))"
> > > > > >
> > > > > > Which obviously did not work. I am dumb but I am still missing =
the
> > > > > > logic when introducting variables into cell references...
> > > > > >
> > > > > > Many thanks if anybody can help
> > > > > > Regards,
> > > > > > Daniel
> > > > > >=20
> > > > > >
> > > >=20
> > > >
> >=20
> >

[Tom Ogilvy]

I had not trouble evaluating the formula (Call_Ask).

Your If statement still remains the same as it was prior to my suggestion.
So I guess you want to proceed on your own.

--
Regards,
Tom Ogilvy




<[email protected]> wrote in message
news:[email protected]...
Dear Tom, many thanks for your help and sorry not to have been able to
come back to you earlier as the morning as been a bit busy for me.

The code is fine, it is running smoothly except it is unable to
evaluate Call_Ask

I am using the follwing code. I removed the MsgBox, and if I do not
want to learn you what you are teaching me, may the problem is on
ESX!$F$10:$F$600='Implied Dividend'!DXXX
especially 'Implied Dividend'!DXXX, no?
many thanks, once again, Tom.
Regards
Dan

_______________

Sub ATEST()

Dim Call_Ask As String
Dim Synthetic_Value As Variant
Call_Ask = "INDEX(ESX!$L$10:$L$600,MATCH(1,(ESX!$F$10:$F$600='Implied
Dividend'!DXXX)*(ESX!$G$10:$G$600='Implied Dividend
'!$R$2)*(ESX!$I$10:$I$600=""Call""),0))"

For i = 4 To 13
Worksheets("Implied Dividend").Select
If LCase(Range("B" & i).Value) = "synthetic" Then Call_Ask =
Replace(Call_Ask, "XXX", i)
Synthetic_Value = Application.Evaluate(Call_Ask)
Next i
End Sub

__________________

Tom Ogilvy wrote:
> You didn't write your code the same way I did. You single line IF
> statement
> construct tries to do the evaluate regardless of whether the line contains
> snythetic or not.
>
> When I set up data that would actually have a line that has all the
> criteria, the altered code worked fine for me:
>
> Sub ATEST()
>
> Dim Call_Ask As String
> Dim Synthetic_Value As Variant
>
> Call_Ask = "INDEX(ESX!$L$10:$L$600,MATCH" & _
> "(1,(ESX!$F$10:$F$600='Implied Dividend'!" & _
> "DXXX)*(ESX!$G$10:$G$600='Implied Dividend'!" & _
> "$R$2)*(ESX!$I$10:$I$600=""Call""),0))"
>
> For i = 4 To 13
> If LCase(Range("B" & i).Value) = "synthetic" Then
> Call_Ask = Replace(Call_Ask, "XXX", i)
> Synthetic_Value = Application.Evaluate(Call_Ask)
> End If
> Next i
> MsgBox Synthetic_Value
> End Sub
>
> If you copy this out of the posting, inspect it and make sure there are no
> extra dashes ("-") embedded in the code, particularly in the formula.
> Somehow these sometimes get embedded.
>
> --
> Regards,
> Tom Ogilvy
>
>
> "[email protected]" wrote:
>
> > I am out of the loop, I dont understand why it is still not working.
> > There is only one, and will always be only one cell in row 4 to 13
> > that will contain the word "synthetic" (without brackets) and I thus
> > want to evaluate the formula only for that one...
> >
> > using this code I can read an "Error 2015" error in the local windows
> > for "Synthetic_Value"]
> > I know I am a pain... many thanks for your help...
> >
> > _________________________
> > Sub ATEST()
> >
> > Dim Call_Ask As String
> > Dim Synthetic_Value As Variant
> >
> > Call_Ask = "INDEX(ESX!$L$10:$L$600,MATCH(1,(ESX!$F$10:$F$600='Implied
> > Dividend '!DXXX)*(ESX!$G$10:$G$*600='Implied Dividend
> > '!$R$2)*(ESX!$I$10:$I$600=""Call""),0))"
> >
> > For i = 4 To 13
> > If LCase(Range("B" & i).Value) = "synthetic" Then Call_Ask =
> > Replace(Call_Ask, "XXX", i)
> > Synthetic_Value = Application.Evaluate(Call_Ask)
> > Next i
> > End Sub
> > ______________
> >
> >
> > Tom Ogilvy wrote:
> > > Where it appears is dependent on the the functionality you seek. If
> > > there
> > > are several cells in row 4 to 13 that will contain the word synthetic
> > > and you
> > > want to evaluate the formula for each one:
> > >
> > > Sub ATEST()
> > >
> > > Dim Call_Ask As String
> > > Dim Synthetic_Value As Variant
> > >
> > > Call_Ask = "INDEX(ESX!$L$10:$L$600,MATCH(1,(ESX!$F$10:$F$600='Implied
> > > Dividend'!DXXX)*(ESX!$G$10:$G$*600='Implied
> > > Dividend'!$R$2)*(ESX!$I$10:$I$600=""Call""),0))"
> > >
> > > For i = 4 To 13
> > > If lcase(Range("B" & i).Value) = "synthetic" Then
> > > Call_Ask = Replace(Call_Ask, "XXX", i)
> > > Synthetic_Value = Application.Evaluate(Call_Ask)
> > > if not iserror(Synthetic_Value) then
> > > msgbox Range("B" & i) & " " & Range("D" & i) & _
> > > " " & Synthetic_Value
> > > else
> > > msgbox "Row: " & i & " Error Returned: " & Call_Ask
> > > end if
> > > End If
> > > Next i
> > >
> > > End Sub
> > >
> > > --
> > > Regards,
> > > Tom Ogilvy
> > >
> > > End Sub
> > >
> > >
> > > "[email protected]" wrote:
> > >
> > > > Many thanks Tom, I remember you already helped me some time ago!
> > > > I tried your solution, but without a lot of chance I am afraid.
> > > > When I arrive to
> > > > Synthetic_Value = Application.Evaluate(Call_Ask)
> > > > I am just receiving an error message.
> > > >
> > > > At the moment, the code is
> > > >
> > > > Sub ATEST()
> > > >
> > > > Dim Call_Ask As String
> > > > Dim Synthetic_Value As Single
> > > >
> > > > Call_Ask =
> > > > "INDEX(ESX!$L$10:$L$600,MATCH(1,(ESX!$F$10:$F$600='Implied
> > > > Dividend'!DXXX)*(ESX!$G$10:$G$*600='Implied
> > > > Dividend'!$R$2)*(ESX!$I$10:$I$600=""Call""),0))"
> > > >
> > > > For i = 4 To 13
> > > > If Range("B" & i).Value = "Synthetic" Then
> > > > Call_Ask = Replace(Call_Ask, "XXX", i)
> > > > Exit For
> > > > End If
> > > > Next i
> > > >
> > > > Synthetic_Value = Application.Evaluate(Call_Ask)
> > > >
> > > > End Sub
> > > >
> > > > Moreover, tell me if I am wrong but the For / Next i cycle should
> > > > not
> > > > be written at the top of the code?
> > > >
> > > > kind regards
> > > > Daniel
> > > >
> > > > Tom Ogilvy wrote:
> > > > > Call_Ask =
> > > > > "INDEX(ESX!$L$10:$L$600,MATCH(1,(ESX!$F$10:$F$600='ID'!DXXX)*(ESX!$G$10:$G$600='ID'!$R$2)*(ESX!$I$10:$I$600=""Call""),0))"
> > > > >
> > > > > for i = 4 to 13
> > > > > If Range("B" & i).Value = "Synthetic" Then
> > > > > Call_Ask = Replace(Call_Ask,"XXX",i)
> > > > > exit for
> > > > > Next i
> > > > >
> > > > > --
> > > > > Regards,
> > > > > Tom Ogilvy
> > > > >
> > > > >
> > > > > "[email protected]" wrote:
> > > > >
> > > > > > hello all, and once again I am looking for help on a expression
> > > > > > writing
> > > > > > issue.
> > > > > > I had the following code working just fine thanks to the help I
> > > > > > received here a few months ago.
> > > > > >
> > > > > > Call_Ask =
> > > > > > "INDEX(ESX!$L$10:$L$600,MATCH(1,(ESX!$F$10:$F$600='ID'!D4)*(ESX!$G$10:$G$600='ID'!$R$2)*(ESX!$I$10:$I$600=""Call""),0))"
> > > > > >
> > > > > > But (ESX!$F$10:$F$600='ID'!D4)
> > > > > > Cannot refer at D4 anymore but a number which can be between 4
> > > > > > and 13.
> > > > > > I am defining this exact line number using following code;
> > > > > >
> > > > > > For i = 4 To 13
> > > > > > If Range("B" & i).Value = "Synthetic" Then Line1 = i
> > > > > >
> > > > > > I thus tried to write the new code as
> > > > > >
> > > > > > Call_Bid =
> > > > > > "INDEX(ESX!$K$10:$K$600,MATCH(1,(ESX!$F$10:$F$600='ID'!""D&
> > > > > > Line1")*(ESX!$G$10:$G$600='ID'!$R$2)*(ESX!$I$10:$I$600=""Call""),0))"
> > > > > >
> > > > > > Which obviously did not work. I am dumb but I am still missing
> > > > > > the
> > > > > > logic when introducting variables into cell references...
> > > > > >
> > > > > > Many thanks if anybody can help
> > > > > > Regards,
> > > > > > Daniel
> > > > > >
> > > > > >
> > > >
> > > >
> >
> >

[[email protected]]

Tom, I am not sure to follow you as I did follow your suggestion, but
maybe am I missing a point here.
Don't mind, I can understand there can be frustration if it is working
on your side and I dont implement it correctly.
Kind regards
Daniel

Tom Ogilvy wrote:
> I had not trouble evaluating the formula (Call_Ask).
>
> Your If statement still remains the same as it was prior to my suggestion.
> So I guess you want to proceed on your own.
>
> --
> Regards,
> Tom Ogilvy
>
>
>
>
> <[email protected]> wrote in message
> news:[email protected]...
> Dear Tom, many thanks for your help and sorry not to have been able to
> come back to you earlier as the morning as been a bit busy for me.
>
> The code is fine, it is running smoothly except it is unable to
> evaluate Call_Ask
>
> I am using the follwing code. I removed the MsgBox, and if I do not
> want to learn you what you are teaching me, may the problem is on
> ESX!$F$10:$F$600=3D'Implied Dividend'!DXXX
> especially 'Implied Dividend'!DXXX, no?
> many thanks, once again, Tom.
> Regards
> Dan
>
> _______________
>
> Sub ATEST()
>
> Dim Call_Ask As String
> Dim Synthetic_Value As Variant
> Call_Ask =3D "INDEX(ESX!$L$10:$L$600,MATCH(1,(ESX!$F$10:$F$600=3D'Implied
> Dividend'!DXXX)*(ESX!$G$10:$G$600=3D'Implied Dividend
> '!$R$2)*(ESX!$I$10:$I$600=3D""Call""),0))"
>
> For i =3D 4 To 13
> Worksheets("Implied Dividend").Select
> If LCase(Range("B" & i).Value) =3D "synthetic" Then Call_Ask =3D
> Replace(Call_Ask, "XXX", i)
> Synthetic_Value =3D Application.Evaluate(Call_Ask)
> Next i
> End Sub
>
> __________________
>
> Tom Ogilvy wrote:
> > You didn't write your code the same way I did. You single line IF
> > statement
> > construct tries to do the evaluate regardless of whether the line conta=
ins
> > snythetic or not.
> >
> > When I set up data that would actually have a line that has all the
> > criteria, the altered code worked fine for me:
> >
> > Sub ATEST()
> >
> > Dim Call_Ask As String
> > Dim Synthetic_Value As Variant
> >
> > Call_Ask =3D "INDEX(ESX!$L$10:$L$600,MATCH" & _
> > "(1,(ESX!$F$10:$F$600=3D'Implied Dividend'!" & _
> > "DXXX)*(ESX!$G$10:$G$600=3D'Implied Dividend'!" & _
> > "$R$2)*(ESX!$I$10:$I$600=3D""Call""),0))"
> >
> > For i =3D 4 To 13
> > If LCase(Range("B" & i).Value) =3D "synthetic" Then
> > Call_Ask =3D Replace(Call_Ask, "XXX", i)
> > Synthetic_Value =3D Application.Evaluate(Call_Ask)
> > End If
> > Next i
> > MsgBox Synthetic_Value
> > End Sub
> >
> > If you copy this out of the posting, inspect it and make sure there are=
no
> > extra dashes ("-") embedded in the code, particularly in the formula.
> > Somehow these sometimes get embedded.
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "[email protected]" wrote:
> >
> > > I am out of the loop, I dont understand why it is still not working.
> > > There is only one, and will always be only one cell in row 4 to 13
> > > that will contain the word "synthetic" (without brackets) and I thus
> > > want to evaluate the formula only for that one...
> > >
> > > using this code I can read an "Error 2015" error in the local windows
> > > for "Synthetic_Value"]
> > > I know I am a pain... many thanks for your help...
> > >
> > > _________________________
> > > Sub ATEST()
> > >
> > > Dim Call_Ask As String
> > > Dim Synthetic_Value As Variant
> > >
> > > Call_Ask =3D "INDEX(ESX!$L$10:$L$600,MATCH(1,(ESX!$F$10:$F$600=3D'Imp=
lied
> > > Dividend '!DXXX)*(ESX!$G$10:$G$=AD600=3D'Implied Dividend
> > > '!$R$2)*(ESX!$I$10:$I$600=3D""Call""),0))"
> > >
> > > For i =3D 4 To 13
> > > If LCase(Range("B" & i).Value) =3D "synthetic" Then Call_Ask =3D
> > > Replace(Call_Ask, "XXX", i)
> > > Synthetic_Value =3D Application.Evaluate(Call_Ask)
> > > Next i
> > > End Sub
> > > ______________
> > >
> > >
> > > Tom Ogilvy wrote:
> > > > Where it appears is dependent on the the functionality you seek. If
> > > > there
> > > > are several cells in row 4 to 13 that will contain the word synthet=
ic
> > > > and you
> > > > want to evaluate the formula for each one:
> > > >
> > > > Sub ATEST()
> > > >
> > > > Dim Call_Ask As String
> > > > Dim Synthetic_Value As Variant
> > > >
> > > > Call_Ask =3D "INDEX(ESX!$L$10:$L$600,MATCH(1,(ESX!$F$10:$F$600=3D'I=
mplied
> > > > Dividend'!DXXX)*(ESX!$G$10:$G$=AD600=3D'Implied
> > > > Dividend'!$R$2)*(ESX!$I$10:$I$600=3D""Call""),0))"
> > > >
> > > > For i =3D 4 To 13
> > > > If lcase(Range("B" & i).Value) =3D "synthetic" Then
> > > > Call_Ask =3D Replace(Call_Ask, "XXX", i)
> > > > Synthetic_Value =3D Application.Evaluate(Call_Ask)
> > > > if not iserror(Synthetic_Value) then
> > > > msgbox Range("B" & i) & " " & Range("D" & i) & _
> > > > " " & Synthetic_Value
> > > > else
> > > > msgbox "Row: " & i & " Error Returned: " & Call_Ask
> > > > end if
> > > > End If
> > > > Next i
> > > >
> > > > End Sub
> > > >
> > > > --
> > > > Regards,
> > > > Tom Ogilvy
> > > >
> > > > End Sub
> > > >
> > > >
> > > > "[email protected]" wrote:
> > > >
> > > > > Many thanks Tom, I remember you already helped me some time ago!
> > > > > I tried your solution, but without a lot of chance I am afraid.
> > > > > When I arrive to
> > > > > Synthetic_Value =3D Application.Evaluate(Call_Ask)
> > > > > I am just receiving an error message.
> > > > >
> > > > > At the moment, the code is
> > > > >
> > > > > Sub ATEST()
> > > > >
> > > > > Dim Call_Ask As String
> > > > > Dim Synthetic_Value As Single
> > > > >
> > > > > Call_Ask =3D
> > > > > "INDEX(ESX!$L$10:$L$600,MATCH(1,(ESX!$F$10:$F$600=3D'Implied
> > > > > Dividend'!DXXX)*(ESX!$G$10:$G$=AD600=3D'Implied
> > > > > Dividend'!$R$2)*(ESX!$I$10:$I$600=3D""Call""),0))"
> > > > >
> > > > > For i =3D 4 To 13
> > > > > If Range("B" & i).Value =3D "Synthetic" Then
> > > > > Call_Ask =3D Replace(Call_Ask, "XXX", i)
> > > > > Exit For
> > > > > End If
> > > > > Next i
> > > > >
> > > > > Synthetic_Value =3D Application.Evaluate(Call_Ask)
> > > > >
> > > > > End Sub
> > > > >
> > > > > Moreover, tell me if I am wrong but the For / Next i cycle should
> > > > > not
> > > > > be written at the top of the code?
> > > > >
> > > > > kind regards
> > > > > Daniel
> > > > >
> > > > > Tom Ogilvy wrote:
> > > > > > Call_Ask =3D
> > > > > > "INDEX(ESX!$L$10:$L$600,MATCH(1,(ESX!$F$10:$F$600=3D'ID'!DXXX)*=
(ESX!$G$10:$G$600=3D'ID'!$R$2)*(ESX!$I$10:$I$600=3D""Call""),0))"
> > > > > >
> > > > > > for i =3D 4 to 13
> > > > > > If Range("B" & i).Value =3D "Synthetic" Then
> > > > > > Call_Ask =3D Replace(Call_Ask,"XXX",i)
> > > > > > exit for
> > > > > > Next i
> > > > > >
> > > > > > --
> > > > > > Regards,
> > > > > > Tom Ogilvy
> > > > > >
> > > > > >
> > > > > > "[email protected]" wrote:
> > > > > >
> > > > > > > hello all, and once again I am looking for help on a expressi=
on
> > > > > > > writing
> > > > > > > issue.
> > > > > > > I had the following code working just fine thanks to the help=
I
> > > > > > > received here a few months ago.
> > > > > > >
> > > > > > > Call_Ask =3D
> > > > > > > "INDEX(ESX!$L$10:$L$600,MATCH(1,(ESX!$F$10:$F$600=3D'ID'!D4)*=
(ESX!$G$10:$G$600=3D'ID'!$R$2)*(ESX!$I$10:$I$600=3D""Call""),0))"
> > > > > > >
> > > > > > > But (ESX!$F$10:$F$600=3D'ID'!D4)
> > > > > > > Cannot refer at D4 anymore but a number which can be between 4
> > > > > > > and 13.
> > > > > > > I am defining this exact line number using following code;
> > > > > > >
> > > > > > > For i =3D 4 To 13
> > > > > > > If Range("B" & i).Value =3D "Synthetic" Then Line1 =3D i
> > > > > > >
> > > > > > > I thus tried to write the new code as
> > > > > > >
> > > > > > > Call_Bid =3D
> > > > > > > "INDEX(ESX!$K$10:$K$600,MATCH(1,(ESX!$F$10:$F$600=3D'ID'!""D&
> > > > > > > Line1")*(ESX!$G$10:$G$600=3D'ID'!$R$2)*(ESX!$I$10:$I$600=3D""=
Call""),0))"
> > > > > > >
> > > > > > > Which obviously did not work. I am dumb but I am still missing
> > > > > > > the
> > > > > > > logic when introducting variables into cell references...
> > > > > > >
> > > > > > > Many thanks if anybody can help
> > > > > > > Regards,
> > > > > > > Daniel
> > > > > > >
> > > > > > >
> > > > >
> > > > >
> > >
> > >