| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
input conditional formulas using VBA Macro in axcel sheet
Rangeusing WindowsXP professional and excel 2003 (excel is in Italian) as I
am writing from Rome Italy and I need some help: What I need is to in put a formula (if,true, false) in the cells of my excel. the the macro I have used is: Worksheets("output").Range("B17")="=SE(O_(B17="";D 17="";D17=0);"";CERCA.VERT(B17;input!$A$3:$B$1000; 2;FALSO)*D17)" I get an error (runtime 1004) I do not know if it due to the fact that VBA macro doesn't recognice the instruction in italian in the formula ) Traslation:( SE= IF, CERCA.VERT= SEARCH Vertical) I tried to use IF and VLOOKUP in place of the italian but got same error Please can any one help me? Thanks a lot Francesco |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
input conditional formulas using VBA Macro in axcel sheet
Hi Francesco:
Worksheets("ouput").Range("B17").FormulaLocal = "=SE(O_(B17="""";D17="""";D17=0);"""";CERCA.VERT(B 17;input!$A$3:$B$1000;2;FALSO)*D17)" or Worksheets("ouput").Range("B17").Formula = "=IF(O_(B17="""";D17="""";D17=0);"""";VLOOKUP(B17; input!$A$3:$B$1000;2;FALSE)*D17)" Note the double quotes between quotes -- Kind regards, Niek Otten "Francesco" wrote in message ... Rangeusing WindowsXP professional and excel 2003 (excel is in Italian) as I am writing from Rome Italy and I need some help: What I need is to in put a formula (if,true, false) in the cells of my excel. the the macro I have used is: Worksheets("output").Range("B17")="=SE(O_(B17="";D 17="";D17=0);"";CERCA.VERT(B17;input!$A$3:$B$1000; 2;FALSO)*D17)" I get an error (runtime 1004) I do not know if it due to the fact that VBA macro doesn't recognice the instruction in italian in the formula ) Traslation:( SE= IF, CERCA.VERT= SEARCH Vertical) I tried to use IF and VLOOKUP in place of the italian but got same error Please can any one help me? Thanks a lot Francesco |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
input conditional formulas using VBA Macro in axcel sheet
small typo
Worksheets("ouput").Range("B17").Formula = "=IF(OR(B17="""",D17="""",D17=0),"""",VLOOKUP(B17, input!$A$3:$B$1000,2,FALSE )*D17)" -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Niek Otten" wrote in message ... Hi Francesco: Worksheets("ouput").Range("B17").FormulaLocal = "=SE(O_(B17="""";D17="""";D17=0);"""";CERCA.VERT(B 17;input!$A$3:$B$1000;2;FA LSO)*D17)" or Worksheets("ouput").Range("B17").Formula = "=IF(O_(B17="""";D17="""";D17=0);"""";VLOOKUP(B17; input!$A$3:$B$1000;2;FALSE )*D17)" Note the double quotes between quotes -- Kind regards, Niek Otten "Francesco" wrote in message ... Rangeusing WindowsXP professional and excel 2003 (excel is in Italian) as I am writing from Rome Italy and I need some help: What I need is to in put a formula (if,true, false) in the cells of my excel. the the macro I have used is: Worksheets("output").Range("B17")="=SE(O_(B17="";D 17="";D17=0);"";CERCA.VERT (B17;input!$A$3:$B$1000;2;FALSO)*D17)" I get an error (runtime 1004) I do not know if it due to the fact that VBA macro doesn't recognice the instruction in italian in the formula ) Traslation:( SE= IF, CERCA.VERT= SEARCH Vertical) I tried to use IF and VLOOKUP in place of the italian but got same error Please can any one help me? Thanks a lot Francesco |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
input conditional formulas using VBA Macro in axcel sheet
Hi Niek Otten
Thanks for your suggestion but neither one of the 2 solution works, macro stops at that line for a runtime erroro number 9 and a statement " index not inclided in the interval" Any othe suggestion? regards Francesco "Niek Otten" wrote: Hi Francesco: Worksheets("ouput").Range("B17").FormulaLocal = "=SE(O_(B17="""";D17="""";D17=0);"""";CERCA.VERT(B 17;input!$A$3:$B$1000;2;FALSO)*D17)" or Worksheets("ouput").Range("B17").Formula = "=IF(O(B17="""";D17="""";D17=0);"""";VLOOKUP(B17;i nput!$A$3:$B$1000;2;FALSE)*D17)" Note the double quotes between quotes -- Kind regards, Niek Otten "Francesco" wrote in message ... Rangeusing WindowsXP professional and excel 2003 (excel is in Italian) as I am writing from Rome Italy and I need some help: What I need is to in put a formula (if,true, false) in the cells of my excel. the the macro I have used is: Worksheets("output").Range("B17")="=SE(O_(B17="";D 17="";D17=0);"";CERCA.VERT(B17;input!$A$3:$B$1000; 2;FALSO)*D17)" I get an error (runtime 1004) I do not know if it due to the fact that VBA macro doesn't recognice the instruction in italian in the formula ) Traslation:( SE= IF, CERCA.VERT= SEARCH Vertical) I tried to use IF and VLOOKUP in place of the italian but got same error Please can any one help me? Thanks a lot Francesco |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
input conditional formulas using VBA Macro in axcel sheet
Thanks Bob but I get same type of error ( error type" 9" and index not
included in the interval) "Bob Phillips" wrote: small typo Worksheets("ouput").Range("B17").Formula = "=IF(OR(B17="""",D17="""",D17=0),"""",VLOOKUP(B17, input!$A$3:$B$1000,2,FALSE )*D17)" -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Niek Otten" wrote in message ... Hi Francesco: Worksheets("ouput").Range("B17").FormulaLocal = "=SE(O_(B17="""";D17="""";D17=0);"""";CERCA.VERT(B 17;input!$A$3:$B$1000;2;FA LSO)*D17)" or Worksheets("ouput").Range("B17").Formula = "=IF(O_(B17="""";D17="""";D17=0);"""";VLOOKUP(B17; input!$A$3:$B$1000;2;FALSE )*D17)" Note the double quotes between quotes -- Kind regards, Niek Otten "Francesco" wrote in message ... Rangeusing WindowsXP professional and excel 2003 (excel is in Italian) as I am writing from Rome Italy and I need some help: What I need is to in put a formula (if,true, false) in the cells of my excel. the the macro I have used is: Worksheets("output").Range("B17")="=SE(O_(B17="";D 17="";D17=0);"";CERCA.VERT (B17;input!$A$3:$B$1000;2;FALSO)*D17)" I get an error (runtime 1004) I do not know if it due to the fact that VBA macro doesn't recognice the instruction in italian in the formula ) Traslation:( SE= IF, CERCA.VERT= SEARCH Vertical) I tried to use IF and VLOOKUP in place of the italian but got same error Please can any one help me? Thanks a lot Francesco |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
input conditional formulas using VBA Macro in axcel sheet
Bob pointed to a typo (O_ instead of OR), and I misspelled "output"
After correction of the typo, the statement works for me from VBE. However, you have circular references (B17); shouldn't they refer to another sheet? -- Kind regards, Niek Otten "Francesco" wrote in message ... Hi Niek Otten Thanks for your suggestion but neither one of the 2 solution works, macro stops at that line for a runtime erroro number 9 and a statement " index not inclided in the interval" Any othe suggestion? regards Francesco "Niek Otten" wrote: Hi Francesco: Worksheets("ouput").Range("B17").FormulaLocal = "=SE(O_(B17="""";D17="""";D17=0);"""";CERCA.VERT(B 17;input!$A$3:$B$1000;2;FALSO)*D17)" or Worksheets("ouput").Range("B17").Formula = "=IF(O(B17="""";D17="""";D17=0);"""";VLOOKUP(B17;i nput!$A$3:$B$1000;2;FALSE)*D17)" Note the double quotes between quotes -- Kind regards, Niek Otten "Francesco" wrote in message ... Rangeusing WindowsXP professional and excel 2003 (excel is in Italian) as I am writing from Rome Italy and I need some help: What I need is to in put a formula (if,true, false) in the cells of my excel. the the macro I have used is: Worksheets("output").Range("B17")="=SE(O_(B17="";D 17="";D17=0);"";CERCA.VERT(B17;input!$A$3:$B$1000; 2;FALSO)*D17)" I get an error (runtime 1004) I do not know if it due to the fact that VBA macro doesn't recognice the instruction in italian in the formula ) Traslation:( SE= IF, CERCA.VERT= SEARCH Vertical) I tried to use IF and VLOOKUP in place of the italian but got same error Please can any one help me? Thanks a lot Francesco |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
input conditional formulas using VBA Macro in axcel sheet
Hi Niek Otten
Wow !!! Thanks finally worked fine. The macro I used is the one in which you suggested "FormulaLocal" thanks a lot again Francesco "Niek Otten" wrote: Hi Francesco: Worksheets("output").Range("B17").FormulaLocal = "=SE(O_(B17="""";D17="""";D17=0);"""";CERCA.VERT(B 17;input!$A$3:$B$1000;2;FALSO)*D17)" or Worksheets("ouput").Range("B17").Formula = "=IF(O_(B17="""";D17="""";D17=0);"""";VLOOKUP(B17; input!$A$3:$B$1000;2;FALSE)*D17)" Note the double quotes between quotes -- Kind regards, Niek Otten "Francesco" wrote in message ... Rangeusing WindowsXP professional and excel 2003 (excel is in Italian) as I am writing from Rome Italy and I need some help: What I need is to in put a formula (if,true, false) in the cells of my excel. the the macro I have used is: Worksheets("output").Range("B17")="=SE(O_(B17="";D 17="";D17=0);"";CERCA.VERT(B17;input!$A$3:$B$1000; 2;FALSO)*D17)" I get an error (runtime 1004) I do not know if it due to the fact that VBA macro doesn't recognice the instruction in italian in the formula ) Traslation:( SE= IF, CERCA.VERT= SEARCH Vertical) I tried to use IF and VLOOKUP in place of the italian but got same error Please can any one help me? Thanks a lot Francesco |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
input conditional formulas using VBA Macro in axcel sheet
Yes Niek, you are right with the circular reference B17 infact I mispelled
it, it must have been E17 the formula that work perfectly and that I implemented is: Worksheets("output").range("E17:E32").FormulaLocal = "=SE(O(B17="""";D17="""";D17=0);"""";CERCA.VERT(B1 7;input!$A$3:$B$1000;2;FALSO)*D17)" "Niek Otten" wrote: Bob pointed to a typo (O_ instead of OR), and I misspelled "output" After correction of the typo, the statement works for me from VBE. However, you have circular references (B17); shouldn't they refer to another sheet? -- Kind regards, Niek Otten "Francesco" wrote in message ... Hi Niek Otten Thanks for your suggestion but neither one of the 2 solution works, macro stops at that line for a runtime erroro number 9 and a statement " index not inclided in the interval" Any othe suggestion? regards Francesco "Niek Otten" wrote: Hi Francesco: Worksheets("ouput").Range("B17").FormulaLocal = "=SE(O_(B17="""";D17="""";D17=0);"""";CERCA.VERT(B 17;input!$A$3:$B$1000;2;FALSO)*D17)" or Worksheets("ouput").Range("B17").Formula = "=IF(O(B17="""";D17="""";D17=0);"""";VLOOKUP(B17;i nput!$A$3:$B$1000;2;FALSE)*D17)" Note the double quotes between quotes -- Kind regards, Niek Otten "Francesco" wrote in message ... Rangeusing WindowsXP professional and excel 2003 (excel is in Italian) as I am writing from Rome Italy and I need some help: What I need is to in put a formula (if,true, false) in the cells of my excel. the the macro I have used is: Worksheets("output").Range("B17")="=SE(O_(B17="";D 17="";D17=0);"";CERCA.VERT(B17;input!$A$3:$B$1000; 2;FALSO)*D17)" I get an error (runtime 1004) I do not know if it due to the fact that VBA macro doesn't recognice the instruction in italian in the formula ) Traslation:( SE= IF, CERCA.VERT= SEARCH Vertical) I tried to use IF and VLOOKUP in place of the italian but got same error Please can any one help me? Thanks a lot Francesco |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Selecting Last Sheet | Excel Worksheet Functions | |||
| apply a macro to all sheets except for a certain sheet | Excel Discussion (Misc queries) | |||
| point to one number on each sheet that will be in many formulas | New Users to Excel | |||
| AUTO FILTER NOT CHANGING RESULTS | Excel Discussion (Misc queries) | |||
| Macro, select Sheet "Number", NOT Sheet Name | Excel Worksheet Functions | |||
Linear Mode
