To run the formula from any active cell
 

In my macros I wrote the formula as follows.
ActiveCell.FormulaR1C1 =
"=IF(COUNTIF([item.xls]item!C[-11],RC[-6]),VLOOKUP(RC[-6],[item.xls]Item!C[-11]:C[-1],11,0),0)"

the output formula would be as like this:
=IF(COUNTIF([item.xls]item!B:B,G7),VLOOKUP(G7,[item.xls]item!B:L,11,0),0).

So is it possible to write the formula the same as that of seconed formula
in my macro.Becuuse I need to run the formula from any active cell which may
be L7 or M7. So please help me to write that formula to enable to run from
any active cell.
With thanks
Pol

To run the formula from any active cell
 

Hi,

Maybe this

ActiveCell.Formula =
"=IF(COUNTIF([item.xls]item!B:B,G7),VLOOKUP(G7,[item.xls]item!B:L,11,0),0)"

Mike

"pol" wrote:

In my macros I wrote the formula as follows.
ActiveCell.FormulaR1C1 =
"=IF(COUNTIF([item.xls]item!C[-11],RC[-6]),VLOOKUP(RC[-6],[item.xls]Item!C[-11]:C[-1],11,0),0)"

the output formula would be as like this:
=IF(COUNTIF([item.xls]item!B:B,G7),VLOOKUP(G7,[item.xls]item!B:L,11,0),0).

So is it possible to write the formula the same as that of seconed formula
in my macro.Becuuse I need to run the formula from any active cell which may
be L7 or M7. So please help me to write that formula to enable to run from
any active cell.
With thanks
Pol

To run the formula from any active cell
 

Thanks for the reply But that formula is not working . I tried already that
formula but When I am running that macors the result will be as follows

=IF(COUNTIF([Item.xls]item!B:B,'G7'),VLOOKUP('G7',[Item.xls]item!B:L,11,0),0)

The G7 becomes 'G7' , so the result will be shown as as error #NAME#. But
when I am running that formula in formula bar it will work fine but will not
work on macors. Please help


"Mike H" wrote:

Hi,

Maybe this

ActiveCell.Formula =
"=IF(COUNTIF([item.xls]item!B:B,G7),VLOOKUP(G7,[item.xls]item!B:L,11,0),0)"

Mike

"pol" wrote:

In my macros I wrote the formula as follows.
ActiveCell.FormulaR1C1 =
"=IF(COUNTIF([item.xls]item!C[-11],RC[-6]),VLOOKUP(RC[-6],[item.xls]Item!C[-11]:C[-1],11,0),0)"

the output formula would be as like this:
=IF(COUNTIF([item.xls]item!B:B,G7),VLOOKUP(G7,[item.xls]item!B:L,11,0),0).

So is it possible to write the formula the same as that of seconed formula
in my macro.Becuuse I need to run the formula from any active cell which may
be L7 or M7. So please help me to write that formula to enable to run from
any active cell.
With thanks
Pol

To run the formula from any active cell
 

If you use .formular1c1, then all the addresses in your formula(r1c1) have to be
in R1C1 reference style.

But you don't have to use .formulaR1C1:

activecell.formula _
= "=IF(COUNTIF([item.xls]item!B:B,G7),VLOOKUP(G7,[item.xls]item!B:L,11,0),0)"

I'm not sure if you wanted a different formula if the activecell were in L7 or
M7, though.

pol wrote:

In my macros I wrote the formula as follows.
ActiveCell.FormulaR1C1 =
"=IF(COUNTIF([item.xls]item!C[-11],RC[-6]),VLOOKUP(RC[-6],[item.xls]Item!C[-11]:C[-1],11,0),0)"

the output formula would be as like this:
=IF(COUNTIF([item.xls]item!B:B,G7),VLOOKUP(G7,[item.xls]item!B:L,11,0),0).

So is it possible to write the formula the same as that of seconed formula
in my macro.Becuuse I need to run the formula from any active cell which may
be L7 or M7. So please help me to write that formula to enable to run from
any active cell.
With thanks
Pol

--

Dave Peterson

To run the formula from any active cell
 

In R1C1 notation a number within brackets is relative to the column/row that
the formula is in. If you always need to look in column G for the value to
test and match, then perhaps (formula in column L) use RC7 instead of RC[-6].
RC7 is the same as $G7
The other references could be changes in a similar fashion, as C2 for column
B instead of C[-11]; C2 would turn out to be the same as $B:$B.

Hope this helps.


"pol" wrote:

Thanks for the reply But that formula is not working . I tried already that
formula but When I am running that macors the result will be as follows

=IF(COUNTIF([Item.xls]item!B:B,'G7'),VLOOKUP('G7',[Item.xls]item!B:L,11,0),0)

The G7 becomes 'G7' , so the result will be shown as as error #NAME#. But
when I am running that formula in formula bar it will work fine but will not
work on macors. Please help


"Mike H" wrote:

Hi,

Maybe this

ActiveCell.Formula =
"=IF(COUNTIF([item.xls]item!B:B,G7),VLOOKUP(G7,[item.xls]item!B:L,11,0),0)"

Mike

"pol" wrote:

In my macros I wrote the formula as follows.
ActiveCell.FormulaR1C1 =
"=IF(COUNTIF([item.xls]item!C[-11],RC[-6]),VLOOKUP(RC[-6],[item.xls]Item!C[-11]:C[-1],11,0),0)"

the output formula would be as like this:
=IF(COUNTIF([item.xls]item!B:B,G7),VLOOKUP(G7,[item.xls]item!B:L,11,0),0).

So is it possible to write the formula the same as that of seconed formula
in my macro.Becuuse I need to run the formula from any active cell which may
be L7 or M7. So please help me to write that formula to enable to run from
any active cell.
With thanks
Pol

To run the formula from any active cell
 

You are correct . Thats my wrong because I given as FormulaR1C1 instead of
Formula. Many thanks


"Mike H" wrote:

Hi,

Maybe this

ActiveCell.Formula =
"=IF(COUNTIF([item.xls]item!B:B,G7),VLOOKUP(G7,[item.xls]item!B:L,11,0),0)"

Mike

"pol" wrote:

In my macros I wrote the formula as follows.
ActiveCell.FormulaR1C1 =
"=IF(COUNTIF([item.xls]item!C[-11],RC[-6]),VLOOKUP(RC[-6],[item.xls]Item!C[-11]:C[-1],11,0),0)"

the output formula would be as like this:
=IF(COUNTIF([item.xls]item!B:B,G7),VLOOKUP(G7,[item.xls]item!B:L,11,0),0).

So is it possible to write the formula the same as that of seconed formula
in my macro.Becuuse I need to run the formula from any active cell which may
be L7 or M7. So please help me to write that formula to enable to run from
any active cell.
With thanks
Pol

To run the formula from any active cell
 

Thanks a lot . I never noticed FormulaR1C1 instead of Formula.

Many thanks


"Dave Peterson" wrote:

If you use .formular1c1, then all the addresses in your formula(r1c1) have to be
in R1C1 reference style.

But you don't have to use .formulaR1C1:

activecell.formula _
= "=IF(COUNTIF([item.xls]item!B:B,G7),VLOOKUP(G7,[item.xls]item!B:L,11,0),0)"

I'm not sure if you wanted a different formula if the activecell were in L7 or
M7, though.

pol wrote:

In my macros I wrote the formula as follows.
ActiveCell.FormulaR1C1 =
"=IF(COUNTIF([item.xls]item!C[-11],RC[-6]),VLOOKUP(RC[-6],[item.xls]Item!C[-11]:C[-1],11,0),0)"

the output formula would be as like this:
=IF(COUNTIF([item.xls]item!B:B,G7),VLOOKUP(G7,[item.xls]item!B:L,11,0),0).

So is it possible to write the formula the same as that of seconed formula
in my macro.Becuuse I need to run the formula from any active cell which may
be L7 or M7. So please help me to write that formula to enable to run from
any active cell.
With thanks
Pol

--

Dave Peterson