Excel VBA - 将格式化符号添加到单元格中的文本，保留原有文本的字符格式 [英] Excel VBA - add formatted symbol to text in a cell, keeping character formats of pre-existing text

问题描述

这是继续我的问题在 excel vba在单元格中选择第二个最后一个字符

我正在写一个宏，在单元格中的现有文本之后插入一个红色的刻度（或向下箭头）。

I am writing a macro to insert a red tick (or down arrow) after the existing text in a cell.

我发现插入的代码会删除原始单元格内容的自定义字符格式（粗体带下划线的红色等）。

The code I found to insert it, removes the customised character formatting of the original cell contents (bold underlined red etc).

ActiveCell.FormulaR1C1 = ActiveCell & " P "

我无法解决如何插入字符和 em>单元格中预先存在的字符格式（一些文本红色，其他文本粗体，其他文本可能带下划线）。

I can't work out how to insert the characters and retain the pre-existing character formats in the cell (some text red, other text bold, other text may be underlined.)?

我发现一个宏>替换保留现有格式的字符，并且想知道这是否可以更改为添加单元格末尾的刻度和空格，并格式化？

I found a macro which replaces characters retaining the existing formats, and wondering if this might be changed to instead add the tick and spaces at the end of the cell, and format them?

我发现另一个宏，它在复制该值后将每个字符的格式复制到一个新单元格，在合并保留字符格式的两个Excel单元格的内容（使用VBA），如果可以调整为重新格式化原来的单元格中的人物...我有点st ped！

I found another macro which copies the format of each character to a new cell after copying the value, at Merge contents of 2 Excel cells keeping character format intact (using VBA), if it could be tweaked to re-format the characters in the original cell... I am a bit stumped!

或者还有一个更简单的方法？

Or maybe there is an even simpler way?

任何帮助都非常感激。

===============
（替换宏（编辑的：）

=============== (The replace macro (edited verson of Prevent reformatting of characters when changing line in cell:)

    STRINGTODELETE = InStr(1, cell.Value, " ") 'define + find string to delete, ANYWHERE in cell (TODO inputbox)
    STRINGTODELETE_LENGTH = 1  'define length of string to delete (TODO get length from input)

    Do Until STRINGTODELETE = 0 'loop within cell until no more occurences found
        cell.Characters(STRINGTODELETE, STRINGTODELETE_LENGTH).Delete 'remove found occurrence of SPECIFIED STRING
        cell.Characters(STRINGTODELETE, 0).Insert Chr(10) 'insert line break where SPECIFIED STRING used to be

推荐答案

EDITED 公式将被缩进d更改您以前拥有的任何特殊格式（我的初始帖子忽略了）。但是，随着您的研究间接显示，您可以使用character.insert更改文本，但您需要插入字符的长度以避免更改自定义格式。

EDITED: Using ActiveCell.Value or ActiveCell.Formula will indeed change any SPECIAL formatting you previously had (my initial post overlooked that). However, as your research indirectly displayed, you can change the text by using character.insert, but you need to have the character length of where to insert to avoid changing your custom formatting.

尝试这个而不是值或公式设置。

Try this instead of value or formula setting.

ActiveCell.Characters(ActiveCell.Characters.Count + 1, 1).Insert (" P ")

然后，使用类似的方法继续更新第二个到最后一个字符（这本质上是在最后一个字符之前编辑1个字符） 。

Then you proceed to update the second to last character using a similar approach (this is essentially editing 1 character before the last character).

With ActiveCell.Characters(ActiveCell.Characters.Count -1, Length:=1).Font
        .Name = "Wingdings 2"
        .FontStyle = "Bold"
        .Size = 10
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = 1
        .TintAndShade = 0
        .ThemeFont = xlThemeFontNone
End With

这篇关于Excel VBA - 将格式化符号添加到单元格中的文本，保留原有文本的字符格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！