excel - 如何在 VBA 循环中复制粘贴值？

问题描述

我正在尝试将单元格 e2 和 f2 的内容的粘贴单元格值复制回相同的相应单元格中，作为循环过程的一部分，该循环过程逐行重复此任务（下一行将是 e3 和 f3 的相同过程，并且然后 e4 和 f4 等，直到到达 B 列中的空白单元格。

这是我无法弄清楚的代码子集（它返回错误 1004，Range 类的 PasteSpecial 方法失败：

        tickersSheet.Range("E2").Offset(counter, 0).Copy
        tickersSheet.Range("E2").Offset(counter, 0).PasteSpecial Paste:=x1PasteValue
        
        tickersSheet.Range("F2").Offset(counter, 0).Copy
        tickersSheet.Range("F2").Offset(counter, 0).PasteSpecial Paste:=x1PasteValue

这是完整的代码：

Public Sub Email()

    Application.Goto Reference:="Email"

    ' Set a reference to tickers sheet
    Dim tickersSheet As Worksheet
    Set tickersSheet = ThisWorkbook.Worksheets("Tickers")

    ' Set a reference to tickers range
    Dim tickersRange As Range
    Set tickersRange = tickersSheet.Range("B2:B502")

    ' Set a reference to moves sheet
    Dim movesSheet As Worksheet
    Set movesSheet = ThisWorkbook.Worksheets("Moves")
    
    ' Set a reference to moves starting cell
    Dim movesFirstCell As Range
    Set movesFirstCell = movesSheet.Range("C4")
    
    ' Use a counter to make everything else relative
    Dim counter As Long
    counter = 0
    
    ' Loop through cells in tickers sheet starting in B2
    Dim tickersCell As Range
    For Each tickersCell In tickersRange
    
        If tickersCell.Value <> vbNullString Then
            
            
            ' Set row reference
            Dim rowReference As Long
            rowReference = (-2 + counter)
            
            ' Store formula
            movesFirstCell.Formula2R1C1 = "=Tickers!R[" & rowReference & "]C[-1]"
            
            ' Wait for table to generate
            Application.Wait (Now + TimeValue("0:00:10"))
            
            ' Copy range as picture
            movesSheet.Range("B2:L129").CopyPicture
            tickersSheet.Range("H2").Offset(counter, 0).PasteSpecial
            
            ' Copy paste values for correlations
            tickersSheet.Range("E2").Offset(counter, 0).Copy
            tickersSheet.Range("E2").Offset(counter, 0).PasteSpecial Paste:=x1PasteValue
            
            tickersSheet.Range("F2").Offset(counter, 0).Copy
            tickersSheet.Range("F2").Offset(counter, 0).PasteSpecial Paste:=x1PasteValue
            
            ' Increment counter
            counter = counter + 1
        
        End If
    
    Next tickersCell

End Sub

标签： excelvba

excel - 如何在 VBA 循环中复制粘贴值？

问题描述

解决方案

推荐阅读