Replace Cell Value with Formula
 

I havea bout 30 spreadsheets (Inherited format and size) that currently have
"input" sections highlighted in yellow. The numbers entered into the yellow
section are currently in thousands, and now they want them converted to whole
numbers. I am working on a macro to find the yellow color and replace the
current cell value with cell value * 1000. I get the part where it finds the
color, but how do I write the macro to use a formula for the replacement? I
want yellow cell value * 1000. How do I write this? Or is there a better way
to do it?

Thanks!
Mary

Replace Cell Value with Formula
 

what you want to do is to take the old formula and multiply by 1000. I would
ptu an new set of parethesis around the old formula lile this

newformula = 1000 * (Old formula).

You also ned to rempve the old equal sign

OldFormula = range("A1").formula
OLdFormula = mid(OldFormula,2) 'remove old equal sign
NewFormula = "=1000*(" & Oldformula & ")"
Range("A1").formula = Newformula

"MaryMalone" wrote:

I havea bout 30 spreadsheets (Inherited format and size) that currently have
"input" sections highlighted in yellow. The numbers entered into the yellow
section are currently in thousands, and now they want them converted to whole
numbers. I am working on a macro to find the yellow color and replace the
current cell value with cell value * 1000. I get the part where it finds the
color, but how do I write the macro to use a formula for the replacement? I
want yellow cell value * 1000. How do I write this? Or is there a better way
to do it?

Thanks!
Mary

Replace Cell Value with Formula
 

This macro:

Sub malone()
For Each r In ActiveSheet.UsedRange
If r.Interior.ColorIndex = 6 Then
r.Value = 1000 * r.Value
End If
Next
End Sub

will, for example, convert yellow cells have a value of 1.234 to 1234
--
Gary''s Student - gsnu200784


"MaryMalone" wrote:

I havea bout 30 spreadsheets (Inherited format and size) that currently have
"input" sections highlighted in yellow. The numbers entered into the yellow
section are currently in thousands, and now they want them converted to whole
numbers. I am working on a macro to find the yellow color and replace the
current cell value with cell value * 1000. I get the part where it finds the
color, but how do I write the macro to use a formula for the replacement? I
want yellow cell value * 1000. How do I write this? Or is there a better way
to do it?

Thanks!
Mary

Replace Cell Value with Formula
 

That was perfect!!! Thanks a ton!

"Gary''s Student" wrote:

This macro:

Sub malone()
For Each r In ActiveSheet.UsedRange
If r.Interior.ColorIndex = 6 Then
r.Value = 1000 * r.Value
End If
Next
End Sub

will, for example, convert yellow cells have a value of 1.234 to 1234
--
Gary''s Student - gsnu200784


"MaryMalone" wrote:

I havea bout 30 spreadsheets (Inherited format and size) that currently have
"input" sections highlighted in yellow. The numbers entered into the yellow
section are currently in thousands, and now they want them converted to whole
numbers. I am working on a macro to find the yellow color and replace the
current cell value with cell value * 1000. I get the part where it finds the
color, but how do I write the macro to use a formula for the replacement? I
want yellow cell value * 1000. How do I write this? Or is there a better way
to do it?

Thanks!
Mary