I've seen a lot of threads addressing changing formulas to values, but
haven't come across one going the other way. I have a spreadsheet that gets
created with all values. Column E is the sum of columns C and D. I'm trying
to figure out the code that will go through column E, and at every occurance
of a number, replace that number with the formula =C+D.

I tried using Isnumeric as a test, but it plugged that formula into all the
blank cells as well.
If IsNumeric(ActiveCell.Value) Then ActiveCell.FormulaR1C1 = "=RC[-2]+RC[-1]"

Suggestions?

I should have said

if not activecell.value like "*=*" then


"mattmac" wrote:

> I've seen a lot of threads addressing changing formulas to values, but
> haven't come across one going the other way. I have a spreadsheet that gets
> created with all values. Column E is the sum of columns C and D. I'm trying
> to figure out the code that will go through column E, and at every occurance
> of a number, replace that number with the formula =C+D.
>
> I tried using Isnumeric as a test, but it plugged that formula into all the
> blank cells as well.
> If IsNumeric(ActiveCell.Value) Then ActiveCell.FormulaR1C1 = "=RC[-2]+RC[-1]"
>
> Suggestions?

Try looking for the "=" sign.

if activecell.value like "*=*" then ...

"mattmac" wrote:

> I've seen a lot of threads addressing changing formulas to values, but
> haven't come across one going the other way. I have a spreadsheet that gets
> created with all values. Column E is the sum of columns C and D. I'm trying
> to figure out the code that will go through column E, and at every occurance
> of a number, replace that number with the formula =C+D.
>
> I tried using Isnumeric as a test, but it plugged that formula into all the
> blank cells as well.
> If IsNumeric(ActiveCell.Value) Then ActiveCell.FormulaR1C1 = "=RC[-2]+RC[-1]"
>
> Suggestions?

I'm sorry if I wasn't clear: The cells just have numbers in them, no
formulas. I want to change the value to a formula.

"Barb Reinhardt" wrote:

> Try looking for the "=" sign.
>
> if activecell.value like "*=*" then ...
>
> "mattmac" wrote:
>
> > I've seen a lot of threads addressing changing formulas to values, but
> > haven't come across one going the other way. I have a spreadsheet that gets
> > created with all values. Column E is the sum of columns C and D. I'm trying
> > to figure out the code that will go through column E, and at every occurance
> > of a number, replace that number with the formula =C+D.
> >
> > I tried using Isnumeric as a test, but it plugged that formula into all the
> > blank cells as well.
> > If IsNumeric(ActiveCell.Value) Then ActiveCell.FormulaR1C1 = "=RC[-2]+RC[-1]"
> >
> > Suggestions?

Try this

if not activecell.value like "*=*"

this will find the active cells that don't have a formula.


"mattmac" wrote:

> I'm sorry if I wasn't clear: The cells just have numbers in them, no
> formulas. I want to change the value to a formula.
>
> "Barb Reinhardt" wrote:
>
> > Try looking for the "=" sign.
> >
> > if activecell.value like "*=*" then ...
> >
> > "mattmac" wrote:
> >
> > > I've seen a lot of threads addressing changing formulas to values, but
> > > haven't come across one going the other way. I have a spreadsheet that gets
> > > created with all values. Column E is the sum of columns C and D. I'm trying
> > > to figure out the code that will go through column E, and at every occurance
> > > of a number, replace that number with the formula =C+D.
> > >
> > > I tried using Isnumeric as a test, but it plugged that formula into all the
> > > blank cells as well.
> > > If IsNumeric(ActiveCell.Value) Then ActiveCell.FormulaR1C1 = "=RC[-2]+RC[-1]"
> > >
> > > Suggestions?

How about?

If IsNumeric(ActiveCell.Value) and isempty(activecell.value) = False Then

--
Thanks,

Bruce Bolio


"mattmac" wrote:

> I've seen a lot of threads addressing changing formulas to values, but
> haven't come across one going the other way. I have a spreadsheet that gets
> created with all values. Column E is the sum of columns C and D. I'm trying
> to figure out the code that will go through column E, and at every occurance
> of a number, replace that number with the formula =C+D.
>
> I tried using Isnumeric as a test, but it plugged that formula into all the
> blank cells as well.
> If IsNumeric(ActiveCell.Value) Then ActiveCell.FormulaR1C1 = "=RC[-2]+RC[-1]"
>
> Suggestions?

Adding the "isempty" code works perfectly! Thanks!!!

I've tried the "if not activecell.value like "*=*" then" code and was still
getting the same error where all blank cells were getting populated with
formulas.

"Bruce Bolio" wrote:

> How about?
>
> If IsNumeric(ActiveCell.Value) and isempty(activecell.value) = False Then
>
> --
> Thanks,
>
> Bruce Bolio
>
>
> "mattmac" wrote:
>
> > I've seen a lot of threads addressing changing formulas to values, but
> > haven't come across one going the other way. I have a spreadsheet that gets
> > created with all values. Column E is the sum of columns C and D. I'm trying
> > to figure out the code that will go through column E, and at every occurance
> > of a number, replace that number with the formula =C+D.
> >
> > I tried using Isnumeric as a test, but it plugged that formula into all the
> > blank cells as well.
> > If IsNumeric(ActiveCell.Value) Then ActiveCell.FormulaR1C1 = "=RC[-2]+RC[-1]"
> >
> > Suggestions?