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
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