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"

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"

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"

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

--
Regards,
Tom Ogilvy

"Rob van Gelder" wrote in message
...
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"

Thanks Tom

--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Tom Ogilvy" wrote in message
...
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

--
Regards,
Tom Ogilvy

"Rob van Gelder" wrote in message
...
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"

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)


"Rob van Gelder" wrote in message
...
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"

Thanks Tom

"Tom Ogilvy" wrote:

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

--
Regards,
Tom Ogilvy

"Rob van Gelder" wrote in message
...
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"