Trying to use SUBSTITUTE to exchange a character, and gets VALUE ERROR

%3CLINGO-SUB%20id%3D%22lingo-sub-1536305%22%20slang%3D%22en-US%22%3ETrying%20to%20use%20SUBSTITUTE%20to%20exchange%20a%20character%2C%20and%20gets%20VALUE%20ERROR%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1536305%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20column%20in%20my%20spreadsheet%20that%20have%20values%20with%20DOT%20instead%20of%20COMMA-sign.%3C%2FP%3E%3CP%3EI%20created%20a%20public%20function%20like%20this%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20Public%20Function%20fixakomma(rng%20As%20String)%3CBR%20%2F%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20fixacomma%20%3D%20SUBSTITUTE(rng%2C%20%22.%22%2C%20%22%2C%22)%3CBR%20%2F%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20End%20Function%3C%2FP%3E%3CP%3ENo%20error%20in%20Visual%20Basic%2C%20but%20when%20I%20type%20in%20an%20empty%20cell%20(%20P6%20)%20beside%20my%20column%20to%20execute%20the%20function%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%3Dfixakomma(M6)%26nbsp%3B%3C%2FP%3E%3CP%3Ewhere%20the%20column%20name%20is%20M%20and%20the%20cell%20row%20in%20the%20column%20is%206%3C%2FP%3E%3CP%3EI%20immediately%20gets%20a%20VALUE%26nbsp%3B%20error%20message%20(in%20swedish%20%23V%C3%85RDEFEL!%20).%3C%2FP%3E%3CP%3EIf%20I%20type%20the%20same%20in%20an%20empty%20cell%20(%20R6%20)%26nbsp%3B%20%26nbsp%3B%3DSUBSTITUTE(M6%3B%22.%22%3B%22%2C%22)%26nbsp%3B%20%26nbsp%3BI%20get%20the%20DOT%20exchanged%20to%20a%20COMMA-sign.%3C%2FP%3E%3CP%3EMy%20reason%20for%20the%20Public%20Function%2C%20was%20to%20execute%20one%20cell%20and%20then%20drag%20this%20down%20in%20the%20column%2C%20to%20get%20the%20rest%20converted%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20is%20my%20problem%20%3F%26nbsp%3B%3C%2FP%3E%3CP%3EDouglas%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1536305%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1536399%22%20slang%3D%22en-US%22%3ERe%3A%20Trying%20to%20use%20SUBSTITUTE%20to%20exchange%20a%20character%2C%20and%20gets%20VALUE%20ERROR%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1536399%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F436593%22%20target%3D%22_blank%22%3E%40DouglasJB%3C%2FA%3E%26nbsp%3BDid%20you%20consider%20%22Find%20%26amp%3B%20Replace%22%20or%20%22Text-to-columns%22%3F%20Is%20there%20a%20particular%20reason%20why%20you%20want%20to%20use%20a%20User%20Defined%20Function%2C%20just%20to%20substitute%20a%20point%20for%20a%20comma%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1536721%22%20slang%3D%22en-US%22%3ERe%3A%20Trying%20to%20use%20SUBSTITUTE%20to%20exchange%20a%20character%2C%20and%20gets%20VALUE%20ERROR%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1536721%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F436593%22%20target%3D%22_blank%22%3E%40DouglasJB%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20got%20the%20column%20in%20a%20table%2C%20and%20I%20wanted%20to%20use%20the%20Sum%20feature%20to%20summarize%20the%20values.%3CBR%20%2F%3EEarlier%20the%20column%20had%20a%20COMMMA-sign%20in%20that%20column%2C%20so%20it%20was%20easy%20to%20summarize%2C%20but%20%3CSPAN%20class%3D%22__postbox-detected-content%20__postbox-detected-date%22%3Efrom%20now%3C%2FSPAN%3E%20on%20they%20will%20have%20a%20DOT%2C%20so%20since%20it%20is%20just%20this%20column%20I%20am%20interested%20in%2C%20I%20thought%20it%20would%20be%20easier%20to%20just%20use%20SUBSTITUTE%2C%20and%20inside%20a%20function%2C%20would%20allow%20me%20to%20select%20just%20this%20column.%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDouglasJB%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1536728%22%20slang%3D%22en-US%22%3ERe%3A%20Trying%20to%20use%20SUBSTITUTE%20to%20exchange%20a%20character%2C%20and%20gets%20VALUE%20ERROR%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1536728%22%20slang%3D%22en-US%22%3EI'm%20not%20sure%20why%20you%20are%20using%20a%20user-defined%20function%20that%20only%20serves%20as%20a%20%22wrapper%22%20function%20for%20one%20that%20is%20built%20in%3F%3CBR%20%2F%3E%3CBR%20%2F%3EWhy%20not%20just%20use%3CBR%20%2F%3E%3DSUBSTITUTE(A1%2C%22.%22%2C%22%2C%22)%3CBR%20%2F%3Eand%20copy%20down%20your%20column%3F%3CBR%20%2F%3E%3CBR%20%2F%3EBut%2C%20for%20the%20sake%20helping%20you%20understand%20what%20may%20be%20wrong%20with%20your%20function%2C%20I%20think%20there%20are%20two%20issues.%20One%20is%20that%20%22substitute%22%20is%20not%20fully%20qualified.%20To%20access%20Excel's%20built-in%20functions%2C%20you%20would%20use%20WorksheetFunction.Substitute%20(or%20Application.Substitute).%20Also%2C%20%22fixakomma%22%20is%20not%20spelled%20consistently%20(inside%20the%20function%2C%20it%20is%20spelled%20%22fixacomma%22).%3CBR%20%2F%3E%3CBR%20%2F%3EPublic%20Function%20fixakomma(rng%20As%20String)%20As%20String%3CBR%20%2F%3Efixakomma%20%3D%20Application.Substitute(rng%2C%20%22.%22%2C%20%22%2C%22)%3CBR%20%2F%3EEnd%20Function%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E
New Contributor

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

@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?

@DouglasJB 

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

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