Всем привет! Имею много одинаковых таблиц в книгах, выполненных по одному шаблону, со временем приходиться редактировать формулы в данных книгах. А книг очень много, при том в каждой книге 21 формула и каждый раз при изменении части формулы приходиться менять их во всех файлах. Для удобства создал "книгу формул" в которой меняю формулу, а потом заменяю в книгах. Возможно автоматизировать этот процесс? Думал про макроредактор, возможно есть вариант попроще?
Каждая книга заполняется инженером по своему шифру объекта, а форма для всех одна, таких журналов у меня уже 15, а будет ещё больше. А формулы я частенько меняю, по причине того, что приходиться добавлять новые условия особенно в ячейках столбцов с BJ по BU, в примере я выложил только формулы, второй лист куда они ссылаются я очистил, что бы не загружать вас лишней информацией. Все ячейки с формулами подкрасил зелёным цветом
В продолжение разговора, точнее монолога). Решение почти нашёл. Через PQ я выгружаю формулы с шаблона, но без знака "=". Получаю так сказать текстовую часть формулы, но вот как преобразовать её в полноценную формулу не получается, пробовал через сцепить не выходит, приложил простой пример.
Спасибо всем! vikttur, сделал проще, формулу сцепил в PQ с "=", но при выгрузке получаю текстовое значение, и приходиться каждую ячейку перещёлкивать мышкой, или менять "=" на "=", тогда превращается в нормальную рабочую формулу
artyrH, да я так и сделал, но опять маленькая проблема, формулы в моём случае не протягиваются, но тут думаю много "гемороя", проще записать макроредактором автопротягивание да и с формулой массива не получается ввести данные... По первому, возможно склеенную формулу запихать в диспетчер имен? По второму я думаю получится ввести формулу кодом через "FormulaArray"
adamm, правильно ли я понял 1. есть таблица с соответствием поля и формулы ,с примечанием, массивная или нет 2. требуется автоматизировать замену формул в другой книге в советующих полях таблиц
Ну то что замену делать макросом - это понятно, и не так сложно. Получить таблицу соответсвий, пробежаться по полям и заменить формулы, не великая печаль. Но 1. вы используете RUS и в случае другой локализации офиса ваш вариант не сработает совсем 2. предположим локализация идентична, тогда надо вставить вставить текст формулу с префиксом = в Range.FormulaLocal и потом сразу Range.FormulaArray=RangeFormula, если формула массивная. заполнить на нужное количество.
2. Далее для каждой формулы, прописал код для копирования в имена, да немного топорно, но лучше чем ручками каждый раз вставлять
Код
Sub Всего_замечаний()
Sheets("Основные линии").Range("BH3").Formula = Sheets("Справочно").Range("AR2").Formula
On Error Resume Next
ThisWorkbook.Worksheets("Основные линии").Names("Всего_замечаний").Delete
On Error GoTo 0
ThisWorkbook.Worksheets("Основные линии").Names.Add Name:="Всего_замечаний", RefersToR1C1:=Sheets("Основные линии").Range("BH3").FormulaR1C1
Sheets("Основные линии").Range("BH3").Formula = "=Всего_замечаний"
End Sub
БМВ, Ну по сути, так и есть, 2 код это только одна формула, на каждую формулу свой код, для примера я выложил только одну формулу. Далее я наверное его объединю убрав End Sub и Sub в промежутках.
adamm написал: Далее я наверное его объединю убрав End Sub и Sub в промежутках.
Вот этого то и опасаюсь :-) Если не передавать объекты, то вот так имел в виду
Код
Sub XXX(ByRef strSourceSheetName As String, ByRef strSourceRange As String, ByRef strDestSheetname As String, ByRef strDestRange As String, ByRef strName As String)
With ThisWorkbook.Worksheets(strDestSheetname)
.Range(strDestRange).Formula = Sheets(strSourceSheetName).Range(strSourceRange).Formula
On Error Resume Next
.Names(strName).Delete
On Error GoTo 0
.Names.Add Name:=strName, RefersToR1C1:=.Range(strDestRange).FormulaR1C1
.Range(strDestRange).Formula = "=" & strName
End With
End Sub
Sub Всего_замечаний()
Call XXX("Справочно", "AR2", "Основные линии", "BH3", "Всего_замечаний")
End Sub
Уже пробовал, все работает! По сути код в дальнейшем можно упростить, до
Код
Sub Всего_замечаний()
ThisWorkbook.Worksheets("Основные линии").Names.Add Name:="Всего_замечаний", RefersToR1C1:=Sheets("Справочно").Range("AR2").FormulaR1C1
End Sub
Так как имена уже будут созданы, их можно просто менять, при необходимости
БМВ, А уточните пожалуйста, чем код в #22 посте отличается? Но опять же если я уже ввел в диспетчер имен имя, в дальнейшем мне его нужно только заменять (саму формулу), ведь на листе оно уже будет существовать, как имя, или я что то ппутаю? ДА в идеале бы конечно, код упростить до возможности только создать в диспетчер имя на основании данных с листа "справочно"
только тем что подпрограмму потом нужно только вызвать , с параметрами, и если нужно что-то в ней изменить, как в случае с оптимизации, то меняется в одном месте, а не в куче. Использование With не принципиально, но рекомендуемо.
Спасибо! теперь вроде как понятно. На просторах нарыл вот такое решение https://excelvba.ru/code/NamesList, вот только жалко, что нет обратного решения)
adamm,У Игоря хорошие решения, просто это для другой цели, ну а обратное не проблема, только основной момент - это подготовка того что нужно загрузить в имена, чтоб все ссылки на ячейки не поехали. По идее R1C1 стиль это решает. Более того, в одной из тем, о том что пробежаться по списку и создать я писал, и главное совершенно не нужно делить на массивные и нет, просто в имена запихнули, в ячейке результат.
Оставил пока все как есть, вроде все работает, в результате имею 78 имен в диспетчере), три общих кода для трех листов, и один запрос, с подключением к шаблону