To run the formula from any active cell

P

pol

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
 
M

Mike H

Hi,

Maybe this

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

Mike
 
P

pol

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 said:
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 said:
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
Click to expand...
Click to expand...
 
D

Dave Peterson

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.
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
Click to expand...
 
J

JLatham

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 said:
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
P

pol

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


Mike H said:
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 said:
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
Click to expand...
Click to expand...
 
P

pol

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

Many thanks


Dave Peterson said:
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.
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
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

to create a formula by removing RC[number] 2
To check two sheets in a single file in Vlookup() 1
Need a help display 0 value 2
to set rownumber 3
Countif and Vlookup in a single formula 2
to workout the formula from many column.. 1
to workout the forumula from any column. 1
Macro 3

Top