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 = SUBSTITUTE(rng, ".", ",")
End Function
No error in Visual Basic, but when I type in an empty cell ( P6 ) beside my column to execute the function
=fixakomma(M6)
where the column name is M and the cell row in the column is 6
I immediately gets a VALUE error message (in swedish #VÅRDEFEL! ).
If I type the same in an empty cell ( R6 ) =SUBSTITUTE(M6;".";",") I get the DOT exchanged to a COMMA-sign.
My reason for the Public Function, was to execute one cell and then drag this down in the column, to get the rest converted
What is my problem ?
Douglas
3 Replies
- 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 I got the column in a table, and I wanted to use the Sum feature to summarize the values.
Earlier the column had a COMMMA-sign in that column, so it was easy to summarize, but from now on they will have a DOT, so since it is just this column I am interested in, I thought it would be easier to just use SUBSTITUTE, and inside a function, would allow me to select just this column.,DouglasJB
DouglasJB Did you consider "Find & Replace" or "Text-to-columns"? Is there a particular reason why you want to use a User Defined Function, just to substitute a point for a comma?
