excel - 来自动态范围的 VBA 范围值

Question

我正在尝试在不使用复制/粘贴功能的情况下创建一个范围值“粘贴”。我对 VBA 很陌生，不明白为什么我的代码不起作用。如果这是一种已经回答但我无法解决其他帖子的问题，我很抱歉。

我可以选择我希望我的值从中复制和粘贴的范围

name_task = [code].select
name_task_2 = [code].select

但我无法将值写入 name_task_2

name_task_2 = name_task.value

这行得通，对我来说，我写的东西是一样的，只是更复杂，但由于它不起作用，显然不一样:D

sht2.Range("C2:D12") = sht1.Range("A8:B18").value 

Excel sheet1

Sub Time_Estimate()

Application.ScreenUpdating = False

Dim name_task As Variant
Dim name_task_2 As Variant

Dim R_count As Double
Dim C_count As Double

Dim sht1 As Worksheet
Dim sht2 As Worksheet

Set sht1 = Sheet1 'Sheets("Tekla_2016")

name_task = sht1.Range("A8:B8", sht1.Range("A8").End(xlDown))
R_count = sht1.Range("A8:B8", sht1.Range("A8").End(xlDown)).Rows.Count
'Debug.Print name_task.Rows.Count
'Debug.Print name_task.Columns.Count
Debug.Print R_count

Set sht2 = Sheet2 'Sheets("Timeforbruk_2016 - UFERDIG")

name_task_2 = sht2.Range("C2:D2", sht2.Range("C2:D2").Offset(R_count - 1, 0))

'name_task_2 = name_task.value 'Why doesn't this work???

'sht2.Range("C2:D12") = sht1.Range("A8:B18").value 'This works but it's not dynamic


Application.ScreenUpdating = True

End Sub

编辑:
这段代码现在可以按我的意愿工作:

Sub Time_Estimate()

Application.ScreenUpdating = False

Dim name_task As Range
Dim name_task_2 As Range
Dim rng_sht1 As Range
Dim rng_sht2 As Range


Dim R_count As Double
Dim C_count As Double

Dim sht1 As Worksheet
Dim sht2 As Worksheet

Set sht1 = Sheet1 'Sheets("Tekla_2016")
Set sht2 = Sheet2 'Sheets("Timeforbruk_2016 - UFERDIG")

Set name_task = sht1.Range("A8:B8", sht1.Range("A8").End(xlDown))
R_count = sht1.Range("A8:B8", sht1.Range("A8").End(xlDown)).Rows.Count

Set name_task_2 = sht2.Range("C2:D2", sht2.Range("C2:D2").Offset(R_count - 1, 0))
name_task_2 = name_task.value

Application.ScreenUpdating = True

End Sub

Answer 1

someRange.Select

选择范围 someRange并返回 bool 值 True (您通常不会注意到这一点，因为您不会尝试将其分配给某物)。这意味着之后

name_task = [code].select
name_task_2 = [code].select

两个 name_task和 name_task_2是 bool 值 ( True ) 而不是范围。

在您发布的子中，您还有另一个问题:

name_task = sht1.Range("A8:B8", sht1.Range("A8").End(xlDown))

因为你没有指定 name_task是(除了 Variant ，基本上没有任何意义)，vba 将使用范围的 Value作为默认属性和 name_task将是一个包含 sht1.Range("A8:B8", sht1.Range("A8").End(xlDown)).Value 的变量数组.要分配范围对象，您需要使用 Set :

Set name_task = sht1.Range("A8:B8", sht1.Range("A8").End(xlDown))

来自 MSDN/ this answer :

Set Keyword. In VBA, the Set keyword is necessary to distinguish between assignment of an object and assignment of the default property of the object. Since default properties are not supported in Visual Basic .NET, the Set keyword is not needed and is no longer supported.

这实际上是一个很好的例子，为什么使用 Option Explicit 是个好主意。 (或者在您的情况下正确声明变量)。如果您声明 Dim name_task As Range VBA 会告诉您正确的线路有问题，并且故障排除变得容易得多。