Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Массовое редактирование формул в именованных диапазонах
 
#1
25.09.2017 23:59:04
Всем привет!

Прошерстил приемы и поиск по темам по запросу "именован". Не нашел того, что хотел. Суть проблемы.

В книге используется несколько сотен именованных диапазонов похожих друг на друга (формула везде одна и та же, отличаются только названия листов, на которых эта формула подбирает диапазон). И во всех есть одна проблема - ссылка на другую книгу:

=OFFSET(Sheet1!$H$182,,,,VLOOKUP(Sheet1!$E$5,'C:\Users\username\Documents\[file.xlsx]Sheet1'!$I$2:$J$13,2,)*2+2);Sheet1!$AH$182

Вопрос. Как быстро во всех диапазонах убрать отсылку к другой книге? Я понимаю, что, скорее всего, это сделать можно только макросом. С макросами сталкивался очень поверхностно, но очень хочу научиться. Код VBA видел и даже писал на нем макросы, интуитивно понимая что надо подправить и выполняя пошаговую отладку. Осознанным кодом это назвать было нельзя. Я сначала в визарде создавал макрос, а потом смотрел, как он реализовался в виде кода, и уже этот код правил, там где видел нестыковки. У меня математическое образование и с алгоритмами я знаком. Так что интуитивно там было понятно что к чему. Попробовал и в этом случае так сделать, но не получилось. Пожалуйста, помогите с кодом.

P.S. Я за третий день создаю третью тему. Тут так принято? Один вопрос - одна тема?
 
 
 
#2
26.09.2017 00:45:12
образование - ПТУ, с алгоритмами не знаком, решал бы так:
Код
Sub ReplaceInNames()
  Dim n
  For Each n In Names
    n.RefersTo = Replace(n.RefersTo, "что заменить", "на что заменить")
  Next
End Sub

при корректно указанных "что заменить" и "на что заменить" - все должно сработать

Программисты - это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете!
 
 
 
#3
26.09.2017 11:29:16
При попытке запуска кода выдает ошибку 1004.
 
 
 
#4
26.09.2017 11:34:18
colonel, а что Вы и как ввели  в строке
Код
n.RefersTo = Replace(n.RefersTo, "что заменить", "на что заменить")
 
 
 
#5
26.09.2017 14:10:11
 n.RefersTo = Replace(n.RefersTo, "C:\Users\username\Documents\[file.xlsx]", "").

Формула обращается к правильным ячейкам. Просто в другой книге. Вот упоминание про эту книгу надо убрать.
 
 
 
#6
26.09.2017 15:22:36
Попробуйте так, но 2 книга должна быть открыта
Код
n.RefersTo = Replace(n.RefersTo, "C:\Users\username\Documents\", "")
Изменено: Илья Демид - 26.09.2017 15:24:19
 
 
 
#7
26.09.2017 15:23:05
после замены формула в n.RefersTo должна оставаться корректной по синтаксису и вычисляемой! (не возвращать какой-то из ошибок)
если в формуле было ПОИСКПОЗ("ЧТО-то";во_внешнем_файле_В1:В100;)
то ПОИСКПОЗ("ЧТО-то";В1:В100;) уже может вернуть #Н/Д потому что этого ЧТО-то не в диапазоне В1:В100 в этой книге, а в той, ссылку на которую Вы только что снесли - было и все работадло нормально
Программисты - это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете!
 
 
 
#8
26.09.2017 19:37:55
Нет. Это исключено. Книги совершенно идентичные. Диапазоны тоже.
 
 
 
#9
26.09.2017 19:47:47
А апострофы остались? Их бы тоже Replace.
А вообще файл-пример не помешает
Согласие есть продукт при полном непротивлении сторон
 
 
 
#10
26.09.2017 23:28:35
Цитата
colonel написал:
формула везде одна и та же, отличаются только названия листов, на которых эта формула подбирает диапазон
А эта исходная формула полностью приведена в первом посте? Как-то она странно выглядит в плане вот этого
довеска ";Sheet1!$AH$182" в конце.  
 
 
 
#11
27.09.2017 11:00:32
Полностью приведена. Формула определяет диапазон как набор ячеек, заданный через формулу OFFSET, плюс еще одна ячейка Sheet1!$AH$182. Так можно и это работает.
 
 
 
#12
27.09.2017 17:09:39
Смущает точка с запятой вместо запятой.

Вручную получается удалить название книги?
Изменено: Equio - 27.09.2017 17:25:58
 
 
 
#13
27.09.2017 18:37:48
Я использую в качестве разделителя листов не запятую, а точку с запятой. Это выставлено в региональных параметрах. Я так привык.

Собственно, я потратил время и поменял все вручную. Кейс закрыт.

Вопрос по диапазонам в рамках этой темы следующий. У меня есть лист, на котором графики используют упомянутые именованные диапазоны (диапазоны созданы на уровне книги). Мне нужно скопировать этот лист в эту же книгу, так, чтобы у меня получилось два листа (с разными именами, конечно), но одинаковым функционалом. Так вот когда я копирую лист, Excel предлагает мне создать новые именованные диапазоны для создаваемого листа. Я их создаю Но вот графики на новом листе обращаются уже не к новосозданным именованным диапазонам, а к обычным массивам ячеек. Как решить проблему?

P.S. Нужен ли файл?
Изменено: colonel - 27.09.2017 18:38:19
 
 
 
#14
27.09.2017 18:43:08
Файл нужен. А, вижу, проблему вручную решили.
Изменено: Влад - 27.09.2017 18:44:54
 
 
 
#15
27.09.2017 19:27:35
Боюсь, что файлом не отделаетесь. Тут всё серьёзнее. Дело пахнет новой темой!
 
 
 
#16
27.09.2017 22:57:32
Дабы не плодить тем, давайте пока попробуем в этой.

Файлик прикладываю. График ссылается на именованные диапазоны, длина которых зависит от месяца. Хотелось бы как-то сделать, чтоб при копировании листа в эту же книгу на новом листе создавался такой же набор диапазонов и чтоб график был к ним привязан автоматически.

P.S. Оч странно. На работе, когда я копировал листы, Excel говорил, что такой диапазон уже существует и не хочу ли я создать копию. В файле примере он просто копирует лист и создает два новых диапазона ни о чем не спрашивая. Но график на новом листе все равно отвязан от них - просто массив ячеек в исходных данных диаграммы.
 
 
 
#17
29.09.2017 11:27:33
Друзья! Есть какие-то новости?
 
 
 
Страницы: 1
Наверх