See if you can live with this?
Sub DigitsToX()
Dim anyCell As Object
Dim anyText As String
Dim LC As Integer
Const Digits = "0123456789"
'
'WARNING: run on a copy of your workbook
'this process DESTROYS the content of the
'cells it works on, including 'erasing'
'any formulas
'
Application.ScreenUpdating = False
ActiveSheet.UsedRange.Select
'since destroying numeric values that
'other cell formulas are dependent on
'will result in a #VALUE error, first
'convert all formulas to their results
'as mentioned - this destroys the formula
'pretty much the same as Edit | Paste Special w/Values
'
For Each anyCell In Selection
anyCell.Formula = anyCell.Value ' just do it!
Next
'now you can safely convert Digits to x's
For Each anyCell In Selection
If Not (IsEmpty(anyCell)) Then
'added test, use this to keep from
'x-ing out numbers in phrases
'as changing Page 99 Footnote 4
'to become
'Page xx Footnote x
'
If IsNumeric(anyCell.Value) Then
anyText = anyCell.Text
If Len(anyText) 0 Then
For LC = 1 To Len(anyText)
If InStr(Digits, Mid(anyText, LC, 1)) Then
Mid(anyText, LC, 1) = "x"
End If
Next
anyCell.Value = anyText
anyCell.HorizontalAlignment = xlRight
End If ' Len() check
End If ' check for numeric content
End If ' Empty check
Next
Range("A1").Select
Application.ScreenUpdating = True
End Sub
It fairly well preserves the format and layout except when the Accounting
format has been chosen (that puts leading spaces between the $ and 1st digit
to keep the $ symbol and decimal aligned in a column). The format looks the
same when you examine it in
VB as that for Currency where the $ is butted up
against the 1st digit. So I can't determine when I need to put some spacing
between the $ symbol and 1st digit.
But the dollar sign, commas and decimal are preserved, and if format added a
space at the right end, that's preserved also. I do a right-align to make it
look like most numbers do, although we can probably do testing for other
alignment and emulate it fairly easily.
" wrote:
On Mar 10, 4:33 pm, JLatham <HelpFrom @ Jlathamsite.com.(removethis)
wrote:
ActiveSheet.UsedRange.Select
[....]
For Each anyCell In Selection
What does ActiveSheet.UsedRange.Select do? It seems that "for each
cell in selection" works without it.
anyContent = anyCell.Value
[...]
For LC = 1 To Len(anyContent)
If InStr(Digits, Mid(anyContent, LC, 1)) Then
Mid(anyContent, LC, 1) = "x"
End If
Next
anyCell.Formula = anyContent
This is close to what I want to do. However, I lose certain things
such as commas separating 1000s because that is part of the cell's
numeric format, not the value.
Because the numeric formats of the cells that I will apply the macro
to vary in appearance (e.g. different number of decimal places, with
and without "$", etc), I think the ideal logic would be something like
this:
dim content as string
content = Display(cell.value)
for i = 1 to len(content)
'replace digits with "x"
next
cell.formula = content
What can I use for Display()?
In other words, I am looking for a VBA (or worksheet) function whose
result will be the cell value exactly as Excel would format it
according to Format - Cells options (all of them).
I don't think Format(cell.value) (without format expression) does not
work for me. I think I would need a way to adapt the format
expression (second argument) based on existing the cell format.
Sounds hard.
Caveat: When I say "exactly as Excel would format it", I am not sure
what to expect if the cell alignment is Right Adjusted with some
indentation applied. I would like the result to rely on the cell
format for that aspect (indentation).