Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Converting European number formats
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Converting European number formats
Make sure Selection.Cells.SpecialCells(xlConstants, xlTextValues) is
not empty |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Converting European number formats
The code only works on text entries in the selection.
If you have selected only numeric values then Excel will balk. If the amount of data is not extremely large (say under 10,000 cells ) then try the following... 1. Add this line just after Dim newValue as String: "Dim i As Long" 1. Remove (or comment out) the first occurrence of "On Error Resume Next" 2. Change: "For Each cell In Selection.Cells.SpecialCells(xlConstants, xlTextValues)" To: "For Each cell In Selection.Cells" The above should allow you to select all numbers, all text or text and numbers. -- Jim Cone Portland, Oregon USA http://www.mediafire.com/PrimitiveSoftware .. .. .. "Sarah H." wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Converting European number formats
On 26 Ago, 14:02, "Sarah H." wrote:
Hi, all, I work some with data from Europe and have trouble getting numbers into U.S. format. Try this code (converts european string "1.234,56" into 1234.56 number value, "52.725" - 52725): Sub USNumbers2() ' by Scossa Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Dim rng As Range Dim cell As Range Dim origValue() As String Dim newValue As String Dim i As Long Set rng = Selection.Cells.SpecialCells(xlCellTypeConstants, xlTextValues) If Not rng Is Nothing Then For Each cell In rng origValue = Split(Replace(cell.Value, ".", ""), ",") On Error Resume Next 'if no "," in string newValue = origValue(0) newValue = newValue & "." & origValue(1) On Error GoTo 0 cell.Value = CDbl(Trim(newValue)) Next cell End If Set rng = Nothing Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True End Sub Tnks for your feedback. Bye! Scossa |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Converting European number formats
Great information from all! Thanks very much, guys. I will be on holiday
the next four or five days, but I'll try the solutions and report here when I'm back. Sarah H. -------------- "Scossa" wrote in message ... On 26 Ago, 14:02, "Sarah H." wrote: Hi, all, I work some with data from Europe and have trouble getting numbers into U.S. format. Try this code (converts european string "1.234,56" into 1234.56 number value, "52.725" - 52725): Sub USNumbers2() ' by Scossa Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Dim rng As Range Dim cell As Range Dim origValue() As String Dim newValue As String Dim i As Long Set rng = Selection.Cells.SpecialCells(xlCellTypeConstants, xlTextValues) If Not rng Is Nothing Then For Each cell In rng origValue = Split(Replace(cell.Value, ".", ""), ",") On Error Resume Next 'if no "," in string newValue = origValue(0) newValue = newValue & "." & origValue(1) On Error GoTo 0 cell.Value = CDbl(Trim(newValue)) Next cell End If Set rng = Nothing Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True End Sub Tnks for your feedback. Bye! Scossa |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Converting European number formats
Scossa, your version of the Ritchie macro works for me. Thank you. It did
get flummoxed when it encountered some text that was an unconverted European date, though. Here's what that column contents looked like: "26.08.2010". (I know how to get that into recognized dates via text-to-columns. But I'm simply reporting that this text confused the macro.) Anyway, when I select the right columns it works beautifully for my needs. Thanks again. Sarah ---- "Scossa" wrote in message ... On 26 Ago, 14:02, "Sarah H." wrote: Hi, all, I work some with data from Europe and have trouble getting numbers into U.S. format. Try this code (converts european string "1.234,56" into 1234.56 number value, "52.725" - 52725): Sub USNumbers2() ' by Scossa Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Dim rng As Range Dim cell As Range Dim origValue() As String Dim newValue As String Dim i As Long Set rng = Selection.Cells.SpecialCells(xlCellTypeConstants, xlTextValues) If Not rng Is Nothing Then For Each cell In rng origValue = Split(Replace(cell.Value, ".", ""), ",") On Error Resume Next 'if no "," in string newValue = origValue(0) newValue = newValue & "." & origValue(1) On Error GoTo 0 cell.Value = CDbl(Trim(newValue)) Next cell End If Set rng = Nothing Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True End Sub Tnks for your feedback. Bye! Scossa |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Converting European number formats
I wrote:
Scossa, your version of the Ritchie macro works for me. Thank you. It did Sorry: "McRitchie." Sarah |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Converting dates to different formats | Excel Discussion (Misc queries) | |||
Excel 2003, European number format | Excel Discussion (Misc queries) | |||
Read both US and European Formats | Excel Discussion (Misc queries) | |||
European date formats | Excel Worksheet Functions | |||
Converting file formats | Excel Programming |