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

Вход

Регистрация

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

 

= Мир MS Excel/Замена выражения в формуле - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по VBA » Замена выражения в формуле (Макросы/Sub)
Замена выражения в формуле
eenk Дата: Понедельник, 13.03.2017, 15:24 | Сообщение № 1
Группа: Пользователи
Ранг: Новичок
Сообщений: 10
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
Добрый день.
Прошу помощи, никак не получается решить такую задачу:
В формулах, при удалении строк, естественно, появляются ошибки вида:
=СРЗНАЧ(L64/$D64;L66/$D66;L68/$D68;L70/$D70;L72/$D72;L74/$D74;L76/$D76;L78/$D78;#ССЫЛКА!/#ССЫЛКА!;L80/$D80)
Таких формул множество, по всей строке. средствами Excel можно заменить выражение [;#ССЫЛКА!/#ССЫЛКА!] на [""] и все нормально. Как сделать это при помощи макроса?

Сначала записал действие, получилось так:
[vba]
Код
Selection.Replace What:=";#ССЫЛКА!/#ССЫЛКА!", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
[/vba]
Макрос выполняется без вопросов, но ничего не заменяет.

Попытался использовать аналогичную команду с перебором ячеек
[vba]
Код
With Application
For Each cell In Selection
    cell.FormulaR1C1 = Replace(cell.FormulaR1C1, ",#REF!/#REF!", "")
Next cell
End With
[/vba]
(надо заменить ССЫЛКА на REF, ибо так видит VBA формулу)
Но данным путем неудобно, потому что
1. Долго;
2. приходится выделять диапазон, тогда как при замене можно выделить просто строку, в противном случае цикл затянется на очень много времени

попытался, даже, построить совсем корявую конструкцию:
[vba]
Код
Dim ff As Range
Set ff = Range(Cells(Selection.Row, Selection.Column), Cells(Selection.Row + Selection.Rows.Count - 1, Selection.Columns.Count - 1 + Selection.Column))

ff.Replace What:=";#ССЫЛКА!/#ССЫЛКА!", Replacement:="", LookAt:= xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
a = ff.Replace(";#ССЫЛКА!/#ССЫЛКА!", "", xlPart, xlByRows, False, False, False)
ff.Replace What:=";#REF!/#REF!", Replacement:="", LookAt:= xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
a = ff.Replace(";#REF!/#REF!", "", xlPart, xlByRows, False, False, False)
[/vba]

все равно - ничего не заменяется!


Сообщение отредактировал eenk - Понедельник, 13.03.2017, 15:35
 
Ответить
СообщениеДобрый день.
Прошу помощи, никак не получается решить такую задачу:
В формулах, при удалении строк, естественно, появляются ошибки вида:
=СРЗНАЧ(L64/$D64;L66/$D66;L68/$D68;L70/$D70;L72/$D72;L74/$D74;L76/$D76;L78/$D78;#ССЫЛКА!/#ССЫЛКА!;L80/$D80)
Таких формул множество, по всей строке. средствами Excel можно заменить выражение [;#ССЫЛКА!/#ССЫЛКА!] на [""] и все нормально. Как сделать это при помощи макроса?

Сначала записал действие, получилось так:
[vba]
Код
Selection.Replace What:=";#ССЫЛКА!/#ССЫЛКА!", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
[/vba]
Макрос выполняется без вопросов, но ничего не заменяет.

Попытался использовать аналогичную команду с перебором ячеек
[vba]
Код
With Application
For Each cell In Selection
    cell.FormulaR1C1 = Replace(cell.FormulaR1C1, ",#REF!/#REF!", "")
Next cell
End With
[/vba]
(надо заменить ССЫЛКА на REF, ибо так видит VBA формулу)
Но данным путем неудобно, потому что
1. Долго;
2. приходится выделять диапазон, тогда как при замене можно выделить просто строку, в противном случае цикл затянется на очень много времени

попытался, даже, построить совсем корявую конструкцию:
[vba]
Код
Dim ff As Range
Set ff = Range(Cells(Selection.Row, Selection.Column), Cells(Selection.Row + Selection.Rows.Count - 1, Selection.Columns.Count - 1 + Selection.Column))

ff.Replace What:=";#ССЫЛКА!/#ССЫЛКА!", Replacement:="", LookAt:= xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
a = ff.Replace(";#ССЫЛКА!/#ССЫЛКА!", "", xlPart, xlByRows, False, False, False)
ff.Replace What:=";#REF!/#REF!", Replacement:="", LookAt:= xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
a = ff.Replace(";#REF!/#REF!", "", xlPart, xlByRows, False, False, False)
[/vba]

все равно - ничего не заменяется!
Автор - eenk
Дата добавления - 13.03.2017 в 15:24
китин Дата: Понедельник, 13.03.2017, 15:28 | Сообщение № 2
Группа: Модераторы
Ранг: Экселист
Сообщений: 7019
Репутация: 1074 ±
Замечаний: 0% ±

Excel 2007;2010;2016
файл?

Не судите очень строго:я пытаюсь научиться
ЯД 41001877306852
 
Ответить
Сообщениефайл?
Автор - китин
Дата добавления - 13.03.2017 в 15:28
Wasilich Дата: Понедельник, 13.03.2017, 15:35 | Сообщение № 3
Группа: Друзья
Ранг: Старожил
Сообщений: 1232
Репутация: 326 ±
Замечаний: 0% ±

2003
Код VBA оформите тегом VBA, значек #.
 
Ответить
СообщениеКод VBA оформите тегом VBA, значек #.
Автор - Wasilich
Дата добавления - 13.03.2017 в 15:35
eenk Дата: Понедельник, 13.03.2017, 15:54 | Сообщение № 4
Группа: Пользователи
Ранг: Новичок
Сообщений: 10
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
вот файл
Если удалить, например, строку 6, в строке Всего в формулах, будет ССЫЛКА.
К сообщению приложен файл: 9316181.xlsx (9.8 Kb)
 
Ответить
Сообщениевот файл
Если удалить, например, строку 6, в строке Всего в формулах, будет ССЫЛКА.
Автор - eenk
Дата добавления - 13.03.2017 в 15:54
eenk Дата: Понедельник, 13.03.2017, 15:55 | Сообщение № 5
Группа: Пользователи
Ранг: Новичок
Сообщений: 10
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
Wasilich, оформил
 
Ответить
СообщениеWasilich, оформил
Автор - eenk
Дата добавления - 13.03.2017 в 15:55
китин Дата: Понедельник, 13.03.2017, 16:01 | Сообщение № 6
Группа: Модераторы
Ранг: Экселист
Сообщений: 7019
Репутация: 1074 ±
Замечаний: 0% ±

Excel 2007;2010;2016
eenk, зачем такие сложности? формула массива
Код
=СРЗНАЧ(C$4:C$13/$B$4:$B$13)
и удаляйте что хотите :D
К сообщению приложен файл: _9316181.xlsx (9.9 Kb)

Не судите очень строго:я пытаюсь научиться
ЯД 41001877306852

Сообщение отредактировал китин - Понедельник, 13.03.2017, 16:02
 
Ответить
Сообщениеeenk, зачем такие сложности? формула массива
Код
=СРЗНАЧ(C$4:C$13/$B$4:$B$13)
и удаляйте что хотите :D
Автор - китин
Дата добавления - 13.03.2017 в 16:01
_Boroda_ Дата: Понедельник, 13.03.2017, 16:05 | Сообщение № 7
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16675
Репутация: 6481 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Попробуйте формулу массива (вводится одновременным нажатием Контрл Шифт Ентер)
Код
=СРЗНАЧ(C4:C13/$B4:$B13)

Или немассивной формулой
Код
=СУММПРОИЗВ(C4:C13/$B4:$B13)/СЧЁТ(C4:C13)

Или в общем случае (можно добавлять данные)
Код
=СРЗНАЧ(ИНДЕКС(C:C;4):ИНДЕКС(C:C;СЧЁТЗ($A:$A)+3-СЧЁТЗ($A1:$A3))/ИНДЕКС($B:$B;4):ИНДЕКС($B:$B;СЧЁТЗ($A:$A)+3-СЧЁТЗ($A1:$A3)))
К сообщению приложен файл: 9316181_1.xlsx (10.8 Kb)

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеПопробуйте формулу массива (вводится одновременным нажатием Контрл Шифт Ентер)
Код
=СРЗНАЧ(C4:C13/$B4:$B13)

Или немассивной формулой
Код
=СУММПРОИЗВ(C4:C13/$B4:$B13)/СЧЁТ(C4:C13)

Или в общем случае (можно добавлять данные)
Код
=СРЗНАЧ(ИНДЕКС(C:C;4):ИНДЕКС(C:C;СЧЁТЗ($A:$A)+3-СЧЁТЗ($A1:$A3))/ИНДЕКС($B:$B;4):ИНДЕКС($B:$B;СЧЁТЗ($A:$A)+3-СЧЁТЗ($A1:$A3)))
Автор - _Boroda_
Дата добавления - 13.03.2017 в 16:05
eenk Дата: Понедельник, 13.03.2017, 16:17 | Сообщение № 8
Группа: Пользователи
Ранг: Новичок
Сообщений: 10
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
китин, спасибо, но не подходит. Тут очень упрощенный пример. Если вставить шапки между строк, уже не работает формула массива.
И, самое страшное, что у меня таких файлов не один десяток. Сейчас заменять все формулы во всех файлах нереально, тысячи их, без преувеличения говорю.
К сообщению приложен файл: 3165138.xlsx (10.0 Kb)
 
Ответить
Сообщениекитин, спасибо, но не подходит. Тут очень упрощенный пример. Если вставить шапки между строк, уже не работает формула массива.
И, самое страшное, что у меня таких файлов не один десяток. Сейчас заменять все формулы во всех файлах нереально, тысячи их, без преувеличения говорю.
Автор - eenk
Дата добавления - 13.03.2017 в 16:17
eenk Дата: Понедельник, 13.03.2017, 16:20 | Сообщение № 9
Группа: Пользователи
Ранг: Новичок
Сообщений: 10
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
_Boroda_, китин, спасибо. Я бы хотел, все таки, узнать, как можно использовать Replace.
 
Ответить
Сообщение_Boroda_, китин, спасибо. Я бы хотел, все таки, узнать, как можно использовать Replace.
Автор - eenk
Дата добавления - 13.03.2017 в 16:20
eenk Дата: Понедельник, 13.03.2017, 16:49 | Сообщение № 10
Группа: Пользователи
Ранг: Новичок
Сообщений: 10
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
Внезапно!
Практически случайно, при подготовке файла примера с макросами, выявил, как надо правильно делать:
при выполнении замены, макрорекордер записывает такую конструкцию:
[vba]
Код
Selection.Replace What:=";#ССЫЛКА!/#ССЫЛКА!", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
[/vba]
вручную, необходимо ее изменить так:
[vba]
Код
Selection.Replace What:=",#REF!/#REF!", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
[/vba]
точку с запятой - на запятую
ССЫЛКА на REF
!


Сообщение отредактировал eenk - Понедельник, 13.03.2017, 16:50
 
Ответить
СообщениеВнезапно!
Практически случайно, при подготовке файла примера с макросами, выявил, как надо правильно делать:
при выполнении замены, макрорекордер записывает такую конструкцию:
[vba]
Код
Selection.Replace What:=";#ССЫЛКА!/#ССЫЛКА!", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
[/vba]
вручную, необходимо ее изменить так:
[vba]
Код
Selection.Replace What:=",#REF!/#REF!", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
[/vba]
точку с запятой - на запятую
ССЫЛКА на REF
!
Автор - eenk
Дата добавления - 13.03.2017 в 16:49
_Boroda_ Дата: Понедельник, 13.03.2017, 17:00 | Сообщение № 11
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16675
Репутация: 6481 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Еще такой вариант у меня прокатывает (и если удалить строку 4, то тоже работает)
[vba]
Код
    Cells.Replace What:="#REF!/#REF!", Replacement:=""
    Cells.Replace What:=",,", Replacement:=","
    Cells.Replace What:="(,", Replacement:="("
[/vba]
Хотя вот так наверное получше будет
[vba]
Код
    Cells.Replace What:=",#REF!/#REF!", Replacement:=""
    Cells.Replace What:="#REF!/#REF!,", Replacement:=""
[/vba]

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеЕще такой вариант у меня прокатывает (и если удалить строку 4, то тоже работает)
[vba]
Код
    Cells.Replace What:="#REF!/#REF!", Replacement:=""
    Cells.Replace What:=",,", Replacement:=","
    Cells.Replace What:="(,", Replacement:="("
[/vba]
Хотя вот так наверное получше будет
[vba]
Код
    Cells.Replace What:=",#REF!/#REF!", Replacement:=""
    Cells.Replace What:="#REF!/#REF!,", Replacement:=""
[/vba]
Автор - _Boroda_
Дата добавления - 13.03.2017 в 17:00
Мир MS Excel » Вопросы и решения » Вопросы по VBA » Замена выражения в формуле (Макросы/Sub)
  • Страница 1 из 1
  • 1
Поиск:

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