input conditional formulas using VBA Macro in axcel sheet

[Francesco]

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="";D17="";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

[Niek Otten]

Hi Francesco:

Worksheets("ouput").Range("B17").FormulaLocal =
"=SE(O_(B17="""";D17="""";D17=0);"""";CERCA.VERT(B17;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" <Francesco@discussions.microsoft.com> wrote in message news:F20697E3-84BC-4C7F-AB7E-69E964DBF106@microsoft.com...
> 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="";D17="";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

[Bob Phillips]

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" <nicolaus@xs4all.nl> wrote in message
news:%23%23NMOJjVGHA.5916@TK2MSFTNGP12.phx.gbl...
> Hi Francesco:
>
> Worksheets("ouput").Range("B17").FormulaLocal =
>
"=SE(O_(B17="""";D17="""";D17=0);"""";CERCA.VERT(B17;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" <Francesco@discussions.microsoft.com> wrote in message
news:F20697E3-84BC-4C7F-AB7E-69E964DBF106@microsoft.com...
> > 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="";D17="";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
>
>

[Francesco]

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" <nicolaus@xs4all.nl> wrote in message
> news:%23%23NMOJjVGHA.5916@TK2MSFTNGP12.phx.gbl...
> > Hi Francesco:
> >
> > Worksheets("ouput").Range("B17").FormulaLocal =
> >
> "=SE(O_(B17="""";D17="""";D17=0);"""";CERCA.VERT(B17;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" <Francesco@discussions.microsoft.com> wrote in message
> news:F20697E3-84BC-4C7F-AB7E-69E964DBF106@microsoft.com...
> > > 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="";D17="";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
> >
> >
>
>
>

[Francesco]

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(B17;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" <Francesco@discussions.microsoft.com> wrote in message news:F20697E3-84BC-4C7F-AB7E-69E964DBF106@microsoft.com...
> > 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="";D17="";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
>
>
>

[Niek Otten]

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" <Francesco@discussions.microsoft.com> wrote in message news:CCEB3ED5-0155-4EBE-BDE3-788971F5D5AB@microsoft.com...
> 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(B17;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" <Francesco@discussions.microsoft.com> wrote in message news:F20697E3-84BC-4C7F-AB7E-69E964DBF106@microsoft.com...
>> > 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="";D17="";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
>>
>>
>>

[Francesco]

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(B17;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" <Francesco@discussions.microsoft.com> wrote in message news:CCEB3ED5-0155-4EBE-BDE3-788971F5D5AB@microsoft.com...
> > 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(B17;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" <Francesco@discussions.microsoft.com> wrote in message news:F20697E3-84BC-4C7F-AB7E-69E964DBF106@microsoft.com...
> >> > 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="";D17="";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
> >>
> >>
> >>
>
>
>

[Francesco]

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(B17;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" <Francesco@discussions.microsoft.com> wrote in message news:F20697E3-84BC-4C7F-AB7E-69E964DBF106@microsoft.com...
> > 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="";D17="";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
>
>
>