使用 vba 将范围值复制到变体中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14428122/
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
copy range values into variant using vba
提问by user1155299
I have the following code in a macro but when I run it, it only copies the value from cell B2 rather than the entire range of values from B2:D2 as I have specified in line 5 of the below code.
我在宏中有以下代码，但是当我运行它时，它只复制单元格 B2 中的值，而不是像我在下面代码的第 5 行中指定的那样复制 B2:D2 中的整个值范围。
Sub copying()
Dim calval as variant
With worksheets("sheet1")
Set calval=.Cells(Rows.Count,"B").End(xlUp).Offset(2,3)
calval.value=Range("B2:D2").Value 'copy range of values
End With
End Sub
This is the data in cells B2:D2
这是单元格 B2:D2 中的数据
21.7 21.3 22.4
Can someone please tell me how to get calval to be assigned the full range of values from B2:D2, rather than just the value in B2.
有人可以告诉我如何让 calval 被分配到 B2:D2 的全部值范围，而不仅仅是 B2 中的值。
回答by CuberChase
You have a few problems here. Firstly when copying to variants, they are not objects so you don't need to use set. They also don't have ranges as they variables. You also need to copy the value to a variant before you can set it to the output range so the order is a little off. The following code will work:
你在这里有一些问题。首先，当复制到变体时，它们不是对象，因此您不需要使用 set。它们也没有范围，因为它们是变量。您还需要先将该值复制到一个变体，然后才能将其设置为输出范围，这样顺序就有点偏离了。以下代码将起作用：
Sub Copying()
Dim calval as Variant
'Read it in from the worksheet
With Worksheets("Sheet1")
calval=.Range("B2:D2").value
'Set it back to the dsired range using the bounds of the variant instead of hardcoding.
.Range(.Cells(Rows.Count,"B").End(xlUp).Offset(2,3), .Cells(Rows.Count,"B").End(xlUp).Offset(1 + UBound(calval, 1), 2 + UBound(calval, 2))).Value = calval
End With
End Sub
回答by bonCodigo
I understand you have accepted an answer, here is another method to achieve what you need: Using WorkSheetFunction.Transposemethod.
我知道您已经接受了一个答案，这是实现您需要的另一种方法：使用WorkSheetFunction.Transpose方法。
Dim calval as Variant
'--- Range("B2:D2").Value
calval = WorkSheetFunction.Transpose(Sheets(1).Range("B2:D2").Value)
You are free to use, offset, resizeaccording to your needs.
e.g. expand your range up to B12
您可以自由使用，offset，resize根据您的需要。例如将您的范围扩大到B12
calval = WorkSheetFunction.Transpose(Sheets(1).Range("B2:D2").Resize(10).Value)
