Pasting formula with defined name

[R. Choate]

I want to paste a formula which has a defined name and does not exist in any cell on any worksheet before I paste it. I know that
defined name formulas can be referred to by other formulas on a worksheet, but I want to paste the defined name formulas
programmatically. I know that there are other ways to accomplish what I'm doing in my project without using the named formulas, but
using them would be a lot cleaner for me and I want to take advantage of that feature if possible. Any ideas?

 

[JulieD]

Hi

is this what you're after

Sub pasteformula()
Range("D1").Formula = "=SUM(myname)"
End Sub

Cheers
JulieD

 

[R. Choate]

No, what I'm referring to is where the formula itself has a "range name" instead of the normal case where a range name refers to
cells on a worksheet. Lets say the formula is "=B1-B2" and the formula's name is "NetPounds". I want to paste my formula in various
places and refer to it by its name, "NetPounds". To use your example, my code would look more like:

Sub pasteformula()
Range("D1").Formula = "NetPounds"
End Sub

Any ideas?

Thanks!
--
RMC,CPA


Hi

is this what you're after

Sub pasteformula()
Range("D1").Formula = "=SUM(myname)"
End Sub

Cheers
JulieD

 

[Trevor Shuttleworth]

Try:

Sub pasteformula()
Range("D1").Formula = "=NetPounds"
End Sub

Be careful though because the range formula will be relative to the cell you
were in when you created the Named Range Formula.

For example, I tested your example in cell A1 with the formula =B1-B2. When
I copied the formula to cell A6 it refers to =B6-B7.

Regards

Trevor

No, what I'm referring to is where the formula itself has a "range name"

instead of the normal case where a range name refers to

cells on a worksheet. Lets say the formula is "=B1-B2" and the formula's

name is "NetPounds". I want to paste my formula in various

 

[R. Choate]

That was it. I had left off the "=" at the beginning of the rangename. How stupid of me, but many thanks to you for putting me back
on track. With regard to the relative referencing, that is just perfect as it is because I need to copy it across.

Thanks much.
--
RMC,CPA


Try:

Sub pasteformula()
Range("D1").Formula = "=NetPounds"
End Sub

Be careful though because the range formula will be relative to the cell you
were in when you created the Named Range Formula.

For example, I tested your example in cell A1 with the formula =B1-B2. When
I copied the formula to cell A6 it refers to =B6-B7.

Regards

Trevor

No, what I'm referring to is where the formula itself has a "range name"

instead of the normal case where a range name refers to

cells on a worksheet. Lets say the formula is "=B1-B2" and the formula's

name is "NetPounds". I want to paste my formula in various