VB .value=.value

[Scott R]

Thru code I'm trying to copy, paste special as values. Here's what I have:

With vRng
.Formula = [formula put in several columns]
.Value = .Value
End With

It's working but is truncating values.
The formula evaluates to 0.013 but gets entered into cell as 0.01.
Precision As Displayed is not on.

Any ideas what's happening or suggestions for an easy fix or replacement for the .Value line?

 

[ExcelChampion]

Perhaps its a formatting issue. Check the format of the cells.

Also, .value=.value is not copying/pasting. Meaning, it does not utilize the clipboard. No copying or pasting is going on. It simply converts the formula to a value.

 

[stanleydgromjr]

Scott R,

Try:

With vRng
  .Formula = [formula put in several columns]
  .Value = .Value
  .NumberFormat = "0.000"
End With

Have a great day,
Stan

 

[Scott R]

No luck so far.

After the .Formula statement, the first cell in vRng evaluates to 0.013 in my Watch expression but the .Value = .Value statement then truncates it to 0.01.
Formatting the cell after the .Value = .Value statement gives 0.010. I want 0.013.

 

[Norie]

Scott

That code doesn't use PasteSpecial.

 

[Scott R]

I'm putting formulas into cells, then converting those formulas to values. I'm not using PasteSpecial and don't want to unless I must. The method I'm using works 99% of the time, but it looks like there are instances where coerced formatting or something like it is interfering with the results.

 

[Colin Legg]

Hi Scott

Try using the range object's Value2 property.

With vRng
          .Formula = [formula put in several columns]
          .Value2 = .Value2
End With

Hope that helps...

 

[mikerickson]

Is Excel>Options>Calculation set to PrecisionAsDisplayed?

 

[Scott R]

Hi Scott

Try using the range object's Value2 property.

With vRng
          .Formula = [formula put in several columns]
          .Value2 = .Value2
End With

Hope that helps...

Thanks, worked great. I'm not familiar with Value2. How is it different and why do you think it mattered in this instance?

 

[Colin Legg]

Hi Scott,

It makes a difference with date and currency data types.

It is the currency data types that were giving you a headache here. If you have a cell formatted as currency you can use the Value2 property to retrieve the full double value.

The reason is explained here:
http://support.microsoft.com/kb/182812

Microsoft Excel stores currency numbers in an integer format that is scaled by 10,000 to produce a fixed-point number with 15 digits to the left of the decimal and 4 digits to the right of the decimal.

Actually the Value2 property is underused: I believe I remember correctly (it would make sense to me) that for numbers working with it is faster than working with the Value property.