Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,670
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 5,651
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 11,058
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,670
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 226
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 5,934
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,670
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 5,651
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 5,651
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
=SUBSTITUTE(C4,"~?#","") will this work to remove multiple string Raja Mahendiran S Excel Worksheet Functions 6 May 12th 10 09:10 PM
Converting "uppercase" string data to "lower case" in CSV file [email protected] Excel Discussion (Misc queries) 2 August 12th 08 08:36 PM
text string: "91E10" in csv file auto converts to: "9.10E+11" [email protected] Excel Discussion (Misc queries) 2 August 12th 08 03:13 PM
Converting string "061123" into a date "23/11/06" WhytheQ Excel Programming 3 November 24th 06 11:41 AM
Making "examp le" become "examp_le" in a string Sworkhard Excel Programming 3 October 29th 04 09:31 PM


All times are GMT +1. The time now is 09:31 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"