Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to add "'" into string?
Within macro, I would like to replace "1'" with varable + "', there is an
error. Please see the following code for details Cells.Replace What:="1'", Replacement:=Range("A2").Value&"'", LookAt:=xlPart, SearchOrder _ :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False Does anyone have any suggestions on how to solve this coding? Thanks in advance for any suggestions Eric |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to add "'" into string?
On Sat, 10 Apr 2010 04:57:01 -0700, Eric
wrote: Within macro, I would like to replace "1'" with varable + "', there is an error. Please see the following code for details Cells.Replace What:="1'", Replacement:=Range("A2").Value&"'", LookAt:=xlPart, SearchOrder _ :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False Does anyone have any suggestions on how to solve this coding? Thanks in advance for any suggestions Eric It's always helpful if you post the error text, instead of making us guess. If you are getting a compile error, it might be due to having omitted the spaces before and after the ampersand in your Replacement:= string. --ron |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to add "'" into string?
If you have data in A2 thru A4 like:
qwerty111 shc1yf 1poiuyt then running: Sub eric001() Dim r As Range Set r = Range("A2:A100") For Each rr In r rr.Value = Replace(rr.Value, "1", "'") Next End Sub will produce: qwerty''' shc'yf poiuyt Note the leading single quote is surpressed. -- Gary''s Student - gsnu201001 "Eric" wrote: Within macro, I would like to replace "1'" with varable + "', there is an error. Please see the following code for details Cells.Replace What:="1'", Replacement:=Range("A2").Value&"'", LookAt:=xlPart, SearchOrder _ :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False Does anyone have any suggestions on how to solve this coding? Thanks in advance for any suggestions Eric |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to add "'" into string?
Could you please tell me how to add variable into Replace function? in order
to make following statement working. r.Value = Replace(r.Value, "1!", str&"'") 'Error here Do you have any suggestions? Thanks in advance for any suggestions Eric Dim str As String Dim Rng As Range str = Range("A2").Value Set Rng = Range("A1:A1000") For Each r In Rng r.Value = Replace(r.Value, "1!", str&"'") 'Error here Next End Sub End Sub "Gary''s Student" wrote: If you have data in A2 thru A4 like: qwerty111 shc1yf 1poiuyt then running: Sub eric001() Dim r As Range Set r = Range("A2:A100") For Each rr In r rr.Value = Replace(rr.Value, "1", "'") Next End Sub will produce: qwerty''' shc'yf poiuyt Note the leading single quote is surpressed. -- Gary''s Student - gsnu201001 "Eric" wrote: Within macro, I would like to replace "1'" with varable + "', there is an error. Please see the following code for details Cells.Replace What:="1'", Replacement:=Range("A2").Value&"'", LookAt:=xlPart, SearchOrder _ :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False Does anyone have any suggestions on how to solve this coding? Thanks in advance for any suggestions Eric |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to add "'" into string?
this compiled for me.
r.Value = Replace(r.Value, "1!", str & "'") -- Gary Keramidas Excel 2003 "Eric" wrote in message ... Could you please tell me how to add variable into Replace function? in order to make following statement working. r.Value = Replace(r.Value, "1!", str&"'") 'Error here Do you have any suggestions? Thanks in advance for any suggestions Eric Dim str As String Dim Rng As Range str = Range("A2").Value Set Rng = Range("A1:A1000") For Each r In Rng r.Value = Replace(r.Value, "1!", str&"'") 'Error here Next End Sub End Sub "Gary''s Student" wrote: If you have data in A2 thru A4 like: qwerty111 shc1yf 1poiuyt then running: Sub eric001() Dim r As Range Set r = Range("A2:A100") For Each rr In r rr.Value = Replace(rr.Value, "1", "'") Next End Sub will produce: qwerty''' shc'yf poiuyt Note the leading single quote is surpressed. -- Gary''s Student - gsnu201001 "Eric" wrote: Within macro, I would like to replace "1'" with varable + "', there is an error. Please see the following code for details Cells.Replace What:="1'", Replacement:=Range("A2").Value&"'", LookAt:=xlPart, SearchOrder _ :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False Does anyone have any suggestions on how to solve this coding? Thanks in advance for any suggestions Eric |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to add "'" into string?
Ron gave you the answer.... you need to add spaces around the ampersand when
it is being used for concatenation (you cannot rely on VB to automatically format the spaces in for you). Why? It has to do with VB's backward compatibility with the BASIC languages it was derived from. In the "old days" (although there are some that still do this), you were able to declare your variables with a postfix symbol to indicate the data type. The ampersand symbol was used to declare a variable as Long. So, you did this to establish a Long variable... Dim MyVariable& The worse thing about using the postfix symbol is that you were able to force the declaration at the time of first use without providing a Dim statement beforehand. So, if you did this... X = 10 * MyVariable& in code without first Dim'ming the variable as a Long, VB would automatically Dim it as a Long for you the first time it came across the & attached to the variable name. Even worse, in the old, old days of BASIC (those prior to VB), you could actually have the same name (names were limited to 2 characters back then) with different postfix symbols. So you could have (again, back in the old, old days) AB%, AB!, AB$ (and I don't remember the other available symbols any more) in one program and they would all be different. To account for this behavior, BASIC had to allow the postfix symbol to be used whenever the variable was used. The early VB's, trying to maintain backward code compatibility (as much as the switch from procedural BASIC to event driven VB would allow that is), continued to allow the postfix symbol to be used whenever the variable was used (although, as I vaguely remember, the ability to have the same name used with different data types was eliminated when the "As <VarType" declaration were created). Anyway, the reason you need the space after the variable name, and before the ampersand, is because VB isn't able to decide if you are applying a Long postfix symbol to the variable name or simply trying to concatenate it. The above explains (I hope) why you **always** need the space in front of the ampersand when it is used as a concatenation symbol. There are times (not in your example, when you also need to put a space after the ampersand that is used for concatenating text after it. If the variable name after the ampersand starts with an "h" or an "H", you must include the separating space between them. In VB, &H and &h indicate to VB to consider what follows are hexadecimal digits (for example, &HFACE is the decimal number -1330). If you set up this statement... CombinedText = TextVariabe &HFACE then VB would assume &HFACE was the value -1330 and would produce a syntax error because you have two values without an operator of some sort between them... VB would not see the ampersand as a concatenation symbol. This is a problem **only** when what follows the "h" or "H" in the variable name can make a valid hexadecimal number... a variable named HLine would not be a problem because &HLine is not a valid hexadecimal number, so for that variable name, VB would automatically insert the space between the ampersand and the "H". To be safe, though, you **always** need to include the space before the ampersand if a variable name precedes it and it is usually a good idea to include the space after it if a variable name follows it (just so you don't have to remember the leading "h/H" character exception). -- Rick (MVP - Excel) "Eric" wrote in message ... Could you please tell me how to add variable into Replace function? in order to make following statement working. r.Value = Replace(r.Value, "1!", str&"'") 'Error here Do you have any suggestions? Thanks in advance for any suggestions Eric Dim str As String Dim Rng As Range str = Range("A2").Value Set Rng = Range("A1:A1000") For Each r In Rng r.Value = Replace(r.Value, "1!", str&"'") 'Error here Next End Sub End Sub "Gary''s Student" wrote: If you have data in A2 thru A4 like: qwerty111 shc1yf 1poiuyt then running: Sub eric001() Dim r As Range Set r = Range("A2:A100") For Each rr In r rr.Value = Replace(rr.Value, "1", "'") Next End Sub will produce: qwerty''' shc'yf poiuyt Note the leading single quote is surpressed. -- Gary''s Student - gsnu201001 "Eric" wrote: Within macro, I would like to replace "1'" with varable + "', there is an error. Please see the following code for details Cells.Replace What:="1'", Replacement:=Range("A2").Value&"'", LookAt:=xlPart, SearchOrder _ :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False Does anyone have any suggestions on how to solve this coding? Thanks in advance for any suggestions Eric |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to add "'" into string?
Wow, I get surprise on your detailed explanation.
Thank everyone very very much for suggestions Eric "Rick Rothstein" wrote: Ron gave you the answer.... you need to add spaces around the ampersand when it is being used for concatenation (you cannot rely on VB to automatically format the spaces in for you). Why? It has to do with VB's backward compatibility with the BASIC languages it was derived from. In the "old days" (although there are some that still do this), you were able to declare your variables with a postfix symbol to indicate the data type. The ampersand symbol was used to declare a variable as Long. So, you did this to establish a Long variable... Dim MyVariable& The worse thing about using the postfix symbol is that you were able to force the declaration at the time of first use without providing a Dim statement beforehand. So, if you did this... X = 10 * MyVariable& in code without first Dim'ming the variable as a Long, VB would automatically Dim it as a Long for you the first time it came across the & attached to the variable name. Even worse, in the old, old days of BASIC (those prior to VB), you could actually have the same name (names were limited to 2 characters back then) with different postfix symbols. So you could have (again, back in the old, old days) AB%, AB!, AB$ (and I don't remember the other available symbols any more) in one program and they would all be different. To account for this behavior, BASIC had to allow the postfix symbol to be used whenever the variable was used. The early VB's, trying to maintain backward code compatibility (as much as the switch from procedural BASIC to event driven VB would allow that is), continued to allow the postfix symbol to be used whenever the variable was used (although, as I vaguely remember, the ability to have the same name used with different data types was eliminated when the "As <VarType" declaration were created). Anyway, the reason you need the space after the variable name, and before the ampersand, is because VB isn't able to decide if you are applying a Long postfix symbol to the variable name or simply trying to concatenate it. The above explains (I hope) why you **always** need the space in front of the ampersand when it is used as a concatenation symbol. There are times (not in your example, when you also need to put a space after the ampersand that is used for concatenating text after it. If the variable name after the ampersand starts with an "h" or an "H", you must include the separating space between them. In VB, &H and &h indicate to VB to consider what follows are hexadecimal digits (for example, &HFACE is the decimal number -1330). If you set up this statement... CombinedText = TextVariabe &HFACE then VB would assume &HFACE was the value -1330 and would produce a syntax error because you have two values without an operator of some sort between them... VB would not see the ampersand as a concatenation symbol. This is a problem **only** when what follows the "h" or "H" in the variable name can make a valid hexadecimal number... a variable named HLine would not be a problem because &HLine is not a valid hexadecimal number, so for that variable name, VB would automatically insert the space between the ampersand and the "H". To be safe, though, you **always** need to include the space before the ampersand if a variable name precedes it and it is usually a good idea to include the space after it if a variable name follows it (just so you don't have to remember the leading "h/H" character exception). -- Rick (MVP - Excel) "Eric" wrote in message ... Could you please tell me how to add variable into Replace function? in order to make following statement working. r.Value = Replace(r.Value, "1!", str&"'") 'Error here Do you have any suggestions? Thanks in advance for any suggestions Eric Dim str As String Dim Rng As Range str = Range("A2").Value Set Rng = Range("A1:A1000") For Each r In Rng r.Value = Replace(r.Value, "1!", str&"'") 'Error here Next End Sub End Sub "Gary''s Student" wrote: If you have data in A2 thru A4 like: qwerty111 shc1yf 1poiuyt then running: Sub eric001() Dim r As Range Set r = Range("A2:A100") For Each rr In r rr.Value = Replace(rr.Value, "1", "'") Next End Sub will produce: qwerty''' shc'yf poiuyt Note the leading single quote is surpressed. -- Gary''s Student - gsnu201001 "Eric" wrote: Within macro, I would like to replace "1'" with varable + "', there is an error. Please see the following code for details Cells.Replace What:="1'", Replacement:=Range("A2").Value&"'", LookAt:=xlPart, SearchOrder _ :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False Does anyone have any suggestions on how to solve this coding? Thanks in advance for any suggestions Eric . |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to add "'" into string?
hi, your code seems correct, just replace Value& with Value & Eric;695756 Wrote: Within macro, I would like to replace "1'" with varable + "', there is an error. Please see the following code for details Cells.Replace What:="1'", Replacement:=Range("A2").Value&"'", LookAt:=xlPart, SearchOrder _ :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False Does anyone have any suggestions on how to solve this coding? Thanks in advance for any suggestions Eric -- kamal ------------------------------------------------------------------------ kamal's Profile: 1739 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=194576 http://www.thecodecage.com/forumz |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to add "'" into string?
On Sat, 10 Apr 2010 07:00:01 -0700, Eric
wrote: Could you please tell me how to add variable into Replace function? in order to make following statement working. r.Value = Replace(r.Value, "1!", str&"'") 'Error here Do you have any suggestions? What happened when you tried my suggestion of spaces before and after the ampersand? --ron |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to add "'" into string?
On Sat, 10 Apr 2010 10:48:24 -0400, "Rick Rothstein"
wrote: Why? It has to do with VB's backward compatibility with the BASIC languages it was derived from. Rick, I saw your detailed explanation after my second post. Thanks for that. I always wondered about that aspect of VBA's behavior. --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
=SUBSTITUTE(C4,"~?#","") will this work to remove multiple string | Excel Worksheet Functions | |||
Converting "uppercase" string data to "lower case" in CSV file | Excel Discussion (Misc queries) | |||
text string: "91E10" in csv file auto converts to: "9.10E+11" | Excel Discussion (Misc queries) | |||
Converting string "061123" into a date "23/11/06" | Excel Programming | |||
Making "examp le" become "examp_le" in a string | Excel Programming |