vba 如何将VBA数组的一部分粘贴到excel范围
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25185230/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
how to paste part of VBA array to excel range
提问by bramvs
Is it somehow possible to past part of an vba array to an excel range?
是否有可能将 vba 数组的一部分传递到 excel 范围？
I need to do something like this:
我需要做这样的事情：
dim testday() as Variant
testday = Sheets("raw").Range("E745:BN745").Value
Sheets("raw").Range("E745:BN745").Value = ""
Sheets("Interface").Range("B4:E4") = testday(3, 4, 5, 6).Value
but this doesn't work... is there a way to fix this? Thx!
但这不起作用......有没有办法解决这个问题？谢谢！
采纳答案by Jean-Fran?ois Corbett
You can either:
您可以：
Use a loop to copy the values you need to a new array, and write that to your range. If you're going to do this often, you can write a reusable function that does this. Or,
Read only what you need from your
"raw"sheet, and write that to your range, as illustrated below. This is probably the simplest solution in your particular case.
使用循环将您需要的值复制到新数组中，并将其写入您的范围。如果您打算经常这样做，您可以编写一个可重用的函数来执行此操作。或者，
仅从工作
"raw"表中读取您需要的内容，并将其写入您的范围，如下图所示。在您的特定情况下，这可能是最简单的解决方案。
Dim testday() As Variant
testday = Sheets("raw").Range("G745:J745").Value ' only read what you need
Sheets("raw").Range("E745:BN745").ClearContents
Sheets("Interface").Range("B4:E4") = testday(3, 4, 5, 6).Value
回答by evenprime
You can Slicethe Arrayusing Indexfunction
您可以切片的Array使用Index功能
Sub Slicer()
Dim testday() As Variant
testday = Sheets("raw").Range("E745:BN745").Value
Sheets("raw").Range("E745:BN745").Value = ""
Sheets("Interface").Range("B4:E4")= Application.Index(testday, 1, Array(3, 4, 5, 6))
End Sub
回答by Ioannis
If the array you want to copy is one-dimensional, and you need to copy contiguous cells you can use the CopyMemoryfunction:
如果要复制的数组是一维的，并且需要复制连续的单元格，则可以使用CopyMemory函数：
Option Explicit
Private Declare PtrSafe Sub CopyMemory Lib "kernel32" _
Alias "RtlMoveMemory" (Destination As Any, Source As Any, ByVal Length As Long)
Sub test()
Dim vArr(), vArr2()
Dim lCnt As Long
Dim lStartIndex As Long, lFinishIndex As Long, lLength As Long
With Application
vArr = .Transpose(.Transpose(Range("A1:R1").Value2))
End With
lStartIndex = 3
lFinishIndex = 6
lLength = lFinishIndex - lStartIndex + 1
ReDim vArr2(1 To lLength)
CopyMemory vArr2(1), vArr(lStartIndex), lLength * 16
For lCnt = LBound(vArr2) To UBound(vArr2)
Debug.Print vArr2(lCnt)
Next lCnt
Range("A2").Resize(1, UBound(vArr2)).Value2 = vArr2
End Sub
Tested with first row being
测试第一行是
67.2 9 57.2 boo 52 64 76 39 48 50 28 54 96 29 98 25 68 19
returns
返回
57.2 boo 52 64
on the second row. So your snippet would change as
在第二行。所以你的片段会改变为
dim testday(), testday2()
With Application ' Value2 is faster than Value
testday = .Transpose(.Transpose(Sheets("raw").Range("E745:BN745").Value2))
End With
Sheets("raw").Range("E745:BN745").ClearContents ' Good suggestion by JFC
CopyMemory testday2(1), testday(3), 4 * 16 ' Variant = 16 Bytes
Sheets("Interface").Range("B4:E4").Value2 = testday2 ' I would do Resize instead
I hope this helps!
我希望这有帮助！
