S
Sarah H.
Hi, all,
I work some with data from Europe and have trouble getting numbers into U.S.
format.
I found a promising looking macro on the web, written by David McRitchie:
http://www.mvps.org/dmcritchie/excel/numconv.htm
-----------------------------
Sub USNumbers()
'David McRitchie 2000-05-10 misc convert text numbers
' under wrong control settings to numbes
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim cell As Range
Dim origValue As String
Dim newValue As String
On Error Resume Next
For Each cell In Selection.Cells.SpecialCells(xlConstants, xlTextValues)
origValue = cell.Value
newValue = ""
For i = 1 To Len(origValue)
If Mid(origValue, i, 1) = "." Then
newValue = newValue & ","
ElseIf Mid(origValue, i, 1) = "," Then
newValue = newValue & "."
Else
newValue = newValue & Mid(origValue, i, 1)
End If
Next i
On Error Resume Next
cell.Value = CDbl(Trim(newValue))
On Error GoTo 0
Next cell
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub
-----------------------------
But when I try to run it in Excel 2010, I get an error about the For-Loop
not being set. Not sure how to proceed. Any help would be appreciated.
-S
I work some with data from Europe and have trouble getting numbers into U.S.
format.
I found a promising looking macro on the web, written by David McRitchie:
http://www.mvps.org/dmcritchie/excel/numconv.htm
-----------------------------
Sub USNumbers()
'David McRitchie 2000-05-10 misc convert text numbers
' under wrong control settings to numbes
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim cell As Range
Dim origValue As String
Dim newValue As String
On Error Resume Next
For Each cell In Selection.Cells.SpecialCells(xlConstants, xlTextValues)
origValue = cell.Value
newValue = ""
For i = 1 To Len(origValue)
If Mid(origValue, i, 1) = "." Then
newValue = newValue & ","
ElseIf Mid(origValue, i, 1) = "," Then
newValue = newValue & "."
Else
newValue = newValue & Mid(origValue, i, 1)
End If
Next i
On Error Resume Next
cell.Value = CDbl(Trim(newValue))
On Error GoTo 0
Next cell
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub
-----------------------------
But when I try to run it in Excel 2010, I get an error about the For-Loop
not being set. Not sure how to proceed. Any help would be appreciated.
-S