SpecialCells(xlLastCell) в пользовательских функциях

[tolikt]

Нужна функция для нахождения номера последней заполненной строки на соседнем листе.

Например, такая пользовательская функция:

Код: Выделить всё

Function AnotherSheetLastCell(CallerRange As Range)
AnotherSheetLastCell = CallerRange.SpecialCells(xlLastCell).Row
End Function

Однако функция почему-то возвращает номер строки ячейки, взятой в качестве аргумента функции.
Из хелпа про SpecialCells Method:
expression.SpecialCells(Type, Value)
expression Required. An expression that returns a Range object
Или тут к функции Range object не применим?

И пока приходиться обходиться более изощрёнными методами.

[bi-lya]

Я думаю, что-то такое должно быть

Код: Выделить всё

Function AnotherSheetLastCell(CallerRange)
  AnotherSheetLastCell = Worksheets(CallerRange).Range("A1").SpecialCells(xlLastCell).Row
End Function

где CallerRange - или номер или имя нужного листа

[tolikt]

Не, совсем не то.
Мой вариант хоть совсем неправильно, но работает. А вариант от bi-lya совсем не работает, что естественно, т.к. простая ошибка в коде.
Дело именно в SpecialCells(xlLastCell), а не грамотном указании имени или номера листа.

[bi-lya]

Если вы под подразумеваете функцию для ввода в ячейку, то она и не будет работать - в книжках пишут.
А насчет ошибки в коде - зря...

[tolikt]

Если вы под подразумеваете функцию для ввода в ячейку, то она и не будет работать - в книжках пишут.
А насчет ошибки в коде - зря...

1. Ну неужели не видно, что данная функция, содержащая только одну строку, НИЧЕГО НЕ МЕНЯЕТ НИ В КАКИХ ЯЧЕЙКАХ??? Просто возвращает номер строки. Кажется, уж куда уж конкретней - ЗРИ В КОРЕНЬ (т. е. смотри в код)

2. Передёрнув правильную строку в моём коде Function AnotherSheetLastCell(CallerRange As Range) на другую свою Function AnotherSheetLastCell(CallerRange) не надо меня же обвинять в том, чего нет. Ну чётко же написано в моём случае, что CallerRange это Range, а не имя листа или что-то другое.

Вопрос был о применимости SpecialCells(xlLastCell) в функциях. Поэтому прошу не отвечать не по существу.

ЗЫ. Метод Find и иже с ними тоже не работают!
PS. А есть ли список методов и функций, которые работают в процедурах Sub, но не работают в пользовательских функциях Function? Естественно, кроме тех, которые что-либо меняют в других ячейках.

[Serge_Bliznykov]

tolikt, не надо так нервничать... :-)))
вот так пойдёт?

Код: Выделить всё

Function AnotherSheetLastCell()
  AnotherSheetLastCell = ActiveSheet.UsedRange.Rows.Count
End Function

[Aent]

Serge_Bliznykov, и где же в приведённом вами коде ссылка на параметр (ранг на другом (неактивном) листе ? Как тут не занервничать

[Serge_Bliznykov]

и где же в приведённом вами коде ссылка на параметр (ранг на другом (неактивном) листе

упс. Был неправ. вспылил...
пропустил в постановке задачи "на соседнем листе"...
а так пойдёт?

Код: Выделить всё

Function AnotherSheetLastCell(RR As Range)
  AnotherSheetLastCell = RR.Worksheet.UsedRange.Row + RR.Worksheet.UsedRange.Rows.Count - 1
End Function

и чё нервничать то?... ;-)))

[Aent]

Serge_Bliznykov, тут не всё так просто.
Такая функция не будет пересчитываться при изменении другого листа на котором расположен ранг RR. Не будет меняться значение и при принудительном
пересчёте (F9). Для правильной работы надо добавить в обработчик событий листа
на который ссылается ранг что то вроде

Код: Выделить всё

Private Sub Worksheet_Change(ByVal Target As Range)
    With ThisWorkbook.Worksheets(1) 'лист с формулой
      .[A1] = .[A1]
    End With
End Sub

Хотя это всё теоретические изыски. На практике как подсказывает моя практика
подобные проблемы возникают из за неправильной постановки первоначальной
задачи и неверного выбора средств для её решения

[Serge_Bliznykov]

Хотя это всё теоретические изыски. На практике как подсказывает моя практика
подобные проблемы возникают из за неправильной постановки первоначальной
задачи и неверного выбора средств для её решения.

Золотые слова!! Готов подписаться под каждым словом.