Photo by Michael Dziedzic on Unsplash
The Replace function in Excel is fast - really fast and it works very nicely on a range of data to find and replace data. However, the replace function has a hidden bug and this article explores the problem with an example and a video. The following is a simple example of the executable line of code replacing Sports to Sport in range B1 to B10.
Sheet1.Range("B1:B10").Replace "Sports", "Sport"
The issue I have with the code is when you have made a search on the entire workbook the above code is disregarded and the entire workbook is changed. It happened to me while I was using a clients file. I messed it up as I believed that the above code was adequate to only change a specific range. I was so concerned that I made a video to outline the issue. The following video outlines the issue. It is worth a watch.
In summary be very careful when you use the replace formulation in the VBA environment. It can cause some problems and they may well be serious if you save the file without noticing what has gone wrong. Tread carefully with the replace function.
