我試圖在 VBA 中創建一個函式，該函式根據函式的輸入將資料從一張紙復制到另一張紙。但是，我很難在實際函式本身中使用函式的引數。
下面是我的代碼：正如你所看到的，我想要的范圍現在是硬編碼的，這是有效的！但是我無法讓它接受我作為引數傳遞給函式的范圍。我究竟做錯了什么？
資料應代替范圍（F35：F65），目標應代替范圍（C6）
Function Copytranspose(data As Range, target As Range)
Worksheets("Data").Activate
ActiveSheet.Range("F35:F65").Copy
Worksheets("Totalizers").Activate
ActiveSheet.Range("C6").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Function
Sub tempo()
Call Copytranspose(Range("F35:F65"), Range("C6"))
End Sub
uj5u.com熱心網友回復：
復制范圍的值
兩個硬編碼作業表（范圍地址（字串）引數）
Option Explicit
Sub CopyRange( _
ByVal SourceRangeAddress As String, _
ByVal DestinationFirstCellAddress As String, _
Optional ByVal DoTranspose As Boolean = False)
Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
Dim sws As Worksheet: Set sws = wb.Worksheets("Data")
Dim srg As Range: Set srg = sws.Range(SourceRangeAddress)
Dim dws As Worksheet: Set dws = wb.Worksheets("Totalizers")
Dim dfCell As Range: Set dfCell = dws.Range(DestinationFirstCellAddress)
Dim drg As Range
If DoTranspose Then
Set drg = dfCell.Resize(srg.Columns.Count, srg.Rows.Count)
drg.Value = Application.Transpose(srg.Value)
Else
Set drg = dfCell.Resize(srg.Rows.Count, srg.Columns.Count)
drg.Value = srg.Value
End If
End Sub
Sub tempo()
' Either transpose...
CopyRange "F35:F65", "C6", True ' 'C6:AG6'
' ... or do not transpose:
'CopyRange "F35:F65", "C6" ' 'C6:C36'
End Sub
任何范圍（范圍引數）
Option Explicit
Sub CopyRange( _
ByVal srg As Range, _
ByVal dfCell As Range, _
Optional ByVal DoTranspose As Boolean = False)
Dim drg As Range
If DoTranspose Then
Set drg = dfCell.Resize(srg.Columns.Count, srg.Rows.Count)
drg.Value = Application.Transpose(srg.Value)
Else
Set drg = dfCell.Resize(srg.Rows.Count, srg.Columns.Count)
drg.Value = srg.Value
End If
End Sub
Sub tempo()
' Either transpose...
CopyRange Range("F35:F65"), Range("C6"), True ' 'C6:AG6'
' ... or do not transpose:
'CopyRange Range("F35:F65"), Range("C6") ' 'C6:C36'
End Sub
uj5u.com熱心網友回復：
像這樣的東西？
Sub CopyData(sourceRange As Excel.Range, targetRange As Excel.Range)
targetRange.Resize(sourceRange.Rows.Count, sourceRange.Columns.Count).Value = sourceRange.Value
End Sub
Sub TestCopyData()
CopyData Sheets("Sheet1").Range("a1:a3"), Sheets("sheet1").Range("q1")
End Sub
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/375569.html