合并2个Excel单元格的内容，保持字符格式不变（使用VBA） [英] Merge contents of 2 Excel cells keeping character format intact (using VBA)

问题描述

正如标题所示：我尝试将2个单元格的内容合并到Excel中的第3个。有一个类似的问题在这里SO，但这些解决方案不保持字符格式完好无损。例如，源单元格内容的部分格式为粗体和红色，其他部分是正常的。当我把它们合并起来像

  Range（A3）= Range（A1）&范围（A2）
  

然后A1和A2的任何格式将丢失。我需要的是保持格式不变的解决方案。这将是一个更大的VBA程序的一部分，所以我需要一个VBA解决方案，没有公式。 Excel版本是2002（XP）。

解决方案

Doc，这是一个有趣的问题。我被困了，但看到了价值，所以经过一番搜索，这里是我发现的。从 vbaexpress ，我获得了单元格格式的基本了解，其中我修改为以下使用。

  Sub Merge_Cells（）
 Dim iOS As Integer 
 Dim rngFrom1 As Range 
 Dim rngFrom2 As Range 
 Dim rngTo As Range 
 Dim lenFrom1 As Integer 
 Dim lenFrom2 As Integer 
 
设置rngFrom1 =单元格（1，1）
设置rngFrom2 =单元格（1，2）
设置rngTo =单元格（1，3）
 lenFrom1 = rngFrom1.Characters.Count 
 lenFrom2 = rngFrom2.Characters.Count 
 
 rngTo.Value = rngFrom1.Text& rngFrom2.Text 
 
对于iOS = 1至lenFrom1 
带有rngTo.Characters（iOS，1）.Font 
 .Name = rngFrom1.Characters（iOS，1）.Font。名称
 .Bold = rngFrom1.Characters（iOS，1）.Font.Bold 
 .Size = rngFrom1.Characters（iOS，1）.Font.Size 
 .ColorIndex = rngFrom1.Characters（ iOS，1）.Font.ColorIndex 
结束
下一个iOS 
对于iOS = 1到lenFrom2 
带有rngTo.Characters（lenFrom1 + iOS，1）.Font 
 .Name = rngFrom2.Characters（iOS，1）.Font.Name 
 .Bold = rngFrom2.Characters（iOS，1）.Font.Bold 
 .Size = rngFrom2.Characters（iOS，1） .Font.Size 
 .ColorIndex = rngFrom2.Characters（iOS，1）.Font.ColorIndex 
结束
下一个iOS 
 
 End Sub 
  

只需将特定单元格的3个单元格（）更改即可。也许有人可以找到一个更干净的方式，但是当我测试这个，它的工作，我明白你（和我）想要。

希望这有助于...

As the title says: I try to merge the contents of 2 cells into a 3rd in Excel. There was a similar question here on SO, but those solutions do not keep the character format intact. For example, parts of the source cell contents are formatted bold and red, other parts are normal. When I merge them like

 Range("A3") = Range("A1") & Range("A2")

then any formatting of A1 and A2 is lost. What I need is a solution keeping the format intact. This is going to be part of a bigger VBA program, so I need a VBA solution, no formula, please. Excel version is 2002(XP).

解决方案

Doc, that is an interesting question. I was stumped myself but saw the value, so after some searching, here is what I found. From vbaexpress I got the basic understanding of in cell formatting, which I modified for your use below.

Sub Merge_Cells()
Dim iOS As Integer
Dim rngFrom1 As Range
Dim rngFrom2 As Range
Dim rngTo As Range
Dim lenFrom1 As Integer
Dim lenFrom2 As Integer

  Set rngFrom1 = Cells(1, 1)
  Set rngFrom2 = Cells(1, 2)
  Set rngTo = Cells(1, 3)
  lenFrom1 = rngFrom1.Characters.Count
  lenFrom2 = rngFrom2.Characters.Count

  rngTo.Value = rngFrom1.Text & rngFrom2.Text

  For iOS = 1 To lenFrom1
    With rngTo.Characters(iOS, 1).Font
      .Name = rngFrom1.Characters(iOS, 1).Font.Name
      .Bold = rngFrom1.Characters(iOS, 1).Font.Bold
      .Size = rngFrom1.Characters(iOS, 1).Font.Size
      .ColorIndex = rngFrom1.Characters(iOS, 1).Font.ColorIndex
    End With
  Next iOS
  For iOS = 1 To lenFrom2
    With rngTo.Characters(lenFrom1 + iOS, 1).Font
      .Name = rngFrom2.Characters(iOS, 1).Font.Name
      .Bold = rngFrom2.Characters(iOS, 1).Font.Bold
      .Size = rngFrom2.Characters(iOS, 1).Font.Size
      .ColorIndex = rngFrom2.Characters(iOS, 1).Font.ColorIndex
    End With
  Next iOS

End Sub

Just change out the 3 cells() with your specific cells. Maybe someone can find a cleaner way, but when I tested this, it worked as I understand you (and I) would like.
Hope this helps...

这篇关于合并2个Excel单元格的内容，保持字符格式不变（使用VBA）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！