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