View Single Post
  #5   Report Post  
Posted to microsoft.public.excel.misc
JLatham JLatham is offline
external usenet poster
 
Posts: 2,203
Default 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