VBA Find and Replace

[Scott Huish]

I need to do the following in VBA as it is part of a bigger project.

I need to do a find and replace on , , and replace with ,

The problem is there could be multiple comma spaces, such as

, , ,

now that above Find and Replace would change that to , ,

If I was doing this manually I would repeat the Find and Replace until it didn't find anymore.

How can I do this in VBA?

So far this is what I have as a test for this:

Sub test()
Cells.Replace ", ,", ", "
End Sub

It doesn't however generate an error when it doesn't find any, how can I repeat the code as long as it is necessary?

 

[jbeaucaire]

If you know it will only be needed a maximum of 10 times, just use a quick loop:

Sub test()
For i = 1 To 10
    Cells.Replace ", ,", ", "
Next i
End Sub

 

[Scott Huish]

Yeah, I thought of that, but I have no way to know how many times it would be necessary. If I go to high, it's horribly inefficient, and if I go to low, then my data is not cleaned up as it should be. I may still go with that, I was just hoping there was some way to know when it couldn't find the search terms any longer.

I think I may have just figured it out, probably something along the lines of a Do Loop coupled with a Find so that it loops only as long as it finds the value.

 

[Scott Huish]

This is what I came up with:

Sub test()
Dim c As Range
Do
    Set c = Cells.Find(", ,")
    Cells.Replace ", ,", ","
Loop Until c Is Nothing
End Sub

 

[pgc01]

Hi Hotpepper

You can also do it without the range object:

Sub test()
While Not Cells.Find(", ,") Is Nothing
    Cells.Replace ", ,", ","
Wend
End Sub

 

[Jonmo1]

Hey Hotpepper...

I can't help but wonder where the data is coming from?
Is your bigger project creating the data that you need to do the replace on?
Or is it raw data you have recieved and have no control over?

I would think it may be easier and more effective to get the problem at the source...

 

[Scott Huish]

It is raw data I have no control over

Thanks pgc01, that worked really well!

 

[Jonmo1]

I know this is a day late and a dollar short, but...

does your data have any actual spaces (other than the spaces between the commas)?
I mean, does any of the ACTUAL data contain spaces?

If not, you could try something like this

x = Replace(Range("A1").Value, ",", " ")
x = Application.Trim(x)
x = Replace(x, " ", ",")

 

[Scott Huish]

Yes the actual data contains spaces, and it essentially needs to be applied to the entire spreadsheet.