Макрос Replace_by_VAL - заменить формулы на значения

Старая форма входа

Страница 1 из 1 1
Модератор форума: китин, _Boroda_

Мир MS Excel » Вопросы и решения » Вопросы по VBA » Макрос Replace_by_VAL - заменить формулы на значения (Помогите "допилить" макрос, пожалуйста)

Alex_ST

Дата: Понедельник, 06.05.2013, 17:09 | Сообщение № 1

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

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

Сообщений: 3206

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

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

2003

Давно написал и подвесил на хоткеи макрос, который в выделенном диапазоне заменит формулы на значения:[vba]

Код

Sub Replace_by_VAL()   '  в выбранном диапазоне в не скрытых ячейках заменить формулы на значения
      On Error Resume Next
      With ActiveWindow.RangeSelection.Cells.SpecialCells(xlCellTypeFormulas).SpecialCells(xlCellTypeVisible)
         .Value = .Value
      End With
End Sub

[/vba]
Всё работает хорошо пока не выбираю ОДНУ ячейку с формулой.
А вот если выбираю ОДНУ ячейку, то SpecialCells(xlCellTypeFormulas) выбирает ВСЕ ячейки листа, содержащие формулы.
Это вообще-то принцип, часто встречающийся в Excel'e: например, если перед нажатием Ctrl+F был выбран диапазон ячеек, то ищет только в нём, а если одна ячейка - на всём листе.

Всё руки не доходили подправить макрос. Просто выбирал диапазон и всё работало. А тут на днях забыл об этой бяке и тапнул хоткей, выбрав только одну ячейку, где нужно было поменять формулы на значения. Потом сдуру сохранился... и нажил себе гемор восстановить забитые значениями формулы на всём листе.
Поправил попорченный файл. Подумал, что ща на раз-два макрос подправлю, а что-то "не выходит каменный цветок" простыми действиями.
А сильно усложнять не хочется...
Есть у кого-нибудь идеи, как обойти?

Давно написал и подвесил на хоткеи макрос, который в выделенном диапазоне заменит формулы на значения:[vba]

Код

С уважением,
Алексей
MS Excel 2003 - the best!!!

Сообщение отредактировал Alex_ST - Понедельник, 06.05.2013, 17:24

Ответить

СообщениеДавно написал и подвесил на хоткеи макрос, который в выделенном диапазоне заменит формулы на значения:[vba]

Код

Автор - Alex_ST
Дата добавления - 06.05.2013 в 17:09

Саня

Дата: Понедельник, 06.05.2013, 17:28 | Сообщение № 2

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

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

Сообщений: 1068

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

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

XL 2016

[vba]

Код

dim rng2Val as range
With ActiveWindow.RangeSelection
If .count=1 then
Set rng2Val=.Cells(1)
Else
Set rng2Val=.Cells.SpecialCells(xlCellTypeFormulas).SpecialCells(xlCellTypeVisible)
End If
End With

rng2Val.value=rng2Val.value

[/vba]

Ответить

Alex_ST

Дата: Понедельник, 06.05.2013, 22:46 | Сообщение № 3

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

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

Сообщений: 3206

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

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

2003

Привет, Саш!
Так-то я, естественно, пробовал.
Но не помню, почему забраковал вариант с .Count … Кажется, при каких-то условиях не срабатывало.
Завтра на работе с утра (пока не загрузили - завал жуткий! cry

) попробую поэкспериментировать.
Отпишусь обязательно.

С уважением,
Алексей
MS Excel 2003 - the best!!!

Ответить

Alex_ST

Дата: Вторник, 07.05.2013, 08:46 | Сообщение № 4

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

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

Сообщений: 3206

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

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

2003

Саш, ещё раз спасибо за совет.
Совсем я закрутился на работе и сходу сдуру пытался проверять .Count сразу в ActiveWindow.RangeSelection.Cells.SpecialCells(xlCellTypeFormulas).SpecialCells(xlCellTypeVisible) , а не в ActiveWindow.RangeSelection вот и выбирались все.
А On Error Resume Next там нужен чтобы не вылетало в ошибку если формул в выделенном диапазоне нет.
Да и .Select в данном случае мне кажется удобнее использовать чтобы была видна отработка макроса.
В общем, вот так у меня в результате получилось:[vba]

Код

Sub Replace_by_VAL()   '  в выбранном диапазоне в не скрытых ячейках заменить формулы на значения
    On Error Resume Next
    With ActiveWindow.RangeSelection
       If .Count = 1 Then
          .Cells(1).Select
       Else
          .Cells.SpecialCells(xlCellTypeFormulas).SpecialCells(xlCellTypeVisible).Select
       End If
    End With
    Selection.Value = Selection.Value
End Sub

[/vba]

С уважением,
Алексей
MS Excel 2003 - the best!!!

Ответить

Саня

Дата: Вторник, 07.05.2013, 10:03 | Сообщение № 5

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

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

Сообщений: 1068

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

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

XL 2016

я сейчас подумал, а что если первый SpecialCells вернет одну ячейку, второй как себя поведет?

Ответить

Alex_ST

Дата: Вторник, 07.05.2013, 12:36 | Сообщение № 6

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

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

Сообщений: 3206

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

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

2003

Что-то мне мыло об ответе не пришло...
Надо экспериментировать.
Это удобно сделать если просто закомментить в моей процедуре последнюю строку. Тогда просто по выделению будет видно, что выбралось.
Сейчас мне некогда пробовать варианты, но может быть имеет смысл чуть подправить так:[vba]

Код

Sub Replace_by_VAL()   '  в выбранном диапазоне в не скрытых ячейках заменить формулы на значения
     On Error Resume Next
     With ActiveWindow.RangeSelection.SpecialCells(xlCellTypeVisible)
     If .Count = 1 Then
         .Cells(1).Select
     Else
         .Cells.SpecialCells(xlCellTypeFormulas).Select
     End If
     End With
     Selection.Value = Selection.Value
End Sub

[/vba]

С уважением,
Алексей
MS Excel 2003 - the best!!!

Ответить

_Boroda_

Дата: Вторник, 07.05.2013, 22:42 | Сообщение № 7

Группа: Модераторы

Ранг: Местный житель

Сообщений: 16675

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

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

2003; 2007; 2010; 2013 RUS

Цитата (Саня)

а что если первый SpecialCells вернет одну ячейку, второй как себя поведет?

А я как-то вообще не понял, зачем тут SpecialCells(xlCellTypeFormulas)? В ячейке либо формула, либо значение, так и вставляем тупо во все ячейки всё значениями.
С одной стороны, можно сказать, что выбором только ячеек с формулами мы возможно уменьшаем количество обрабатываемых ячеек. Да, это так, но, с другой стороны, мы увеличиваем количество несвязанных диапазонов. И что будет быстрее работать - очень спорный вопрос, во многом зависящий от конкретной ситуации.

Кстати, возможно, не помешает отключить автопересчет формул.
И еще: если предварительно были (была) выделены ячейки, ВСЕ из которых впоследствии скрыты, а потом запущен макрос, то он (из-за On error resume next) отрабатывает неверно. Поэтому нужно или обработчик ошибки вставлять (типа иф еррор намбер ...) или писать Готу А (что я и сделал, хотя и "дурной тон" писать гоутушки).
Что-то типа:
[vba]

Код

Sub Replace_by_VAL()
       On Error GoTo A
       ACal = Application.Calculation
       Application.Calculation = xlCalculationManual
       With ActiveWindow.RangeSelection.SpecialCells(xlCellTypeVisible)
       If .Count = 1 Then
           .Cells(1).Select
       Else
           .Cells.Select
       End If
       End With
       Selection.Value = Selection.Value
A:
       Application.Calculation = ACal
End Sub

[/vba]

Цитата (Саня)

а что если первый SpecialCells вернет одну ячейку, второй как себя поведет?

Код

[/vba]_Boroda_

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995

Ответить

Сообщение

Цитата (Саня)

а что если первый SpecialCells вернет одну ячейку, второй как себя поведет?

Код

[/vba]

Автор - _Boroda_
Дата добавления - 07.05.2013 в 22:42

Alex_ST

Дата: Среда, 08.05.2013, 08:45 | Сообщение № 8

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

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

Сообщений: 3206

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

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

2003

Ну, на счёт отключения автопересчёта формул, то на всякий случай можно и добавить (честно говоря, я просто не знаю, что на листе происходит: пересчёт формул или заполнение макросом).
По поводу выбора ячеек только с формулами, так это для того, чтобы не нарушать различных форматирований внутри ячеек (хоть и редко, но иногда и мне приходится выделять несколько слов в фразе внутри ячейки).
А на счёт извращённого использования типа сначала скрыть всё выделенное, а потом запустить макрос, так пусть тот извращенец, который так сделает и получит в награду неверный результат.
Главное при написании процедур, ИМХО, чтобы при нормальном использовании они работали нормально и чтобы не вылетали в ошибку при любых извращениях.

С уважением,
Алексей
MS Excel 2003 - the best!!!

Ответить

_Boroda_

Дата: Среда, 08.05.2013, 16:30 | Сообщение № 9

Группа: Модераторы

Ранг: Местный житель

Сообщений: 16675

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

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

2003; 2007; 2010; 2013 RUS

Цитата (Alex_ST)

По поводу выбора ячеек только с формулами, так это для того, чтобы не нарушать различных форматирований внутри ячеек (хоть и редко, но иногда и мне приходится выделять несколько слов в фразе внутри ячейки).

Вот этого не понял. То есть, ты выделяешь несколько слов в фразе внутри ячейки (следовательно, там заведомо не формула, а просто текст) и запускаешь макрос преобразования формул в текст?

Цитата (Alex_ST)

А на счёт извращённого использования типа сначала скрыть всё выделенное, а потом запустить макрос, так пусть тот извращенец, который так сделает и получит в награду неверный результат.

А как же твоё

Цитата (Alex_ST)

А тут на днях забыл об этой бяке и тапнул хоткей

Всяко может случиться, так почему не застраховаться заранее от уже найденной нехорошести?

Ответить

Alex_ST

Дата: Среда, 08.05.2013, 19:46 | Сообщение № 10

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

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

Сообщений: 3206

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

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

2003

Цитата (_Boroda_)

Вот этого не понял.

Ну, извини, подрядчик по телефону на ухе повис пока я писал пост, вот я и невнятно выразился.
Я имел в виду, что если не ограничивать замены только ячейками с формулами, то будет произведена и замена в ячейках с текстом. А текст в ячейках может быть и попугайски форматирован (ну, например, как подписи здесь под окном ответа smile

).
И если в ячейке, содержащей текст Максимальный размер файла100Kb ,заменить значение на значение, то форматирование съедет и останется Максимальный размер файла 100 Kb

Ну а по поводу

Цитата (_Boroda_)

Всяко может случиться, так почему не застраховаться заранее от уже найденной нехорошести?

так как раз от этой не очевидной при использовании бяки доработка с .Count и защищает.
А от всего на свете защититься невозможно. Возможна защита только от тех действий, которые не являются явно нелогичными.
И уж лучше пусть при кривом использовании макрос вообще не отработает, чем испортит данные или вылетит в отладку.

С уважением,
Алексей
MS Excel 2003 - the best!!!

Сообщение отредактировал Alex_ST - Среда, 08.05.2013, 19:47

Ответить

Страница 1 из 1
1