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"

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"

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"

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"

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"

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"