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




wrote in message
oups.com...
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


" 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


" 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


" 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