Force Text To Value VBA Help

[wom2000]

Hi,

I found the following code online that allows a user to force text to values in excel. This code works great except for a couple of issues. When I run the procedure, it coverts all my text to number within the sheet referenced, but it blows away my formulas and screws up my percentages.

Can someone help me modify this code so that it will only apply to what ever area I "highlight" on the sheet with my mouse? Or can you help me to fix the code so it does not blow away my formulas or messes up my percents. Thanks in advance for your brilliance.

Below is the current code that needs modification.

Sub ForceValueCode()

Dim LastPlace, Z As Variant, X As Variant, NewNumb
ActiveSheet.Select
LastPlace = ActiveCell.SpecialCells(xlLastCell).Address
ActiveSheet.Range(Cells(1, 1), LastPlace).Select
Z = Selection.Address
For Each X In ActiveSheet.Range(Z)
If Len(X) > 0 Then
NewNumb = Application.Text(X, "0")
X.FormulaR1C1 = NewNumb
Else
X.FormulaR1C1 = ""
End If
Next
Range(Z).Select
Selection.NumberFormat = "0"
End Sub

 

[Jonmo1]

Try Changing This Line

For Each X In ActiveSheet.Range(Z)

With

For Each X In ActiveSheet.Range(Z).SpecialCells(xlCellTypeConstants)

Hope that helps...

 

[wom2000]

Thank you. This fixes the issue with regards to blowing away my formulas, however I still have the issue with regards to percentages. For example: if I have a value of 0.5 in the cell, when I run the code it coverts my 0.5 to 1 and if I have a value of .3 it converts it to 0.0. How can I modify the code to address this as well? Thank you.

 

[Jonmo1]

This line is setting the number format to 0 decimal places, so the numbers get rounded
Selection.NumberFormat = "0"


try changing it to
Selection.NumberFormat = "0.00"

 

[wom2000]

It worked! Thanks. I had to change the other reference to number format as well, but it worked so thank you very much for your help.