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

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

Why use VBA? Worksheet formulae will do it

=A1*A2

and copy down

--
HTH

-------

Bob Phillips
"animal1881 " wrote in message
...
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 change
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 column1
or 2 is empty or a string. I noticed that if I manually enter a
formula on the complete table it works fine, only when I try it in vba
do I have a problem.


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

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

--
Regards
Frank Kabel
Frankfurt, Germany


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 change
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
column1
or 2 is empty or a string. I noticed that if I manually enter a
formula on the complete table it works fine, only when I try it in
vba
do I have a problem.


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

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

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

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/