vba - 运行时错误 13 : Type Mismatch on code when I add certain lines of code

Question

所以我的代码工作正常，直到我尝试添加嵌套的 for 循环，然后我开始收到运行时错误 13。错误所在的行被 *** 包围

这是我之前工作的代码:

Dim LR As Long
Dim ColLtr As String
Dim Ave As Double
Dim STDev As Double

For i = 1 To DateTime_Column

ColLtr = Replace(Cells(1, i).Address(True, False), "$1", "")
    Ave = Application.Average(Range(ColLtr & "1:" & ColLtr & LR)) ' Based on     all values
    STDev = Application.STDev(Range(ColLtr & "1:" & ColLtr & LR)) ' Based on     all values

Next i

这是得到错误的代码:

Dim LR As Long
Dim ColLtr As String
Dim Ave As Double
Dim STDev As Double
Dim q As Integer
ReDim Range(LR1) As Variant

For i = 1 To DateTime_Column

Sheets(1).Select

ColLtr = Replace(Cells(1, i).Address(True, False), "$1", "")
 ***Ave = Application.Average(Range(ColLtr & "2:" & ColLtr & LR))*** ' Based on all values

For q = 1 To LR1
Range(q) = WorksheetFunction.Abs(Cells(q + 1, ColLtr) - Cells(q, ColLtr))
Next q

接下来我

我最初的想法是 LR 很长，所以我尝试将其更改为字符串数据类型，但这也不起作用，也许它与 for 循环有关？

Answer 1

重新使用 Range object 之类的名称绝不是一个好主意不同于它原本打算成为的东西。在您的情况下尤其如此，因为您决定允许 ActiveSheet property成为默认值Range.Parent property同时重新定义范围 .

Dim LR As Long, LR1 As Long, q As Long
Dim Ave As Double, STDev As Double
ReDim vals(LR1) As Variant


With Sheets(1)
    For i = 1 To DateTime_Column
        LR = .Cells(.Rows.Count, i).End(xlUp).Row
         Ave = Application.Average(.Range(.Cells(2, i), .Cells(LR, i))) ' Based on all values

        For q = 1 To LR1
            vals(q) = WorksheetFunction.Abs(.Cells(q + 1, i) - .Cells(q, i))
        Next q
    Next i
End With

我删除了整个 ColLtr业务并明确提供了 Range.Parent property对于所有 Range和
.Cells对象。

你仍然需要对 vals 做一些事情。数组并确保 LR1被正确定义。