vba 范围和 ActiveCell.Offset 运行时错误“1004”

Range and ActiveCell.Offset Run-time error '1004'

提问by user3832719

I'm trying to determine the minimum and maximum values of a 5 cell range (C:G) for all non-blank rows in a worksheet and place the respective results in columns L and M.

我正在尝试确定工作表中所有非空白行的 5 个单元格范围 (C:G) 的最小值和最大值，并将相应的结果放在 L 和 M 列中。

I'm getting a Run-time error '1004' Application-defined or object-defined error.

我收到运行时错误“1004”应用程序定义或对象定义的错误。

Sub test()
    ActiveSheet.Range("A1").Select
    ActiveCell.Offset(1, 0).Select
    Do While ActiveCell.Value <> Empty
        ActiveCell.Offset(0, 11) = WorksheetFunction.Min(Range(ActiveCell.Offset(0, 2), ActiveCell.Offset(0, 6)))
        ActiveCell.Offset(0, 12) = WorksheetFunction.Max(Range(ActiveCell.Offset(0, 2), ActiveCell.Offset(0, 6)))
        ActiveCell.Offset(1, 0).Select
    Loop
    ActiveSheet.Range("A1").Select
End Sub

I'm pretty sure my problem is in the specification of the range but not sure what it is.

我很确定我的问题出在范围的规格上，但不确定它是什么。

The first and last selects are just a convention I use.

第一个和最后一个选择只是我使用的约定。

The second select is to step past a header row.

第二个选择是越过标题行。

The third select is to increment the row.

第三个选择是增加行。

If there is a simpler way to do this, please let me know.

如果有更简单的方法可以做到这一点，请告诉我。

回答by chris neilsen

I can't reproduce the error you mention, your code seems to run as is.

我无法重现您提到的错误，您的代码似乎按原样运行。

That said there a many ways to improve this code

也就是说有很多方法可以改进此代码

1. Avoid Select(as mentioned in comments)
2. The Applicationobject offers Minand Maxfunctions, no need to use WorksheetFunctions for these
3. Better approach to rangereferences is a combination of Offsetand Resize

1. 避免Select（如评论中所述）
2. 该Application对象报价Min及Max功能，无需使用WorksheetFunctionS对于这些
3. 更好的range参考方法是结合Offset和Resize

Your code, refactored to used these techniques

您的代码，重构为使用这些技术

Sub Demo()
    Dim ws As Worksheet
    Dim rng As Range
    Dim rw As Range

    ' Get a reference to the source data range
    Set ws = ActiveSheet
    With ws
        Set rng = .Cells(2, 1)
        ' Just in case there is only one data row
        If Not IsEmpty(rng.Offset(1, 0)) Then
            Set rng = .Range(rng, rng.End(xlDown))
        End If
    End With

    ' Loop the range
    For Each rw In rng.Rows
        rw.Offset(0, 11) = Application.Min(rw.Offset(0, 1).Resize(, 5))
        rw.Offset(0, 12) = Application.Max(rw.Offset(0, 1).Resize(, 5))
    Next
End Sub

That said, you can go further and use a Variant Arrayapproach. This runs much faster than looping a range (impact will vary depending on number of data rows)

也就是说，您可以更进一步并使用一种Variant Array方法。这比循环范围运行得快得多（影响将取决于数据行数）

Sub Demo2()
    Dim ws As Worksheet
    Dim rng As Range
    Dim dat As Variant
    Dim res As Variant
    Dim i As Long

    ' Get a reference to the source data range
    Set ws = ActiveSheet
    With ws
        Set rng = .Cells(2, 1)
        ' Just in case there is only one data row
        If Not IsEmpty(rng.Offset(1, 0)) Then
            Set rng = .Range(rng, rng.End(xlDown))
        End If
    End With

    ' Set up source and result arrays
    dat = rng.Offset(, 2).Resize(, 5).Value
    ReDim res(1 To UBound(dat, 1), 1 To 2)

    With Application
        ' Loop the array
        For i = 1 To UBound(dat, 1)
            res(i, 1) = .Min(.Index(dat, i))
            res(i, 2) = .Max(.Index(dat, i))
        Next
    End With

    ' Return results to sheet
    rng.Offset(0, 11).Resize(, 2) = res
End Sub

Another technique is to avoid a loop entirely by (temporarily) placing formula into the sheet in one go. This will be muchfaster still (for more than a few data rows)

另一种技术是通过（暂时）一次性将公式放入工作表中来完全避免循环。这将是多快仍然（以上几个数据行）

Sub Demo3()
    Dim ws As Worksheet
    Dim rng As Range
    Dim rw As Range

    ' Get a reference to the source data range
    Set ws = ActiveSheet
    With ws
        Set rng = .Cells(2, 1)
        If Not IsEmpty(rng.Offset(1, 0)) Then
            Set rng = .Range(rng, rng.End(xlDown))
        End If
    End With

    ' Place formulas into sheet
    rng.Offset(0, 11).FormulaR1C1 = "=Min(RC[-9]:RC[-5])"
    rng.Offset(0, 12).FormulaR1C1 = "=Max(RC[-9]:RC[-5])"

    ' replace formulas with values (optional)
    rng.Value = rng.Value
End Sub

回答by Alex P

How about this?

这个怎么样？

Sub MinAndMax()
    Dim rng As Range
    Set rng = Range("A2:A" & Range("A2").End(xlDown).Row)

    Range("L1") = WorksheetFunction.Min(rng)
    Range("M1") = WorksheetFunction.Max(rng)
End Sub

• Define the range upfront
• Write the minand maxto the cells directly

• 预先定义范围
• 将min和max直接写入单元格