Формула для динамического именованного диапазона

Дата: Четверг, 30.01.2020, 12:52 | Сообщение № 1

Группа: Проверенные

Ранг: Ветеран

Сообщений: 931

Репутация: 218 ±

Замечаний: 0% ±

Excel 2007

Доброго дня!
Не могу сам осилить, хелп!
Надоело писать каждый раз формулу для динамического именованного диапазона и попробовал сделать макросом.
Однако, отладчик ругается, что это неправильная формула. Вставлять руками эту формулу или формулу по-проще получается.
Вот формула:

Код

=База!$A$1:ИНДЕКС(База!$C:$C;СЧЁТЗ(База!$A:$A))

а вот код макроса:
[vba]

Код

Sub задать_сводную()
    Dim прав_край As Range, столб_счет As Range, лев_угол As Range
    Dim формула_$, назв_$

    Set прав_край = Application.InputBox("Правый столбец таблицы:", "Запрос данных", "", Type:=8)
    Set столб_счет = Application.InputBox("Столбец для подсчета количества строк:", "Запрос данных", "", Type:=8)
    Set лев_угол = Application.InputBox("Левый угол таблицы:", "Запрос данных", "", Type:=8)
    '    назв_ = InputBox("Укажите значение для поиска:", "Запрос данных", "")
    назв_ = "св_Тест"
    '    On Error Resume Next
    With ActiveSheet
        формула_ = "=" & .Name & "!" & лев_угол.Address & ":ИНДЕКС(" & _
                .Name & "!" & прав_край.Address & ";СЧЁТЗ(" & _
                .Name & "!" & столб_счет.Address & "))"
        ActiveWorkbook.Names.Add Name:=назв_, RefersToLocal:=формула_
    End With
    Stop
End Sub

[/vba]
Кто-нибудь встречался с этим?

К сообщению приложен файл: 6531383.xlsb (17.9 Kb)

"Учиться, учиться и еще раз учиться!"
WM: R399923528092

Сообщение отредактировал pabchek - Четверг, 30.01.2020, 12:52

Дата: Четверг, 30.01.2020, 13:06 | Сообщение № 2

Группа: Друзья

Ранг: Участник клуба

Сообщений: 4115

Репутация: 769 ±

Замечаний: 0% ±

Excel 2013/2016

предположу что запятая перед СЧЁТЗ
[vba]

Код

формула_ = "=" & .Name & "!" & лев_угол.Address & ":ИНДЕКС(" & _
.Name & "!" & прав_край.Address & ",СЧЁТЗ(" & _
.Name & "!" & столб_счет.Address & "))"

[/vba]

но по мне лучше так
[vba]

Код

        формула_ = "=" & .Name & "!" & лев_угол.Address & ":INDEX(" & _
                .Name & "!" & прав_край.Address & ",COUNTA(" & _
                .Name & "!" & столб_счет.Address & "))"
        ActiveWorkbook.Names.Add Name:=назв_, RefersTo:=формула_

[/vba]

~~Замечательный~~ Временно просто медведь ~~, процентов на 20~~.

Сообщение отредактировал bmv98rus - Четверг, 30.01.2020, 13:07

Дата: Четверг, 30.01.2020, 13:17 | Сообщение № 3

Группа: Проверенные

Ранг: Ветеран

Сообщений: 931

Репутация: 218 ±

Замечаний: 0% ±

Excel 2007

Цитата bmv98rus, 30.01.2020 в 13:06, в сообщении № 2 ()

предположу что запятая

не, там точка с запятой должна быть. Если делать ручками, то все нормально с формулой.
Добавил в файл имя с этой формулой. Всё ОК.

К сообщению приложен файл: 6881739.xlsb (18.0 Kb)

"Учиться, учиться и еще раз учиться!"
WM: R399923528092

Сообщение отредактировал pabchek - Четверг, 30.01.2020, 13:19

Дата: Четверг, 30.01.2020, 13:42 | Сообщение № 4

Группа: Друзья

Ранг: Участник клуба

Сообщений: 4115

Репутация: 769 ±

Замечаний: 0% ±

Excel 2013/2016

pabchek, откройте с другими региональными и будет там запятая. Вы второй вариант попробовали?Он более корректный так как будет работать не только в Русском варианте.

~~Замечательный~~ Временно просто медведь ~~, процентов на 20~~.

Дата: Четверг, 30.01.2020, 13:46 | Сообщение № 5

Группа: Проверенные

Ранг: Ветеран

Сообщений: 931

Репутация: 218 ±

Замечаний: 0% ±

Excel 2007

Да, пробовал
[img]

[/img]

"Учиться, учиться и еще раз учиться!"
WM: R399923528092

Дата: Четверг, 30.01.2020, 13:51 | Сообщение № 6

Группа: Друзья

Ранг: Участник клуба

Сообщений: 4115

Репутация: 769 ±

Замечаний: 0% ±

Excel 2013/2016

А так?

К сообщению приложен файл: example1484.xlsb (21.7 Kb)

~~Замечательный~~ Временно просто медведь ~~, процентов на 20~~.

Дата: Четверг, 30.01.2020, 14:05 | Сообщение № 7

Группа: Проверенные

Ранг: Ветеран

Сообщений: 931

Репутация: 218 ±

Замечаний: 0% ±

Excel 2007

А так работает. И в прошлый раз тоже заработало бы, я вставил только первые 3 строки и не обратил внимания, что RefersToLocal надо заменить на RefersTo.
Спасибо большое!
Что ж за засада такая! Ручками точка с запятой, а в коде запятая. Фиг углядишь ))))

"Учиться, учиться и еще раз учиться!"
WM: R399923528092