VBA给合并单元格黏贴赋值

最新推荐文章于 2024-09-05 17:21:52 发布

原创最新推荐文章于 2024-09-05 17:21:52 发布 · 6k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#vba #excel

vba 专栏收录该内容

4 篇文章

订阅专栏

本文介绍了一种通过VBA代码在Excel中解决单元格合并后赋值的问题，包括拆分合并单元格、赋值并重新合并的步骤。详细解释了如何利用VBA的DataObject和HTML格式进行数据处理。

由于EXCEL中通过剪切板给合并后的单元格赋值时候会出现如下错误

所以在vba中也一定不能了，其实可以通过一种workaround的方式来间接实现这个需求，实现步骤如下

一、拆分合并的单元格

二、给第一个单元格赋值

三、合并单元格

下面是具体的使用

Sub tttt()
Dim i, g
Dim objData As DataObject
Dim sHTML As String
Dim sSelAdd As String
Const sTEMP As String = "||||"
Application.EnableEvents = False
g = ActiveSheet.UsedRange.Rows.Count
For i = 9 To g

'拆分单元格
Range("E" & (i) & ":V" & (i)).Select
'With Selection
'.HorizontalAlignment = xlCenter
'.VerticalAlignment = xlTop
'.WrapText = True
'.Orientation = 0
' .AddIndent = False
'.IndentLevel = 0
' .ShrinkToFit = False
' .ReadingOrder = xlContext
' .MergeCells = False
' End With
Selection.UnMerge


If True Then
If LCase(Left(Range("E" & (i)).Value, 6)) = "<html>" Then
Set objData = New DataObject
sHTML = Range("E" & (i)).Value
sHTML = Replace(sHTML, "<br />", sTEMP)
objData.SetText sHTML
objData.PutInClipboard

ActiveSheet.Paste Destination:=Range("E" & (i))
'合并单元格
Range("E" & (i) & ":V" & (i)).Select
' With Selection
'.HorizontalAlignment = xlCenter
'.VerticalAlignment = xlTop
' .WrapText = True
' .Orientation = 0
' .AddIndent = False
' .IndentLevel = 0
' .ShrinkToFit = False
' .ReadingOrder = xlContext
' .MergeCells = False
' End With
Selection.Merge

End If
End If
Next
Application.EnableEvents = True
End Sub