From a fixed cell to a variable
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
|