How do I use VBA to add a formula?

[animal1881]

hello Xpertz!

I'm sure this question has been asked, but anyway...

I need to do some math on a cost sheet. Simple stuff:

Range(row1, column3) = Range(row1,column1)*Range(row1,column2)

What I would like to do is make column3 autoupdate whenever I chang
column1 or column2. I tried using formulas like this:

Range(row1, column3).Formula = "=" & Range(row1,column1).Address(True
True, , True)*Range(row1,column2).Address(True, True, , True)

But that only makes column3 show a zero. Worse yet, sometimes column
or 2 is empty or a string. I noticed that if I manually enter
formula on the complete table it works fine, only when I try it in vb
do I have a problem

 

[Bob Phillips]

Why use VBA? Worksheet formulae will do it

=A1*A2

and copy down

 

[Frank Kabel]

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

 

[animal1881]

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
very large number of these sheets that need to be evaluated, somewher
around 500. I have written the code to go through a directory and ge
all the files, open them one by one and extract the data I need, it'
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 th
column names. There are 45 different columns. I am thinking I migh
need to change the order of them on the spreadsheet to suit my boss'
fickle tastes. If I were to change column 1 to column 5, then I woul
need to go back and re-write all the formulas

 

[Frank Kabel]

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