| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| how to use the active cell in an Excel formula | Excel Worksheet Functions | |||
| IF information is in cell A8 then Formula is active? | Excel Worksheet Functions | |||
| referring to formula in a non active cell from active cell | Excel Discussion (Misc queries) | |||
| Active Cell To Change Formula | Excel Discussion (Misc queries) | |||
| how to copy a trendline formula into an active cell ? | Excel Discussion (Misc queries) | |||
Hybrid Mode
