☰

在调用另一个宏时创建单元格引用

tag-icon • tag-icon microsoft-excel vba macros
在调用另一个宏时创建单元格引用

我正在尝试将可变数量的数据行（值和格式）从一个工作表复制到另一个工作表。为此，我在网上找到并成功找到了一些 VBA 代码，它可以一次复制一个单元格Copy_ValueFormat(cell1 As Range, cell2 As Range)

但是当我从嵌套的 For 循环中调用该代码时，Call 会崩溃。我显然没有正确格式化 Call 中的单元格引用，因为我收到以下错误：[运行时错误‘9’：下标超出范围]

有人能告诉我我做错了什么吗？

'THIS IS THE VBA I GOT OFF THE
'Copy Value and formats from one cell to another
Sub Copy_ValueFormat(cell1 As Range, cell2 As Range)
Dim sel As Range
Set sel = Selection

Application.ScreenUpdating = False

cell1.Copy
cell2.PasteSpecial Paste:=xlPasteFormats

cell1.Copy
cell2.PasteSpecial Paste:=xlPasteValues

sel.Activate
Application.CutCopyMode = False

Application.ScreenUpdating = True

End Sub



'THIS IS SOME TEST CODE THAT SUCCESSFULLY CALLS   "Copy_ValueFormat(...)"
Sub TestCopy()
Call Copy_ValueFormat(Range("ONGOING!B2"), Range("BF2"))
End Sub



'THIS IS MY CODE THA CRASHES WHEN I MAKE THE CALL TO "Copy_ValueFormat(...)"
Sub TestForNext()
Dim i As Long
Dim j As Long

Sheets("JUNK").Select
Cells.ClearContents

For i = 1 To 10   'Row Counter
    For j = 1 To 20   'Column Counter
    Call Copy_ValueFormat(Sheets("ONGOING!").Cells(i, j), Sheets("JUNK").Cells(i, j))
    Next j
Next i

End Sub

答案1

您无需选择某项内容即可对其进行操作，并且收集当前选择以返回到该内容的想法也是不必要的。调用Application.ScreenUpdating只会使屏幕闪烁。

用来Call运行子程序的用法已被禁止。请注意，如果没有它，传递的参数将不会被括起来。

xlPasteFormats 之后原始复制源仍然存在；您不需要再次复制它。

Sub Copy_ValueFormat(cell1 As Range, cell2 As Range)

    cell1.Copy
    cell2.PasteSpecial Paste:=xlPasteFormats
    cell2.PasteSpecial Paste:=xlPasteValues

    Application.CutCopyMode = False

End Sub

Sub TestForNext()

    Dim i As Long, j As Long

    'if you want to halt screen updating, this is where it should be
    Application.ScreenUpdating = False

    With Worksheets("JUNK")

        .Cells.Clear    '<~~ clear values and formats

        For i = 1 To 10   'Row Counter
            For j = 1 To 20   'Column Counter
                Copy_ValueFormat Worksheets("ONGOING!").Cells(i, j), .Cells(i, j)
            Next j
        Next i

    End With

    Application.ScreenUpdating = True

End Sub

相关内容