Ошибка выполнения функции Excel VBA 1004: ошибка приложения или объекта

Question

Я пытаюсь изучить функции с целью упрощения и повторного использования кода при необходимости.

Я начал с того, что превращал то, что часто использую, в функцию: возвращал целочисленное значение последнего непустого столбца в электронной таблице.

Function FindLastDataLine(strColName As String) As Long
    FindLastDataLine = Range(strColName).Offset(Rows.Count - 1, 0).End(xlUp).Row
End Function

Sub PracticeMacro()
    intItemCount = FindLastDataLine("A:A")
    MsgBox ("There are " & intItemCount & " rows of data in column A.")
End Sub

Когда я запускаю это, я получаю ошибку времени выполнения "1004" "Ошибка приложения или объекта", которую Help помогает определить как "чужую ошибку", чтобы процитировать не совсем дословно.

Куда я могу пойти не так?

Answer 1

Почему ваш метод не работает: здесь есть две причины. Первое, когда вы кладете Rows.Count , что для Rows нет ссылки - это свойство Range . Чтобы исправить это, вам просто нужно сослаться на тот же диапазон, который вы уже используете (просто добавьте его перед вызовом Rows.Count так:

Function FindLastDataLine(strColName As String) As Long
    FindLastDataLine = Range(strColName).Offset(Range(strColName).Rows.Count - 1, 0).End(xlUp).Row
End Function

Вторая причина в том, что вы используете Offset . Offset буквально сдвигает Range , насколько вы говорите. Вы не хотите сдвигать весь диапазон ячеек, но найдите последнюю ячейку в этом диапазоне. Вы можете сделать это довольно просто, изменив Offset на Cells , удалив начальный вызов Range() (так как мы собираемся выбрать одну ячейку), и изменив 0 на нужный вам столбец. Однако, поскольку вы передаете столбец как "A:A" , это невозможно, поэтому вам придется заменить его на Range(strColName).Column следующим образом:

Function FindLastDataLine(strColName As String) As Long
    FindLastDataLine = Cells(Range(strColName).Rows.Count, Range(strColName).Column).End(xlUp).Row
End Function

---

Лучшее решение: следующее решение будет работать на всех последних версиях MS Office (2003, 2007 и 2010) и обрабатывать ошибки. Вы вызываете это, передавая либо букву столбца, либо номер столбца:

Function GetLastDataRow(col As Variant) As Long
    GetLastDataRow = -1
    If (IsNumeric(col) And col >= 1) Or Len(col) <= 2 Then
        On Error Resume Next
        GetLastDataRow = _
            Cells(Cells(1, col).EntireColumn.Rows.Count, col).End(xlUp).Row
        On Error GoTo 0
    End If
End Function

Ниже показано, как вы будете вызывать эту функцию, и некоторые примеры выходных данных. Предположим, что весь лист чист, за исключением некоторых случайных данных, введенных в ячейки с B1 по B8 и B10 (B9 оставлено пустым). Обратите внимание, что вы не вводите столбец как диапазон, а скорее букву столбца или номер столбца (недопустимые значения возвращают -1):

GetLastDataRow(1)         =  1    GetLastDataRow("A")       =  1
GetLastDataRow(2)         = 10    GetLastDataRow("B")       = 10
GetLastDataRow("AX")      =  1    GetLastDataRow("A:X")     = -1
GetLastDataRow("Oops...") = -1    GetLastDataRow(200)       =  1

В качестве технического примечания, если метод Cells завершается ошибкой, предполагается, что ввод был неверным, поэтому функция возвращает -1. Я призываю вас использовать эту практику (возвращать неверные значения, если ввод был неверным) в вашей функции, это очень поможет вам избежать ошибок в будущем.

Как это работает, он находит последнюю возможную строку в любом конкретном столбце (зависит от вашей версии MS Office), а затем использует метод End чтобы найти последнюю ячейку в этом столбце с данными.

---

Вот альтернативная версия, которая будет возвращать 0, если все ячейки в этом столбце пустые:

Function GetLastDataRow(col As Variant) As Long
    GetLastDataRow = -1
    If (IsNumeric(col) And col >= 1) Or Len(col) <= 2 Then
        On Error Resume Next
        If IsEmpty(Cells(Cells(1, col).EntireColumn.Rows.Count, col).End(xlUp).Value) Then
            GetLastDataRow = 0
        Else
            GetLastDataRow = _
                Cells(Cells(1, col).EntireColumn.Rows.Count, col).End(xlUp).Row
        End If
        On Error GoTo 0
    End If
End Function

Пример вывода:

GetLastDataRow(1)         =  0    GetLastDataRow("A")       =  0
GetLastDataRow(2)         = 10    GetLastDataRow("B")       = 10
GetLastDataRow("AX")      =  0    GetLastDataRow("A:X")     = -1
GetLastDataRow("Oops...") = -1    GetLastDataRow(200)       =  0

Answer 2

Вы написали «Возвращение целочисленного значения последней непустой строки в электронной таблице». Но похоже, что вы пытаетесь получить последнюю непустую строку в определенном столбце электронной таблицы.

Если вы измените свою функцию на это, я думаю, что это будет работать:

Function FindLastDataLine(strColName As String) As Long
    FindLastDataLine = ActiveSheet.Cells(ActiveSheet.Rows.Count, strColName).End(xlUp).Row
End Function

Answer 3

Это может не исправить вашу ошибку, но вернет ItemCount в пределах диапазона ...

Function FindLastDataLine(strColName As String) As Long
    FindLastDataLine = Evaluate("COUNTA(" & strColName & ")")
End Function

Sub PracticeMacro()
    intItemCount = FindLastDataLine("A:A")
    MsgBox ("There are " & intItemCount & " rows of data in column A.")
End Sub

Обратите внимание, что есть несколько других способов вызвать эту функцию COUNTA Excel ...

Answer 4

Пытаться:

FindLastDataLine = _
     Range(strColName).Offset(ActiveSheet.Rows.Count - 1, 0).End(xlUp).Row

Ряды это собственность.