to workout the formula from many column..

P

pol

ActiveCell.FormulaR1C1 =
"=IF(COUNTIF([ITEM.XLS]SHEET1!C[-11],RC[-6]),VLOOKUP(RC[-6],[ITEM.XLS]SHEET1!C[-11]:C[-1],11,0),0)"

How I can give the varibale for RC[-6] in the above formula. Because it
might not be the column before 6 in all the time. For example if I insert a
new column and that forumula should be worked out from the new column . So
please help to rewrite the forumula to workout from any column

With thanks
Pol
 
L

Luke M

Well, you'll need someway to define your variable (how do we know which
column to use?), but once you figure that out:

xColumn = -6
ActiveCell.FormulaR1C1 =
"=IF(COUNTIF([ITEM.XLS]SHEET1!C[-11],RC[" & xColumn & "]),VLOOKUP(RC[" &
xColumn & "],[ITEM.XLS]SHEET1!C[-11]:C[-1],11,0),0)"
 

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 workout the forumula from any column. 1
To run the formula from any active cell 6
Need a help display 0 value 2
To give paste special (value) in macros 1
Countif and Vlookup in a single formula 2
To check two sheets in a single file in Vlookup() 1
to set rownumber 3

Top