CODEWITHSUNDEEP
xls
deand
deand

Reputation: 1

VBA remove formula and leave original value

I have a spreadsheet called "MTM Lookup", this is downloaded from a specific site, in column D of this spreadsheet are values, but these values have a formula attached to them. The formula rounds these values to 0. The formulas don't round on a specific cell. They look like this =ROUND(35370.6708773751,0) or =ROUND(48368.0427161566,0). I need the values to come through with all the decimals or rounded to 10 decimals but cannot get this to happen, I can remove the formula and leave the value but it is rounded to zero. Please could anyone assist with some simple vba to either remove the =round(,0) or replace the 0 to 10 ie round(x,10).

Upvotes: 0

Views: 261

Answers (1)

mara-mfa
mara-mfa

Reputation: 895

I don't see any problem in the formula you provided. When I put 

=ROUND(35370.6708773751,0)

to a cell, I correctly see 35371 in the cell.

There are, however, two things in play here.

  • Formula
  • Cell Format

For example, when I enter the following value to the cell:

=ROUND(35370.6708773751,10)

I do see 35370.67088 as a result (after rounding to 10 places, cell format rounds it again to 5 decimal places)

I don't know why entering the value without any formula shows you 0, but this leads me to the same suspscion, i. e. that the problem is in the cell format.

You can check it by right clicking on the cell > Format Cells (in office 2010 at least) or programatically, using the following, for example:

sheets("MTM Lookup").range("A:1").numberformat = "0.0000000000"

Hope that it helps.

Upvotes: 1

Related Questions