How to add "'" into string?

E

Eric

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
 
R

Ron Rosenfeld

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
 
G

Gary''s Student

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.
 
E

Eric

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
 
R

Rick Rothstein

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).
 
E

Eric

Wow, I get surprise on your detailed explanation.
Thank everyone very very much for suggestions
Eric
 
K

kamal

hi,
your code seems correct, just replace Value& with Value &


Within macro, I would like to replace "1'" with varable + "', there i
an
 
R

Ron Rosenfeld

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
 
R

Ron Rosenfeld

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top