The following Excel VBA takes AGES to run. Is there a way to speed it up?

Question

I have a simple Excel VBA script that runs through data in Column A and replaces all dashes (-) for slashes (/). It then formats the cell as a Fraction and moves on to the next cell.

The problem is that it takes AGES to execute; probably because it's a loop script.

Can anybody provide me with a working and fast replacement pretty please?

Sub ReplaceDashes()

Worksheets("Scraped Data").Range("A1:A300").Select

Dim c As Range
For Each c In Selection
For x = 1 To 500
c.Formula = Application.WorksheetFunction.Substitute(c.Formula, "-", "/")
Selection.NumberFormat = "??/??"
Next x
Next c

End Sub

Answer 1

The way you coded the algorithm it loops for 150,000 times.

I don't really understand, what you r for x =1 to 500 loop does.

It does not seem to be related to substitution taking place in the range A1 to A300.

So I would suggest that you either correct the for x loop to become functional, or you just drop it so that your resulting loop would become:



Dim c As Range

For Each c In Selection

c.Formula = Application. WorksheetFunction. Substitute( c.Formula, "-", "/" )

Selection.NumberFormat = "??/??"

Next c

Answer 2

What is the purpose of the inner loop...for x = 1 to 500?

I don't see x being used anywhere. Maybe Yahoo ate it?



If you can eliminate the inner loop, then you'll only do the real work 300 times.

With the inner loop, you'll do it 150,000 times. Huge difference.



I really can't see a need for that inner loop. If there is one, explain. Maybe there's a way to short circuit it.

Answer 3

I am not sure what your 'x' loop from 1 to 500 does for you, but it is using a lot of time to do it.



Would this not do as you require?



Sub ReplaceDashes()

Dim i

For i = 1 To 300

Cells(i, "A").Value = Cells(i, "A").Application. _

Substitute(Cells(i, "A"), "-", "/")

Cells(i, "A").NumberFormat = "??/??"

Next

End Sub