| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Substitute chr(39)
Hi,
I made function (see below) to substitute mistake input into right input eg. when I type the letter O, I substitute in zero 0 I also like to let the laptop-user (AZERTY-keyboard layout) use the upper row of the keyboard without shift pressed in to get figures so I substitute & by 1, é by 2 etc. Everything works fine, except for 4 because the ' sign (chr(39)) is used to tell excel this is text. I cannot substitute ' in 4 if it is in the beginning of my input (I works right for (' to make 54 but not '( in 45) Can anyone help to substitute ' in 4 when it's in the beginning of my input? Thanks, Jos Vens Here goes my code Function SET_Value(vInput As Range) Dim vSubstitute vSubstitute = vInput vSubstitute = WorksheetFunction.Substitute(vSubstitute, ",", ".") vSubstitute = WorksheetFunction.Substitute(vSubstitute, "O", "0") vSubstitute = WorksheetFunction.Substitute(vSubstitute, "o", "0") vSubstitute = WorksheetFunction.Substitute(vSubstitute, "_", "-") vSubstitute = WorksheetFunction.Substitute(vSubstitute, "&", "1") vSubstitute = WorksheetFunction.Substitute(vSubstitute, "é", "2") vSubstitute = WorksheetFunction.Substitute(vSubstitute, """", "3") vSubstitute = WorksheetFunction.Substitute(vSubstitute, Chr(39), "4") 'vSubstitute = WorksheetFunction.Substitute(vSubstitute, "'", "4") '-does not work either vSubstitute = WorksheetFunction.Substitute(vSubstitute, "(", "5") vSubstitute = WorksheetFunction.Substitute(vSubstitute, "§", "6") vSubstitute = WorksheetFunction.Substitute(vSubstitute, "è", "7") vSubstitute = WorksheetFunction.Substitute(vSubstitute, "!", "8") vSubstitute = WorksheetFunction.Substitute(vSubstitute, "ç", "9") vSubstitute = WorksheetFunction.Substitute(vSubstitute, "à", "0") vInput = vSubstitute End Function |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Substitute chr(39)
Jos,
Have you considered disabling the ' key like the following? Application.OnKey ("'"), "" Thus the user will only be able to enter ' preceded by any other character, but then your... vSubstitute = WorksheetFunction.Substitute(vSubstitute, Chr(39), "4") ....will take care of it. Regards, KL "Jos Vens" wrote in message ... Hi, I made function (see below) to substitute mistake input into right input eg. when I type the letter O, I substitute in zero 0 I also like to let the laptop-user (AZERTY-keyboard layout) use the upper row of the keyboard without shift pressed in to get figures so I substitute & by 1, é by 2 etc. Everything works fine, except for 4 because the ' sign (chr(39)) is used to tell excel this is text. I cannot substitute ' in 4 if it is in the beginning of my input (I works right for (' to make 54 but not '( in 45) Can anyone help to substitute ' in 4 when it's in the beginning of my input? Thanks, Jos Vens Here goes my code Function SET_Value(vInput As Range) Dim vSubstitute vSubstitute = vInput vSubstitute = WorksheetFunction.Substitute(vSubstitute, ",", ".") vSubstitute = WorksheetFunction.Substitute(vSubstitute, "O", "0") vSubstitute = WorksheetFunction.Substitute(vSubstitute, "o", "0") vSubstitute = WorksheetFunction.Substitute(vSubstitute, "_", "-") vSubstitute = WorksheetFunction.Substitute(vSubstitute, "&", "1") vSubstitute = WorksheetFunction.Substitute(vSubstitute, "é", "2") vSubstitute = WorksheetFunction.Substitute(vSubstitute, """", "3") vSubstitute = WorksheetFunction.Substitute(vSubstitute, Chr(39), "4") 'vSubstitute = WorksheetFunction.Substitute(vSubstitute, "'", "4") '-does not work either vSubstitute = WorksheetFunction.Substitute(vSubstitute, "(", "5") vSubstitute = WorksheetFunction.Substitute(vSubstitute, "§", "6") vSubstitute = WorksheetFunction.Substitute(vSubstitute, "è", "7") vSubstitute = WorksheetFunction.Substitute(vSubstitute, "!", "8") vSubstitute = WorksheetFunction.Substitute(vSubstitute, "ç", "9") vSubstitute = WorksheetFunction.Substitute(vSubstitute, "à", "0") vInput = vSubstitute End Function |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Substitute chr(39)
Hi,
even without disabling the "'" key, it works fine (equal as your suggestion), only the substitute does not take place. I could use Application.OnKey ("'"), "4", but here I prohibit the user by entering a ', and that I cannot do. Thanks anyway, more suggestions? Jos "KL" schreef in bericht ... Jos, Have you considered disabling the ' key like the following? Application.OnKey ("'"), "" Thus the user will only be able to enter ' preceded by any other character, but then your... vSubstitute = WorksheetFunction.Substitute(vSubstitute, Chr(39), "4") ...will take care of it. Regards, KL "Jos Vens" wrote in message ... Hi, I made function (see below) to substitute mistake input into right input eg. when I type the letter O, I substitute in zero 0 I also like to let the laptop-user (AZERTY-keyboard layout) use the upper row of the keyboard without shift pressed in to get figures so I substitute & by 1, é by 2 etc. Everything works fine, except for 4 because the ' sign (chr(39)) is used to tell excel this is text. I cannot substitute ' in 4 if it is in the beginning of my input (I works right for (' to make 54 but not '( in 45) Can anyone help to substitute ' in 4 when it's in the beginning of my input? Thanks, Jos Vens Here goes my code Function SET_Value(vInput As Range) Dim vSubstitute vSubstitute = vInput vSubstitute = WorksheetFunction.Substitute(vSubstitute, ",", ".") vSubstitute = WorksheetFunction.Substitute(vSubstitute, "O", "0") vSubstitute = WorksheetFunction.Substitute(vSubstitute, "o", "0") vSubstitute = WorksheetFunction.Substitute(vSubstitute, "_", "-") vSubstitute = WorksheetFunction.Substitute(vSubstitute, "&", "1") vSubstitute = WorksheetFunction.Substitute(vSubstitute, "é", "2") vSubstitute = WorksheetFunction.Substitute(vSubstitute, """", "3") vSubstitute = WorksheetFunction.Substitute(vSubstitute, Chr(39), "4") 'vSubstitute = WorksheetFunction.Substitute(vSubstitute, "'", "4") '-does not work either vSubstitute = WorksheetFunction.Substitute(vSubstitute, "(", "5") vSubstitute = WorksheetFunction.Substitute(vSubstitute, "§", "6") vSubstitute = WorksheetFunction.Substitute(vSubstitute, "è", "7") vSubstitute = WorksheetFunction.Substitute(vSubstitute, "!", "8") vSubstitute = WorksheetFunction.Substitute(vSubstitute, "ç", "9") vSubstitute = WorksheetFunction.Substitute(vSubstitute, "à", "0") vInput = vSubstitute End Function |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Substitute chr(39)
Jos,
Application.OnKey ("'"), "4" wouldn't work anyway. The substitution ActiveCell = WorksheetFunction.Substitute(ActiveCell, Chr(39), "4") DOES work for me. KL "Jos Vens" wrote in message ... Hi, even without disabling the "'" key, it works fine (equal as your suggestion), only the substitute does not take place. I could use Application.OnKey ("'"), "4", but here I prohibit the user by entering a ', and that I cannot do. Thanks anyway, more suggestions? Jos "KL" schreef in bericht ... Jos, Have you considered disabling the ' key like the following? Application.OnKey ("'"), "" Thus the user will only be able to enter ' preceded by any other character, but then your... vSubstitute = WorksheetFunction.Substitute(vSubstitute, Chr(39), "4") ...will take care of it. Regards, KL "Jos Vens" wrote in message ... Hi, I made function (see below) to substitute mistake input into right input eg. when I type the letter O, I substitute in zero 0 I also like to let the laptop-user (AZERTY-keyboard layout) use the upper row of the keyboard without shift pressed in to get figures so I substitute & by 1, é by 2 etc. Everything works fine, except for 4 because the ' sign (chr(39)) is used to tell excel this is text. I cannot substitute ' in 4 if it is in the beginning of my input (I works right for (' to make 54 but not '( in 45) Can anyone help to substitute ' in 4 when it's in the beginning of my input? Thanks, Jos Vens Here goes my code Function SET_Value(vInput As Range) Dim vSubstitute vSubstitute = vInput vSubstitute = WorksheetFunction.Substitute(vSubstitute, ",", ".") vSubstitute = WorksheetFunction.Substitute(vSubstitute, "O", "0") vSubstitute = WorksheetFunction.Substitute(vSubstitute, "o", "0") vSubstitute = WorksheetFunction.Substitute(vSubstitute, "_", "-") vSubstitute = WorksheetFunction.Substitute(vSubstitute, "&", "1") vSubstitute = WorksheetFunction.Substitute(vSubstitute, "é", "2") vSubstitute = WorksheetFunction.Substitute(vSubstitute, """", "3") vSubstitute = WorksheetFunction.Substitute(vSubstitute, Chr(39), "4") 'vSubstitute = WorksheetFunction.Substitute(vSubstitute, "'", "4") '-does not work either vSubstitute = WorksheetFunction.Substitute(vSubstitute, "(", "5") vSubstitute = WorksheetFunction.Substitute(vSubstitute, "§", "6") vSubstitute = WorksheetFunction.Substitute(vSubstitute, "è", "7") vSubstitute = WorksheetFunction.Substitute(vSubstitute, "!", "8") vSubstitute = WorksheetFunction.Substitute(vSubstitute, "ç", "9") vSubstitute = WorksheetFunction.Substitute(vSubstitute, "à", "0") vInput = vSubstitute End Function |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Substitute chr(39)
Jos,
You may also want to consider adding the below line of code at the end of your function. After all substitutions have been made there should only ['] be left and therefore your vSubstitute would be returning Text, not Number. I guess you are quite safe doing this as the last step: If WorksheetFunction.IsText(vSubstitute) Then _ vSubstitute = CDbl(4 & vSubstitute) Regards, KL "KL" wrote in message ... Jos, Application.OnKey ("'"), "4" wouldn't work anyway. The substitution ActiveCell = WorksheetFunction.Substitute(ActiveCell, Chr(39), "4") DOES work for me. KL "Jos Vens" wrote in message ... Hi, even without disabling the "'" key, it works fine (equal as your suggestion), only the substitute does not take place. I could use Application.OnKey ("'"), "4", but here I prohibit the user by entering a ', and that I cannot do. Thanks anyway, more suggestions? Jos "KL" schreef in bericht ... Jos, Have you considered disabling the ' key like the following? Application.OnKey ("'"), "" Thus the user will only be able to enter ' preceded by any other character, but then your... vSubstitute = WorksheetFunction.Substitute(vSubstitute, Chr(39), "4") ...will take care of it. Regards, KL "Jos Vens" wrote in message ... Hi, I made function (see below) to substitute mistake input into right input eg. when I type the letter O, I substitute in zero 0 I also like to let the laptop-user (AZERTY-keyboard layout) use the upper row of the keyboard without shift pressed in to get figures so I substitute & by 1, é by 2 etc. Everything works fine, except for 4 because the ' sign (chr(39)) is used to tell excel this is text. I cannot substitute ' in 4 if it is in the beginning of my input (I works right for (' to make 54 but not '( in 45) Can anyone help to substitute ' in 4 when it's in the beginning of my input? Thanks, Jos Vens Here goes my code Function SET_Value(vInput As Range) Dim vSubstitute vSubstitute = vInput vSubstitute = WorksheetFunction.Substitute(vSubstitute, ",", ".") vSubstitute = WorksheetFunction.Substitute(vSubstitute, "O", "0") vSubstitute = WorksheetFunction.Substitute(vSubstitute, "o", "0") vSubstitute = WorksheetFunction.Substitute(vSubstitute, "_", "-") vSubstitute = WorksheetFunction.Substitute(vSubstitute, "&", "1") vSubstitute = WorksheetFunction.Substitute(vSubstitute, "é", "2") vSubstitute = WorksheetFunction.Substitute(vSubstitute, """", "3") vSubstitute = WorksheetFunction.Substitute(vSubstitute, Chr(39), "4") 'vSubstitute = WorksheetFunction.Substitute(vSubstitute, "'", "4") '-does not work either vSubstitute = WorksheetFunction.Substitute(vSubstitute, "(", "5") vSubstitute = WorksheetFunction.Substitute(vSubstitute, "§", "6") vSubstitute = WorksheetFunction.Substitute(vSubstitute, "è", "7") vSubstitute = WorksheetFunction.Substitute(vSubstitute, "!", "8") vSubstitute = WorksheetFunction.Substitute(vSubstitute, "ç", "9") vSubstitute = WorksheetFunction.Substitute(vSubstitute, "à", "0") vInput = vSubstitute End Function |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Substitute chr(39)
Hi,
this works fine if the first character is a ' (= 4), but the substitute-function does not change the type, so if you type (' (=54), you get 454, because the IsText function remains TRUE, even if the letters are replaced with figures! Thanks anyway, more suggestions? Jos "KL" schreef in bericht ... Jos, You may also want to consider adding the below line of code at the end of your function. After all substitutions have been made there should only ['] be left and therefore your vSubstitute would be returning Text, not Number. I guess you are quite safe doing this as the last step: If WorksheetFunction.IsText(vSubstitute) Then _ vSubstitute = CDbl(4 & vSubstitute) Regards, KL "KL" wrote in message ... Jos, Application.OnKey ("'"), "4" wouldn't work anyway. The substitution ActiveCell = WorksheetFunction.Substitute(ActiveCell, Chr(39), "4") DOES work for me. KL "Jos Vens" wrote in message ... Hi, even without disabling the "'" key, it works fine (equal as your suggestion), only the substitute does not take place. I could use Application.OnKey ("'"), "4", but here I prohibit the user by entering a ', and that I cannot do. Thanks anyway, more suggestions? Jos "KL" schreef in bericht ... Jos, Have you considered disabling the ' key like the following? Application.OnKey ("'"), "" Thus the user will only be able to enter ' preceded by any other character, but then your... vSubstitute = WorksheetFunction.Substitute(vSubstitute, Chr(39), "4") ...will take care of it. Regards, KL "Jos Vens" wrote in message ... Hi, I made function (see below) to substitute mistake input into right input eg. when I type the letter O, I substitute in zero 0 I also like to let the laptop-user (AZERTY-keyboard layout) use the upper row of the keyboard without shift pressed in to get figures so I substitute & by 1, é by 2 etc. Everything works fine, except for 4 because the ' sign (chr(39)) is used to tell excel this is text. I cannot substitute ' in 4 if it is in the beginning of my input (I works right for (' to make 54 but not '( in 45) Can anyone help to substitute ' in 4 when it's in the beginning of my input? Thanks, Jos Vens Here goes my code Function SET_Value(vInput As Range) Dim vSubstitute vSubstitute = vInput vSubstitute = WorksheetFunction.Substitute(vSubstitute, ",", ".") vSubstitute = WorksheetFunction.Substitute(vSubstitute, "O", "0") vSubstitute = WorksheetFunction.Substitute(vSubstitute, "o", "0") vSubstitute = WorksheetFunction.Substitute(vSubstitute, "_", "-") vSubstitute = WorksheetFunction.Substitute(vSubstitute, "&", "1") vSubstitute = WorksheetFunction.Substitute(vSubstitute, "é", "2") vSubstitute = WorksheetFunction.Substitute(vSubstitute, """", "3") vSubstitute = WorksheetFunction.Substitute(vSubstitute, Chr(39), "4") 'vSubstitute = WorksheetFunction.Substitute(vSubstitute, "'", "4") '-does not work either vSubstitute = WorksheetFunction.Substitute(vSubstitute, "(", "5") vSubstitute = WorksheetFunction.Substitute(vSubstitute, "§", "6") vSubstitute = WorksheetFunction.Substitute(vSubstitute, "è", "7") vSubstitute = WorksheetFunction.Substitute(vSubstitute, "!", "8") vSubstitute = WorksheetFunction.Substitute(vSubstitute, "ç", "9") vSubstitute = WorksheetFunction.Substitute(vSubstitute, "à", "0") vInput = vSubstitute End Function |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Substitute | Excel Worksheet Functions | |||
| =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE.... max. limit 8 :( | Excel Discussion (Misc queries) | |||
| More than 7 IF? any substitute? | Excel Worksheet Functions | |||
| SUBSTITUTE | Excel Worksheet Functions | |||
| Using &Chr$(39)& as substitute for ' in VBA | Excel Discussion (Misc queries) | |||
![Jos Vens[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
