Field value modifications

  • Thread starter Thread starter Benoit
  • Start date Start date
B

Benoit

Hello,

I have a file that calculates charges (see example
bellow). The file will take the info under "Bal. Bef."
and will substract "Bal. Aft." to give us the info
under "Charge".

When I go in one of the fields under "Charge" I get 9as an
example): =(F3)-(G3)

What I would like to do would be (by using a macro or
something like that) to change the value of the field by
the amount given by the eqation.

So for example, I would like to know if there is a way to
make Excel know that field C4 (as example) has a value of
$0.30 (result of the =(A4)-(B4) equation) and change it
into 0.30 so that if I remove the colums "Bal. Bef."
and "Bal. Aft.", I still know that the value is the
correct one (witch would be $0.30 in this example).

Description Bal. Bef. Bal. Aft. Charge
Inbound Call $14.80 $14.50 $0.30
Outbound call $15.10 $14.80 $0.30
Outbound call $15.40 $15.10 $0.30
Outbound call $15.70 $15.40 $0.30
Inbound Call $16.00 $15.70 $0.30
Inbound Call $16.30 $16.00 $0.30
Inbound Call $16.60 $16.30 $0.30

Thanks in advance!!!

Benoit
 
To freeze the values (which is what you're after),
why not just copy the entire col and then paste special as values in place?

Example:

Select col C (assume this is the col for the equation results)
Click Edit>Copy
Click Edit>Paste special>Values>OK
 
Assuming the cols to be converted to values on the sheet
are cols C, E, and G to IV (total: 200+ cols),
try this macro on a back-up copy:

Sub ConvertColToValue()
Columns("C:C").Formula = Columns("C:C").Value
Columns("E:E").Formula = Columns("E:E").Value
Columns("G:IV").Formula = Columns("G:IV").Value
End Sub

Adapt the cols to suit.
 
Back
Top