change values to formulas
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? |
change values to formulas
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? |
change values to formulas
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? |
change values to formulas
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? |
change values to formulas
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? |
change values to formulas
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? |
change values to formulas
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? |
All times are GMT +1. The time now is 09:36 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com