Looping Until Text Not Found

I have some code that I want to continuously loop while double spaces are found, and replace them with a single space.

Is there a way to do this? I can't seem to find any information on what the Find method in VBA returns (or what the replace method returns)

Here's my code (note, this is actually in Visual Studio 2008 in an excel addin):

Code:

---

        bDoubles = Globals.ThisAddIn.Application.Selection.find(What:=Space(2))
        If Not bDoubles Is Nothing Then
            Do Until bDoubles Is Nothing
                System.Windows.Forms.Application.DoEvents()
                bDoubles.Activate()
                Globals.ThisAddIn.Application.Selection.replace(What:=Space(2), Replacement:=Space(1))
                bDoubles = Globals.ThisAddIn.Application.Selection.find(What:=Space(2))
            Loop
        End If

---

Quote:

---

Originally Posted by fEtchboi88

I can't seem to find any information on what the Find method in VBA returns (or what the replace method returns)

---

Excel VBA Find
Excel VBA Replace

That's not quite what I'm looking for (I don't think).

That Find function returns the position within a string that the text is found - is there a way to search the entire spreadsheet, and return the Cell that the text is found in?

Quote:

---

and return the Cell that the text is found in

---

i believe, should be bdoubles.address

I'll give that a shot, thank you.

I have a temporary work-around in place at the moment, but it's pointing to all of the cells that contain dates in the format:
mm/dd/yyyy hh:mm:ss

...since there are two spaces between the year and the hour. The only problem is, when it replaces them with one space they don't actually replace resulting in an infinite loop.

I tried throwing in a "if blahblah.NumberFormat = "m/d/yyyy"" (that's how they're formatted), but it's not going to help for dates formatted a different way.

Does anyone have any thoughts on either how to ignore these, or get the 2 spaces to replace with one?

Thanks,
fEtch

Try this:

Code:

---

With Globals.ThisAddIn.Application.ActiveSheet.Cells
    Do Until .Find(What:="  ", LookIn:=xlValues, LookAt:=xlPart, SearchFormat:=False) Is Nothing
        .Replace What:="  ", Replacement:=" ", LookAt:=xlPart, SearchFormat:=False, ReplaceFormat:=False
    Loop
End With

---

That worked beautifully for the dates - however another issue was born from that.

I now have cells with data formatted as "Accounting", which is displayed as
"$ 1.24"

If I change your code to LookIn:=Excel.XlFindLookIn.xlFormulas, the "Accounting" cells are not an issue, but the dates are - if I leave it as LookIn:=Excel.XlFindLookIn.xlValues, the dates are an issue and the Accounting cells are not. Any other suggestions? lol