to workout the formula from many column..

  • Thread starter Thread starter pol
  • Start date Start date
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
 
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

Back
Top