☰

在呼叫另一個巨集時建立單元格引用

tag-icon • tag-icon microsoft-excel vba macros
在呼叫另一個巨集時建立單元格引用

我正在嘗試將可變數量的資料行（值和格式）從一個工作表複製到另一個工作表。為此，我在網路上找到了一些 VBA 程式碼，並成功地一次複製一個儲存格Copy_ValueFormat(cell1 As Range, cell2 As Range)

但是當我從嵌套的 For 循環中調用該程式碼時，呼叫會崩潰。我顯然沒有正確格式化呼叫中的單元格引用，因為我收到以下錯誤：[運行時錯誤'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

相關內容