to create a formula by removing RC[number]
 

I am using the following forumula in a macro.

ActiveCell.FormulaR1C1 =

"=IF(COUNTIF([item.xls]item!C[-11],RC[-6]),VLOOKUP(RC[-6],[item.xls]Item!C[-11]:C[-1],11,0),0)"
. Here RC[-6]) is G7 column. So I have to write the same formula to work
from any column which is active cell.
I wrote the same formula as follows to enable to work from any of the column

=IF(COUNTIF([item.xls]item!B:B,G7),VLOOKUP(G7,[item.xls]item!B:L,11,0),0).
But the G7 value will be shown as 'G7' so #item# error is showing ie the
formula is coming wrongly
=IF(COUNTIF([item.xls]item!B:B,'G7'),VLOOKUP('G7',[item.xls]item!B:L,11,0),0).

It would be very helpful if anybody can help to solve this problem

With thanks
Pol

to create a formula by removing RC[number]
 

Check your other post.

pol wrote:

I am using the following forumula in a macro.

ActiveCell.FormulaR1C1 =

"=IF(COUNTIF([item.xls]item!C[-11],RC[-6]),VLOOKUP(RC[-6],[item.xls]Item!C[-11]:C[-1],11,0),0)"
. Here RC[-6]) is G7 column. So I have to write the same formula to work
from any column which is active cell.
I wrote the same formula as follows to enable to work from any of the column

=IF(COUNTIF([item.xls]item!B:B,G7),VLOOKUP(G7,[item.xls]item!B:L,11,0),0).
But the G7 value will be shown as 'G7' so #item# error is showing ie the
formula is coming wrongly
=IF(COUNTIF([item.xls]item!B:B,'G7'),VLOOKUP('G7',[item.xls]item!B:L,11,0),0).

It would be very helpful if anybody can help to solve this problem

With thanks
Pol

--

Dave Peterson

to create a formula by removing RC[number]
 

The answer I gave you in your other post works for me

"pol" wrote:

I am using the following forumula in a macro.

ActiveCell.FormulaR1C1 =

"=IF(COUNTIF([item.xls]item!C[-11],RC[-6]),VLOOKUP(RC[-6],[item.xls]Item!C[-11]:C[-1],11,0),0)"
. Here RC[-6]) is G7 column. So I have to write the same formula to work
from any column which is active cell.
I wrote the same formula as follows to enable to work from any of the column

=IF(COUNTIF([item.xls]item!B:B,G7),VLOOKUP(G7,[item.xls]item!B:L,11,0),0).
But the G7 value will be shown as 'G7' so #item# error is showing ie the
formula is coming wrongly
=IF(COUNTIF([item.xls]item!B:B,'G7'),VLOOKUP('G7',[item.xls]item!B:L,11,0),0).

It would be very helpful if anybody can help to solve this problem

With thanks
Pol