Posted to microsoft.public.excel.programming
|
|
Relative Addresses in equations
Bob,
Just curious as to why you're populating .FormulaR1C1 as opposed to, say,
..Formula
Is there some advantage I'm not aware of?
--
Rob van Gelder - http://www.vangelder.co.nz/excel
"Bob Phillips" wrote in message
...
Type
?ActiveCell.FormulaR1C1 = "=14-" & Range("A1").Address(False,False)
in the immediate window, and you should see it effect.
Then try
?ActiveCell.FormulaR1C1 = "=14-" & Range("$A$1").Address(False,False)
and then
?ActiveCell.FormulaR1C1 = "=14-" & Range("A1:H10").Address(False,False)
--
HTH
RP
(remove nothere from the email address if mailing direct)
"OkieViking" wrote in message
...
Bob,
Right as I sent that last note, I realized a simpler solution to my
problem.
However, I am still curious to see the equation you gave me work for
educational reasons.
Thanks for the help.
"OkieViking" wrote:
Bob,
It is a small part of a fairly big program to update an even bigger
workbook. For it to make sense I would have to post both on the web
site,
which I am not allowed to do.
The spreadsheet contains three tables on one sheet. Each cell contains
an
array equation that searches a column for down time for each job
category.
The user only have to dump the down time into the spreadsheet from our
reporting program, and the tables will do all the tracking for the
user.
The
macro is written to let the user add a new job category (new row in
each
of
the three tables). The macro will populate the table with the proper
array
equations. I had no problems with the first two tables. The third
table is
all the trouble time for the given job type, minus the trouble time
captured
in the corresponding cell in the previous table. I will locate that
cell
with
a find function. Thus the question of some equation - myrange.
Then it turned out I was rustier than I thought, and range variables
was
always a weak spot.
"Bob Phillips" wrote:
Okie,
How exactly do you want to use this code? Show me the code that you
have so
far.
--
HTH
RP
(remove nothere from the email address if mailing direct)
"OkieViking" wrote in message
...
Bob,
Thanks for the help. I am rustier in VBA than I thought, so I
can't
make
it
work the way I want. Could you (or someone) write a small sub
using
the
line
below to set activecell to myrange, and then insert the equation
into "A2"
"Bob Phillips" wrote:
ActiveCell.FormulaR1C1 = "=14-" &
Range(myRange).Address(False,False)
--
HTH
RP
(remove nothere from the email address if mailing direct)
"OkieViking" wrote in
message
...
I am trying to write a Macro that will write an equation into
the
spreadsheet. This equation includes references to other cells.
I
want
the
equation to be linked to the other cell after the program is
run. (If
the
macro enters an eq. into cell A4 that depends on A1, the value
of A4
should
change whenever I change the value in A1.) The problem is that
I will
find
the address earlier in the macro. Here is a simplification:
if MyRange is the cell I want to link to (A1)
ActiveCell.FormulaR1C1 = "=14-" & Range(myRange)
the equation in A4 should be "=14-A1"
|