VBA Replace All '/' in a sheet, except for formulas

[LarsB24]

Hi everyone,

I am making a tool to convert a messy data sheet with thousands of rows, coming out of an online pdf-to-excel converter, into a series of separate worksheets containing the necessary associated data. Each sheet is named after a section of the data, and this sheet name is used to collect all the corresponding data from the big input sheet.
Unfortunately, the sections can have '/' symbols or other illegal sheet-name characters in them, so I would need to replace all '/' in the document with a '-' symbol.
The problem is that the online tool also creates cells with '/' that start with the symbol '=', so excel thinks they are formulas, even though the cell format is set to text, and will give an error when I try to change them.

Is there a way in VBA to do 'replace all' on millions of cells at once, but skipping all cells that start with the '=' symbol? I can't replace the '=' symbol itself, as my tool uses it as a marker to find the correct values that it needs.

Thanks in advance!

 

[Micron]

If you can replace a character in millions of cells "at once" with vba without looping through them I don't know how. Would not be surprised if it's possible and I imagine it would involve building a huge array. This would probably cause you to run out of memory whereas looping would not, but would take some time. So if it's a one-off thing and you are forced to loop, then an option would be to start the loop and go for lunch

I think you're asking how to avoid doing this in cells with a formula. For that you could use Range.HasFormula property on the cells, which should be more efficient than testing if the first character is "=". Replace() function would be the way to replace any part of a value in a cell.

 

[MARK858]

I suspect the performance will be rubbish if there are too many occurrences or too many non-contiguous areas (if it doesn't grind to a halt) but maybe (test on a copy of your data)....

Cells.SpecialCells(xlCellTypeConstants, 23).Replace "/", "-", xlPart

 

[LarsB24]

I suspect the performance will be rubbish if there are too many occurrences or too many non-contiguous areas (if it doesn't grind to a halt) but maybe (test on a copy of your data)....

Cells.SpecialCells(xlCellTypeConstants, 23).Replace "/", "-", xlPart

Thank you very much, that actually works! And performance seems good as well after testing it on smaller data sets!

 

[MARK858]

Happy it helped and welcome to the forum