range.address, replace characters

[Rick S.]

I am having dificulties cyphering this.
'=========
Sub Test()
Dim rCells As Range, rLoopCells As Range
Dim OldAddress As Variant
Dim NewAdress As Variant

'Set variable to needed cells
If Selection.Cells.Count < 1 Then
MsgBox "You must select at least one cell"
Else
Set rCells = Selection
End If
For Each rLoopCells In rCells
OldAddress = rLoopCells.Address
NewAdress = Replace(OldAddress, "A", "D")
MsgBox NewAddress 'for testing
'ActiveSheet.Range(NewAdress).Value = ActiveSheet.Range(OldAddress).Value
Next rLoopCells
End Sub
'=======

All I want to do is substitute the letter D for the letter A in the address
of "OldAddress".
I am using msgbox to show results before I destroy my data but all I get is
a blank msgbox.
What do I have wrong in the above code? (I know the problem lies within the
"Replace" statement).

--
Regards

VBA.Noob.Confused
XP Pro
Office 2007

 

[Jim Cone]

Change...
NewAdress = Replace(OldAddress, "A", "D")
To...
NewAdress = Application.Substitute(OldAddress, "A", "D")
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"Rick S."
wrote in message
I am having dificulties cyphering this.
'=========
Sub Test()
Dim rCells As Range, rLoopCells As Range
Dim OldAddress As Variant
Dim NewAdress As Variant
'Set variable to needed cells
If Selection.Cells.Count < 1 Then
MsgBox "You must select at least one cell"
Else
Set rCells = Selection
End If
For Each rLoopCells In rCells
OldAddress = rLoopCells.Address
NewAdress = Replace(OldAddress, "A", "D")
MsgBox NewAddress 'for testing
'ActiveSheet.Range(NewAdress).Value = ActiveSheet.Range(OldAddress).Value
Next rLoopCells
End Sub
'=======
All I want to do is substitute the letter D for the letter A in the address
of "OldAddress".
I am using msgbox to show results before I destroy my data but all I get is
a blank msgbox.
What do I have wrong in the above code? (I know the problem lies within the
"Replace" statement).

 

[sebastienm]

Hi,
You declared
Dim NewAdress with 1 'd'
but you use
NewAddress with 2 'd' s in the code.

This is why it is veeeery strongly recommended to use
Option Explicit
at the top of each module. This way you would get the message
'variable is not declared'
before being able to run the code. This would save you hours of debugging in
the long term.
You can change your settings to always have the 'Option Explicit' added
automatically every time you add a code module (module, class, userform...):
- in the vba editor goto to the editor, menu Tools > Options. The Options
dialog pops up.
- in the editor tab,
- check the Require Variable Declaration
- uncheck the Auto Syntax Check (prevent an annoying pop to show each
time but still highlight syntax issues in red)

 

[JLGWhiz]

You are missing some arguments for the Replace Function:

expression.Replace(Arg1, Arg2, Arg3, Arg4)
expression Required. An expression that returns a WorksheetFunction object.

Arg1 Required String. Text in which you want to replace some characters.

Arg2 Required Double. The position of the character in Arg1 that you want
to replace with Arg4.

Arg3 Required Double. The number of characters in Arg1 that you want the
Replace method to replace with Arg4.

Arg4 Required String. Text that will replace characters in Arg1.

 

[Gary''s Student]

MsgBox NewAdress 'for testing
not
MsgBox NewAddress 'for testing

 

[Rick S.]

Jim, JLG, Gary and sebastien!
[StAdIuM wAvE]
Thank you so much, each one of you helped!

Happy Holidays!

--
Regards

VBA.Noob.Confused
XP Pro
Office 2007

 

[Rick Rothstein \(MVP - VB\)]

Rick S is using the VBA Replace function which is a lot different than the
the REPLACE function from the spreadsheet side of things. VBA's Replace
function has 6 arguments, the last 3 of which are Optional.

Arg1: expression - Required. String expression containing substring to
replace.

Arg2: find - Required. Substring being searched for.

Arg3: replace - Required. Replacement substring.

Arg4: start - Optional. Position within expression where substring
search is to begin. If omitted, 1 is assumed.

Arg5: count - Optional. Number of substring substitutions to perform.
If omitted, the default value is –1, which means make all possible
substitutions.

Arg6: compare - Optional. Numeric value indicating the kind of
comparison to use when evaluating substrings. See Settings section for
values.

Rick

 

[Rick Rothstein \(MVP - VB\)]

Rick S is using the VBA Replace function which is a lot different than the
the REPLACE function from the spreadsheet side of things. VBA's Replace
function has 6 arguments, the last 3 of which are Optional.

Arg1: expression - Required. String expression containing substring to
replace.

Arg2: find - Required. Substring being searched for.

Arg3: replace - Required. Replacement substring.

Arg4: start - Optional. Position within expression where substring
search is to begin. If omitted, 1 is assumed.

Arg5: count - Optional. Number of substring substitutions to perform.
If omitted, the default value is –1, which means make all possible
substitutions.

Arg6: compare - Optional. Numeric value indicating the kind of
comparison to use when evaluating substrings. See Settings section for
values.

Rick

 

[Rick S.]

I had "assumed" some arguments were optional.
--
Regards

VBA.Noob.Confused
XP Pro
Office 2007