Relative Addresses in equations

[Guest]

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"

 

[Bob Phillips]

ActiveCell.FormulaR1C1 = "=14-" & Range(myRange).Address(False,False)

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

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]

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)

 

[Guest]

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.

 

[Guest]

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.

 

[Bob Phillips]

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)

 

[Rob van Gelder]

Bob,

Just curious as to why you're populating .FormulaR1C1 as opposed to, say,
..Formula
Is there some advantage I'm not aware of?

 

[Tom Ogilvy]

I don't know, but if you do use FormulaR1C1 you should do

ActiveCell.FormulaR1C1 = "=14-" & Range("A1").Address(True,True,xlR1C1)

or the formula comes out
=14-'A1'

Note also True, true rather than false, false

 

[Rob van Gelder]

Thanks Tom

 

[Bob Phillips]

Hi Rob,

No an oversight on my part that I picked up from OPs original post.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

Thanks Tom

I don't know, but if you do use FormulaR1C1 you should do

ActiveCell.FormulaR1C1 = "=14-" & Range("A1").Address(True,True,xlR1C1)

or the formula comes out
=14-'A1'

Note also True, true rather than false, false