| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
From a fixed cell to a variable
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
From a fixed cell to a variable
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
From a fixed cell to a variable
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
From a fixed cell to a variable
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
From a fixed cell to a variable
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
From a fixed cell to a variable
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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
From a fixed cell to a variable
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 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
From a fixed cell to a variable
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 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How fill series containing one variable and one fixed value in XL | Excel Discussion (Misc queries) | |||
| percent of a fixed and variable number | Excel Discussion (Misc queries) | |||
| Fixed column- Variable row # | Excel Discussion (Misc queries) | |||
| Variable column to fixed array | Excel Worksheet Functions | |||
| reference cell value from fixed column with variable row | Excel Discussion (Misc queries) | |||
Linear Mode
