VBA Replace does not work

[PedroVision]

tempRowCounter = Replace(tempRowCounter, " ", "")

Trying to remove all spaces from a text string...

I've tried 67 different ways...and all I keep getting from VBA is...see image...


I just think this is, or should be difficult...but I'm not having any luck getting past this...any help would be humbly appreciated!

Many thanks...

 

[dreid1011]

Have you used the name "Replace" as a variable or Sub name elsewhere in your code?

 

[Skyybot]

Try it with parenthesis:

MsgBox (Replace("Lots    of    space   here."," ",""))

 

[Alex Blakenburg]

As suggested by @dreid1011, I get exactly that error message "Wrong number of arguments..." if I do this:
(PS: Using it as a variable gave a different error message)

Sub ReplaceExample()
    MsgBox Replace(" I love excel ", " ", "")
    'Result is: "Iloveexcel"
End Sub

Sub Replace()
    Debug.Print "test sub"
End Sub

 

[jolivanes]

@ Scott Huish
Re Post #6.
That's what I had but when I tried it before posting, it did not do anything while the code I posted did work. Strange.

 

[jolivanes]

I renamed the macros as follows.

Sub Number_One()
    ThisWorkbook.Sheets("Sheet1").Range("A1:A100").Replace " ", "", 1
End Sub

Sub Number_Two()
Dim c As Range
    For Each c In Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row)
        c.Value = Replace(c.Value, " ", "", 1)
    Next c
End Sub

Sub Number_Three()
    Range("A:A").Replace " ", ""
End Sub

Sub Number_Four()
    Sheets("Sheet1").Columns(1).Replace What:=" ", Replacement:="", LookAt:=xlPart
End Sub

(I have not played around with Paul's code from Post #4 yet. Will try that later.)
Then I ran them in order as below with result shown.

Order of macros:
Number_One. Nothing happens.
Number_Two. Works (Followed By data replacing to original)
Number_Three. Nothing Happens
Number_Four. Works (Followed By data replacing to original)
Number_Three. Works (Followed By data replacing to original)
Number_Two. Works (Followed By data replacing to original)
Number_One. Nothing happens.

Order of macros:
Number_One. Nothing happens.
Number_Four. Works (Followed By data replacing to original)
Number_Three. Works (Followed By data replacing to original)
Number_Two. Works (Followed By data replacing to original)

Order of macros:
Number_One. Nothing happens.
Number_Two. Works (Followed By data replacing to original)
Number_Three. Nothing Happens
Number_Two. Works (Followed By data replacing to original)
Number_Three. Nothing Happens

Number_One never works.
Number_Two always works.
Number_Three only works after Number_Four was used.
Number_Four always works.

If I knew why it is like this I would not have posted all of this. I would have just fixed it.

 

[Alex Blakenburg]

@jolivanes
You are mixing 2 different methods.
1) Range.Replace
If you don't explicitly state the parameters the first 3 are:
> What, Replacement and LookAt (xlPart or xlWhole)
If you leave out the 3rd then your results will be hit and miss because it uses the Find dialogue box which remembers many of the setting from the last time you used it. So if the last time you used it you checked Match Entire Cell content (aka xlWhole) either manually or with code, it will use that.

In your Number_One you have the 3rd parameter as 1 which is xlWhole. The subsequent Number_Three with no 3rd parameter will fail because xlWhole carries over.

Number_Four resets the LookAt to xlPart so if you run Number_Three after that it will work.

2) Replace()
Is a totally different beast and does not use the Find & Replace box and the 1 in the last position is actually position 4 which is "Start position" (in string)
As such it is totally unaffected by the other other 3 subs.
The excel equivalent being Substitute(). So you could add:

Sub Number_Five()
    With Sheets("Sheet1").Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row)
        .Value = Application.Substitute(.Value, " ", "")
    End With
End Sub

@PedroVision - the error message is still indicative of there being a sub called "Replace" somewhere in your project.

 

[jolivanes]

Alex
Thank you very much for taking the time to explain.
It is very much appreciated.

 

[RoryA]

Here is another example...again, it does not work...

Try using VBA.Replace rather than just Replace. If that fixes it, then you have used Replace as the name of a routine of your own making.

 

[PedroVision]

OK...it appears I have fallen in with another set of thieves...(in a good way!!). Glad I found you guys!!

A few weeks ago, while debugging a wholly different procedure, I had named a command button "Replace"...once that and its associated Sub were renamed...voilà!! Magically, the command REPLACE began working as desired.

WELL DONE PEOPLE...MrExcel...I have arrived.

Greetings from the land of Finns!! (Thank you again!)

 

[PedroVision]

Try using VBA.Replace rather than just Replace. If that fixes it, then you have used Replace as the name of a routine of your own making.

EXCELLENT.