Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/Формула для динамического именованного диапазона - Мир MS Excel

Регистрация · Логин: · Пароль: · · Забыли пароль?
  • Страница 1 из 1
  • 1
Модератор форума: _Boroda_, Manyasha, SLAVICK, китин  
Мир MS Excel » Вопросы и решения » Вопросы по VBA » Формула для динамического именованного диапазона (Макросы/Sub)
Формула для динамического именованного диапазона
pabchek Дата: Четверг, 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
 
Ответить
СообщениеДоброго дня!
Не могу сам осилить, хелп!
Надоело писать каждый раз формулу для динамического именованного диапазона и попробовал сделать макросом.
Однако, отладчик ругается, что это неправильная формула. Вставлять руками эту формулу или формулу по-проще получается.
Вот формула:
Код
=База!$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]
Кто-нибудь встречался с этим?

Автор - pabchek
Дата добавления - 30.01.2020 в 12:52
bmv98rus Дата: Четверг, 30.01.2020, 13:06 | Сообщение № 2
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3182
Репутация: 546 ±
Замечаний: 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
 
Ответить
Сообщениепредположу что запятая перед СЧЁТЗ
[vba]
Код
формула_ = "=" & .Name & "!" & лев_угол.Address & ":ИНДЕКС(" & _
                .Name & "!" & прав_край.Address & ",СЧЁТЗ(" & _
                .Name & "!" & столб_счет.Address & "))"
[/vba]

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

Автор - bmv98rus
Дата добавления - 30.01.2020 в 13:06
pabchek Дата: Четверг, 30.01.2020, 13:17 | Сообщение № 3
Группа: Проверенные
Ранг: Ветеран
Сообщений: 931
Репутация: 218 ±
Замечаний: 0% ±

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

не, там точка с запятой должна быть. Если делать ручками, то все нормально с формулой.
Добавил в файл имя с этой формулой. Всё ОК.
К сообщению приложен файл: 6881739.xlsb(18.0 Kb)


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


Сообщение отредактировал pabchek - Четверг, 30.01.2020, 13:19
 
Ответить
Сообщение
предположу что запятая

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

Автор - pabchek
Дата добавления - 30.01.2020 в 13:17
bmv98rus Дата: Четверг, 30.01.2020, 13:42 | Сообщение № 4
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3182
Репутация: 546 ±
Замечаний: 0% ±

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


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

Автор - bmv98rus
Дата добавления - 30.01.2020 в 13:42
pabchek Дата: Четверг, 30.01.2020, 13:46 | Сообщение № 5
Группа: Проверенные
Ранг: Ветеран
Сообщений: 931
Репутация: 218 ±
Замечаний: 0% ±

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


"Учиться, учиться и еще раз учиться!"
WM: R399923528092
 
Ответить
СообщениеДа, пробовал
[img][/img]

Автор - pabchek
Дата добавления - 30.01.2020 в 13:46
bmv98rus Дата: Четверг, 30.01.2020, 13:51 | Сообщение № 6
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3182
Репутация: 546 ±
Замечаний: 0% ±

Excel 2013/2016
А так?
К сообщению приложен файл: example1484.xlsb(21.7 Kb)


Замечательный Временно просто медведь , процентов на 20.
 
Ответить
СообщениеА так?

Автор - bmv98rus
Дата добавления - 30.01.2020 в 13:51
pabchek Дата: Четверг, 30.01.2020, 14:05 | Сообщение № 7
Группа: Проверенные
Ранг: Ветеран
Сообщений: 931
Репутация: 218 ±
Замечаний: 0% ±

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


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

Автор - pabchek
Дата добавления - 30.01.2020 в 14:05
bmv98rus Дата: Четверг, 30.01.2020, 14:09 | Сообщение № 8
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3182
Репутация: 546 ±
Замечаний: 0% ±

Excel 2013/2016
А что в там в RefersToLocal из #3 , если посмотреть из VBA?
По факту , всегда запятая, так как это разделитель по умолчанию, также как и точка - десятичный разделитель в VBA, не зависимо от локализации.


Замечательный Временно просто медведь , процентов на 20.
 
Ответить
СообщениеА что в там в RefersToLocal из #3 , если посмотреть из VBA?
По факту , всегда запятая, так как это разделитель по умолчанию, также как и точка - десятичный разделитель в VBA, не зависимо от локализации.

Автор - bmv98rus
Дата добавления - 30.01.2020 в 14:09
pabchek Дата: Четверг, 30.01.2020, 14:25 | Сообщение № 9
Группа: Проверенные
Ранг: Ветеран
Сообщений: 931
Репутация: 218 ±
Замечаний: 0% ±

Excel 2007
так как это разделитель по умолчанию

Да, конечно. Просто в диспетчере имен эта запятая превращается в точку с запятой. Это я забыл/плохо знал, потому так и получилось.

А про
А что в там в RefersToLocal из #3
не понял вопрос?


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


Сообщение отредактировал pabchek - Четверг, 30.01.2020, 14:26
 
Ответить
Сообщение
так как это разделитель по умолчанию

Да, конечно. Просто в диспетчере имен эта запятая превращается в точку с запятой. Это я забыл/плохо знал, потому так и получилось.

А про
А что в там в RefersToLocal из #3
не понял вопрос?

Автор - pabchek
Дата добавления - 30.01.2020 в 14:25
Мир MS Excel » Вопросы и решения » Вопросы по VBA » Формула для динамического именованного диапазона (Макросы/Sub)
  • Страница 1 из 1
  • 1
Поиск:

Яндекс цитирования
© 2010-2020 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!