Hi
sure you can use variables :-)
e.g. if your variable col_index stores the column number try:
col_index=1
Range(row1, column3).formulaR1C1="=R[0]C" & col_index & "*R[0]C" &
col_index+1


--
Regards
Frank Kabel
Frankfurt, Germany


The problem with both solutions is that this macro is, well,
complicated.

Why use VBA? Worksheet formulae will do it

Yes, it can be easily done manually, the problem is that there is a
very large number of these sheets that need to be evaluated,
somewhere
around 500. I have written the code to go through a directory and
get
all the files, open them one by one and extract the data I need, it's
just this final computation I cant get past.

Range(row1, column3).formulaR1C1="=R[0]C[-2]*R[0]C[-1]"

I considered this, but it doesnt not allow variables to be used for
the column names. There are 45 different columns. I am thinking I
might need to change the order of them on the spreadsheet to suit my
boss's fickle tastes. If I were to change column 1 to column 5, then
I would need to go back and re-write all the formulas.


---
Message posted from http://www.ExcelForum.com/