excel - 将单独合并单元格中的值复制到一个合并单元格中，以逗号分隔的坐标

Question

我是VBA的初学者。我正在尝试将数据从 1 个工作簿复制到另一个工作簿，特别是将 2 个合并单元格中的数据作为坐标复制到 1 个合并单元格中。

Cell 1 = 58.634
Cell 2 = 63.458
Destination Cell = 58.634, 63.458

而且我还需要反向执行操作，例如：从以逗号分隔的坐标单元格中​​检索各个值并将它们粘贴到 2 个单独（合并）的单元格中。

截至目前，我的代码只是由多个单元格上的一系列简单数据复制/粘贴组成。多行这样：

Workbooks("Origin file.extension").Worksheets("sheet1").Range(" ").Copy
Workbooks("Destination file.extension").Worksheets("sheet1").Range("  ").PasteSpecial Paste:=xlPasteValuesAndNumberFormats

标签： excelvba

Answer 1

我已经评论了代码。

Option Explicit

Sub Sample()
    Dim wbOrigin As Workbook
    Dim wbDest As Workbook
    
    '~~> Change this to the relevant workbooks
    Set wbOrigin = Workbooks("Origin file.extension")
    Set wbDest = Workbooks("Destination file.extension")
    
    Dim wsOrigin As Worksheet
    Dim wsDest As Worksheet
    
    '~~> Change this to the relvant worksheets
    Set wsOrigin = wbOrigin.Sheets("Sheet1")
    Set wsDest = wbDest.Sheets("Sheet1")
    
    Dim rngOriginMergedOne As Range
    Dim rngOriginMergedTwo As Range
    Dim rngMergedDest As Range
    
    '~~> Change these to the relevant merged cells
    Set rngOriginMergedOne = wsOrigin.Range("A1")
    Set rngOriginMergedTwo = wsOrigin.Range("D1")
    Set rngMergedDest = wsDest.Range("A1")
        
    '~~> This is the delimiter
    Dim delim As String: delim = ", "
    
    '~~> Combining two values
    rngMergedDest.Value = rngOriginMergedOne.Value2 & delim & rngOriginMergedTwo.Value2
End Sub

相反，将rngMergedDest.Value = rngOriginMergedOne.Value2 & delim & rngOriginMergedTwo.Value2上面代码中的行替换为下面的行。

'~~> The Reverse
rngOriginMergedOne.Value = Split(rngMergedDest.Value2, delim)(0)
rngOriginMergedTwo.Value = Split(rngMergedDest.Value2, delim)(1)