| 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 |
| 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) | |||
Hybrid Mode
