Forum Discussion
WorksheetFunction.Substitute fails on some cells but works on other cells in a column
Hi all,
I use :
cell = WorksheetFunction.Substitute(cell, ".", ",")
on column A of the attached "Example" workbook to get the results in column B.
But as you can notice the formula does not work for all the cells in column A.
Appreciate your help.
5 Replies
You have attached a .xlsx workbook, so there is no macro.
If you set the horizontal alignment of column A to General, you'll see that some values are left-aligned (so they are text) and others are right-aligned (they are numbers). I suspect that this causes your problem.
Thank you for your reply.
I did not attach a .xlsm file for security reason (I didn't know if I could). Please see the new .xlsm file I'm attaching with the macro.
I suspected some values could be text so I tried converting them, but still can't get all the cells to work.
Please note that I'm converting column "A" and column "D" serves as a back up of the original data.
This i the result when I run your macro using my default system settings (decimal separator = point).
Note that there are no points in column A anymore.
But if I set the decimal separator to comma, this is the result:
Note that several cells still contain a point, for example A3 and A4.
This is because those cells originally contained a number value, and the point is the thousands separator. Excel does not see the thousands separator as part of the value - it is merely part of the display. So that point is not replaced with a comma.
What is your decimal separator?
And can you provide some examples of the desired result?
