translate procedure into function

[neveu]

hello forum,

i've created a procedure that would set the correct number format for raw data extracted from a system, but which is not recognized by Excel as number due to different pb (extra spaces, different decimal symbol ).

i would like to translate this into a function so i can use it in my code for different columns


here is the code. appreciate any inputs/help. thank you

For Each cell In Columns(Sheets("SETUP").Range("B21"))
    If cell <> "" Then
        If Application.WorksheetFunction.IsNumber(cell) = False Then
        cell.Value = Trim(cell.Text) 'delete spaces in numbers
        cell.Value = Application.WorksheetFunction.Substitute(cell.Text, Chr(32), "") 'delete unnecessary spaces
        cell.Value = Application.WorksheetFunction.Substitute(cell.Text, ",", "x") 'replace coma to x
        cell.Value = Application.WorksheetFunction.Substitute(cell.Text, ".", "x") 'replace dot to x
            If cell.Text Like "*x*" Then
                If Application.International(xlThousandsSeparator) <> " " Then
                    cell.Value = Application.WorksheetFunction.Substitute(Left(cell.Text, Len(cell.Text) - 3), "x", _
                    Application.International(xlThousandsSeparator)) & _
                    Application.WorksheetFunction.Substitute(Right(Rng.Text, 3), "x", _
                    Application.International(xlDecimalSeparator))
                    Else
                    cell.Value = Application.WorksheetFunction.Substitute(Left(cell.Text, Len(cell.Text) - 3), "x", "") & _
                    Application.WorksheetFunction.Substitute(Right(Rng.Text, 3), "x", _
                    Application.International(xlDecimalSeparator))
                End If
            End If
        cell.Value = CDbl(Rng.Value) * (1) 'double type
        cell.NumberFormat = "#,##0.00"
        End If
    End If
Next cell

 

[gaj104]

Something like:

Function RunMe()

Call CleanNo("A")
Call CleanNo("C")
Call CleanNo("E")

End Function


Function CleanNo(strCol As String)

Dim cell As Range, rng As Range

For Each cell In Columns(strCol)
    If cell <> "" Then
        If Application.WorksheetFunction.IsNumber(cell) = False Then
        cell.Value = Trim(cell.Text) 'delete spaces in numbers
        cell.Value = Application.WorksheetFunction.Substitute(cell.Text, Chr(32), "") 'delete unnecessary spaces
        cell.Value = Application.WorksheetFunction.Substitute(cell.Text, ",", "x") 'replace coma to x
        cell.Value = Application.WorksheetFunction.Substitute(cell.Text, ".", "x") 'replace dot to x
            If cell.Text Like "*x*" Then
                If Application.International(xlThousandsSeparator) <> " " Then
                    cell.Value = Application.WorksheetFunction.Substitute(Left(cell.Text, Len(cell.Text) - 3), "x", _
                    Application.International(xlThousandsSeparator)) & _
                    Application.WorksheetFunction.Substitute(Right(rng.Text, 3), "x", _
                    Application.International(xlDecimalSeparator))
                    Else
                    cell.Value = Application.WorksheetFunction.Substitute(Left(cell.Text, Len(cell.Text) - 3), "x", "") & _
                    Application.WorksheetFunction.Substitute(Right(rng.Text, 3), "x", _
                    Application.International(xlDecimalSeparator))
                End If
            End If
        cell.Value = CDbl(rng.Value) * (1) 'double type
        cell.NumberFormat = "#,##0.00"
        End If
    End If
Next cell

End Function

Note, there is a reference to a variable rng, which isnt anywhere in your code.