Forum Discussion
Trying to use SUBSTITUTE to exchange a character, and gets VALUE ERROR
I have a column in my spreadsheet that have values with DOT instead of COMMA-sign. I created a public function like this: Public Function fixakomma(rng As String) fixacomma = SUBSTITUT...
I'm not sure why you are using a user-defined function that only serves as a "wrapper" function for one that is built in?
Why not just use
=SUBSTITUTE(A1,".",",")
and copy down your column?
But, for the sake helping you understand what may be wrong with your function, I think there are two issues. One is that "substitute" is not fully qualified. To access Excel's built-in functions, you would use WorksheetFunction.Substitute (or Application.Substitute). Also, "fixakomma" is not spelled consistently (inside the function, it is spelled "fixacomma").
Public Function fixakomma(rng As String) As String
fixakomma = Application.Substitute(rng, ".", ",")
End Function
Why not just use
=SUBSTITUTE(A1,".",",")
and copy down your column?
But, for the sake helping you understand what may be wrong with your function, I think there are two issues. One is that "substitute" is not fully qualified. To access Excel's built-in functions, you would use WorksheetFunction.Substitute (or Application.Substitute). Also, "fixakomma" is not spelled consistently (inside the function, it is spelled "fixacomma").
Public Function fixakomma(rng As String) As String
fixakomma = Application.Substitute(rng, ".", ",")
End Function