Topic Icon
General Questions 
Forum for asking General Questions. If you can't find another suitable forum for your enquirey then please post it here.
Blue Circle Vaughan Kaufman Welcomes you to the General Questions Forum :
This is the <i>General Questions</i> forum where you can pose any computer question.<br /><br />However, you should bear in mind that since KVK Consultancy specialises in and recommends the use of <b>Microsoft</b> products, for you to get the most from this forum we recommend focusing on asking questions relating to the Microsoft Windows Platform (<i>since this is how you will receive the best advice</i>)
Vaughan Kaufman said :  
Blue Circle Vaughan Kaufman explains :
A customer brought me an Excel spreadsheet populated with financial data that despite having the correct settings refused to sum or otherwise add up any of the financial information. Investigation revealed that there was "extra" unwanted data in the currency fields preventing the numbers being properly read by Excel. The solution however, was non obvious, so I wrote a VBA macro to tidy up this kind of issue with any spreadsheet. This is so useful I have decided to post it here and share it. If you use this macro, please maintain leave in the original author details but otherwise make it your own.
Vaughan Kaufman said :  
' This Subroutine processed every worksheet in the
' current excel workbook formatting fields as currency
' as appopriate and removing common invalid characters
' which prevent correct currency processing in Excel.

' Author : K.V.Kaufman
' Copyright : KVK Consultancy 2010
Public Sub InvalidCurrencyFieldFix()
     Dim sht As Worksheet
     Dim celAny As Range
     Dim strTemp As String
     For Each sht In ThisWorkbook.Sheets
         Debug.Print "Processing Sheet : " & sht.Name
         For Each celAny In Sheet1.UsedRange.Cells
             bPass = (celAny.Formula = celAny)
             If IsNumeric(celAny.Formula) Then
                 bPass = bPass Or Val(celAny.Formula) = celAny
             End If
             If celAny.Formula = "" Or bPass Then
                 strTemp = celAny
                 If strTemp <> "" Then
                     strTemp = Replace(strTemp, " ", "")
                     If InStr(1, strTemp, "") <> 0 Then
                         celAny.NumberFormat = "Currency"
                     End If
                     strTemp = Replace(strTemp, Chr(160), "")
                     strTemp = Replace(strTemp, "", "")
                     If celAny.Row Mod 10 = 0 And celAny.Column Mod 10 = 0 Then
                         Debug.Print ".";
                     End If
                     celAny = Trim(strTemp)
                 End If
             Else
                 celAny = celAny.Formula
                 Debug.Print "."
             End If
         Next
         Debug.Print ""
         Debug.Print "Completed Sheet : " & sht.Name
     Next
End Sub
Blue Circle Vaughan Kaufman asked :
Vaughan Kaufman said :