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" <(E-Mail Removed)> wrote in message
> news:13081D6A-A9FF-4D9F-85C2-(E-Mail Removed)...
> > 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
>
> .
>
Similar Threads
