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

Вход

Регистрация

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

 

= Мир MS Excel/Макрос замены формул на значения + цвет текста - Мир MS Excel

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

Excel 2010
Здравствуйте!
Я "обычный пользователь", и кодить не умею ни на чем, но вот возникла такая необходимость: есть документ, в котором ряд ячеек "сдублированы" с соседних (например, в ячейке A2 стоит "=A1") и задан цвет текста "новой" ячейки (A2). В исходной ячейке - ссылка на другой лист или вообще другой документ. Задача: заменить все формулы в "новых" ячейках (с красным текстом) - на значения.
Если кому не в лом - помогите, пожалуйста. Вот я нашел, например такой макрос:

Sub bb()
Dim a As Range
For Each a In Intersect(Selection, ActiveSheet.UsedRange).Areas
a.Value = a.Value
Next
End Sub

Можно ли к нему добавить условие для проверки цвета текста ячейки?

Собственных "знаний" хватит только на то, чтобы сохранить и запустить готовый макрос((
[moder]Ну хоть на прочтение Правил форума хватит еще сил-то?
К сообщению приложен файл: 9399275.xls (26.0 Kb)


Сообщение отредактировал Nowhere_Man_1983 - Понедельник, 11.08.2014, 16:05
 
Ответить
СообщениеЗдравствуйте!
Я "обычный пользователь", и кодить не умею ни на чем, но вот возникла такая необходимость: есть документ, в котором ряд ячеек "сдублированы" с соседних (например, в ячейке A2 стоит "=A1") и задан цвет текста "новой" ячейки (A2). В исходной ячейке - ссылка на другой лист или вообще другой документ. Задача: заменить все формулы в "новых" ячейках (с красным текстом) - на значения.
Если кому не в лом - помогите, пожалуйста. Вот я нашел, например такой макрос:

Sub bb()
Dim a As Range
For Each a In Intersect(Selection, ActiveSheet.UsedRange).Areas
a.Value = a.Value
Next
End Sub

Можно ли к нему добавить условие для проверки цвета текста ячейки?

Собственных "знаний" хватит только на то, чтобы сохранить и запустить готовый макрос((
[moder]Ну хоть на прочтение Правил форума хватит еще сил-то?

Автор - Nowhere_Man_1983
Дата добавления - 11.08.2014 в 15:05
Nowhere_Man_1983 Дата: Понедельник, 11.08.2014, 16:08 | Сообщение № 2
Группа: Пользователи
Ранг: Новичок
Сообщений: 11
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Извините, я не выложил файл-пример,да? Сейчас прикрепил кусок, но он отличается от моего файла тем, что в "исходных" (черных) ячейках обычный текст вместо ссылки на внешний источник. Поэтому мне нужно как-то вставить значения не всех формул, а именно из ячеек, текст в которых красный...
 
Ответить
СообщениеИзвините, я не выложил файл-пример,да? Сейчас прикрепил кусок, но он отличается от моего файла тем, что в "исходных" (черных) ячейках обычный текст вместо ссылки на внешний источник. Поэтому мне нужно как-то вставить значения не всех формул, а именно из ячеек, текст в которых красный...

Автор - Nowhere_Man_1983
Дата добавления - 11.08.2014 в 16:08
_Boroda_ Дата: Понедельник, 11.08.2014, 16:21 | Сообщение № 3
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16675
Репутация: 6481 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Если в черных ячейках тоже могут быть формулы, тогда придется поячеечно. Например (самый простой способ), так[vba]
Код
Sub tt()
     For i = 1 To Selection.Count
         If Selection(i).Font.ColorIndex = 3 Then Selection(i) = Selection(i).Value
     Next i
End Sub
[/vba]Предварительно выделите все ячейки (и красные, и все остальные)


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеЕсли в черных ячейках тоже могут быть формулы, тогда придется поячеечно. Например (самый простой способ), так[vba]
Код
Sub tt()
     For i = 1 To Selection.Count
         If Selection(i).Font.ColorIndex = 3 Then Selection(i) = Selection(i).Value
     Next i
End Sub
[/vba]Предварительно выделите все ячейки (и красные, и все остальные)

Автор - _Boroda_
Дата добавления - 11.08.2014 в 16:21
Nowhere_Man_1983 Дата: Понедельник, 11.08.2014, 17:11 | Сообщение № 4
Группа: Пользователи
Ранг: Новичок
Сообщений: 11
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
_Boroda_, спасибо просто ну космическое!! все работает!
 
Ответить
Сообщение_Boroda_, спасибо просто ну космическое!! все работает!

Автор - Nowhere_Man_1983
Дата добавления - 11.08.2014 в 17:11
Nowhere_Man_1983 Дата: Среда, 03.09.2014, 09:29 | Сообщение № 5
Группа: Пользователи
Ранг: Новичок
Сообщений: 11
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Еще раз всем здравствуйте!

Есть ли какой-нибудь способ изменить замечательный макрос из сообщения №3 выше таким образом, чтобы он обрабатывал 1) нескольких листов сразу/все листы открытой книги 2) область печати? Т.е. раньше у меня все было на одном листе (под 20 000 строк), я прекрасно выделял нужную область и запускал макрос, а теперь эти документы разбили на стотыщмильёонов листов, и сразу стало не очень весело(( Было бы невероятно круто просто открыть документ и запустить макрос.

Вот исходная версия макроса:
Sub tt()
    For i = 1 To Selection.Count
        If Selection(i).Font.ColorIndex = 3 Then Selection(i) = Selection(i).Value
    Next i
End Sub


Спасибо.
 
Ответить
СообщениеЕще раз всем здравствуйте!

Есть ли какой-нибудь способ изменить замечательный макрос из сообщения №3 выше таким образом, чтобы он обрабатывал 1) нескольких листов сразу/все листы открытой книги 2) область печати? Т.е. раньше у меня все было на одном листе (под 20 000 строк), я прекрасно выделял нужную область и запускал макрос, а теперь эти документы разбили на стотыщмильёонов листов, и сразу стало не очень весело(( Было бы невероятно круто просто открыть документ и запустить макрос.

Вот исходная версия макроса:
Sub tt()
    For i = 1 To Selection.Count
        If Selection(i).Font.ColorIndex = 3 Then Selection(i) = Selection(i).Value
    Next i
End Sub


Спасибо.

Автор - Nowhere_Man_1983
Дата добавления - 03.09.2014 в 09:29
Gustav Дата: Среда, 03.09.2014, 14:49 | Сообщение № 6
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2748
Репутация: 1137 ±
Замечаний: 0% ±

начинал с Excel 4.0, видел 2.1
чтобы он обрабатывал 1) нескольких листов сразу/все листы открытой книги

Случай 1), все листы:
[vba]
Код
Sub convertAllFormulaToValue()
       Dim wks  As Worksheet
       Dim cell As Range
       On Error Resume Next 'на случай, если на каких-то листах формул не будет
       For Each wks In ThisWorkbook.Worksheets
           For Each cell In wks.Cells.SpecialCells(xlCellTypeFormulas)
               If cell.Font.ColorIndex = 3 Then cell.Value = cell.Value
           Next cell
       Next wks
End Sub
[/vba]

P.S. Всеобъемлющий случай:
[vba]
Код
Sub convertAllFormulaToValue2()
      Dim wks     As Worksheet
      Dim rng     As Range
      Dim rng1    As Range
      Dim rng2    As Range
      Dim cell    As Range

      For Each wks In ThisWorkbook.Worksheets
          On Error Resume Next
              Set rng1 = wks.Cells.SpecialCells(xlCellTypeFormulas)
              Set rng2 = wks.Range("Область_печати")
          On Error GoTo 0
            
          If Not rng1 Is Nothing And Not rng2 Is Nothing Then
              Set rng = Union(rng1, rng2)
          ElseIf Not rng1 Is Nothing Then
              Set rng = rng1
          ElseIf Not rng2 Is Nothing Then
              Set rng = rng2
          Else
              Set rng = Nothing
          End If
            
          If Not rng Is Nothing Then
              For Each cell In rng
                  If cell.Font.ColorIndex = 3 Then cell.Value = cell.Value
              Next cell
          End If
      Next wks
End Sub
[/vba]


МОИ: Ник, Tip box: 41001663842605

Сообщение отредактировал Gustav - Среда, 03.09.2014, 15:09
 
Ответить
Сообщение
чтобы он обрабатывал 1) нескольких листов сразу/все листы открытой книги

Случай 1), все листы:
[vba]
Код
Sub convertAllFormulaToValue()
       Dim wks  As Worksheet
       Dim cell As Range
       On Error Resume Next 'на случай, если на каких-то листах формул не будет
       For Each wks In ThisWorkbook.Worksheets
           For Each cell In wks.Cells.SpecialCells(xlCellTypeFormulas)
               If cell.Font.ColorIndex = 3 Then cell.Value = cell.Value
           Next cell
       Next wks
End Sub
[/vba]

P.S. Всеобъемлющий случай:
[vba]
Код
Sub convertAllFormulaToValue2()
      Dim wks     As Worksheet
      Dim rng     As Range
      Dim rng1    As Range
      Dim rng2    As Range
      Dim cell    As Range

      For Each wks In ThisWorkbook.Worksheets
          On Error Resume Next
              Set rng1 = wks.Cells.SpecialCells(xlCellTypeFormulas)
              Set rng2 = wks.Range("Область_печати")
          On Error GoTo 0
            
          If Not rng1 Is Nothing And Not rng2 Is Nothing Then
              Set rng = Union(rng1, rng2)
          ElseIf Not rng1 Is Nothing Then
              Set rng = rng1
          ElseIf Not rng2 Is Nothing Then
              Set rng = rng2
          Else
              Set rng = Nothing
          End If
            
          If Not rng Is Nothing Then
              For Each cell In rng
                  If cell.Font.ColorIndex = 3 Then cell.Value = cell.Value
              Next cell
          End If
      Next wks
End Sub
[/vba]

Автор - Gustav
Дата добавления - 03.09.2014 в 14:49
Nowhere_Man_1983 Дата: Среда, 03.09.2014, 18:14 | Сообщение № 7
Группа: Пользователи
Ранг: Новичок
Сообщений: 11
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Gustav, спасибо, но "что-то пошло не так", когда запускаю макрос - ничего не происходит, гм.((
 
Ответить
СообщениеGustav, спасибо, но "что-то пошло не так", когда запускаю макрос - ничего не происходит, гм.((

Автор - Nowhere_Man_1983
Дата добавления - 03.09.2014 в 18:14
Gustav Дата: Среда, 03.09.2014, 18:25 | Сообщение № 8
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2748
Репутация: 1137 ±
Замечаний: 0% ±

начинал с Excel 4.0, видел 2.1
Макрос должен находиться в модуле книги, которую он обрабатывает. Это так? Это условие выполняется? Макросы разрешены? Возможно, открыты какие-то другие файлы и фокус находится на них...

P.S. Проверил на файле из первого сообщения темы - всё чётко, формулы красных ячеек превращаются в значения.


МОИ: Ник, Tip box: 41001663842605

Сообщение отредактировал Gustav - Среда, 03.09.2014, 18:30
 
Ответить
СообщениеМакрос должен находиться в модуле книги, которую он обрабатывает. Это так? Это условие выполняется? Макросы разрешены? Возможно, открыты какие-то другие файлы и фокус находится на них...

P.S. Проверил на файле из первого сообщения темы - всё чётко, формулы красных ячеек превращаются в значения.

Автор - Gustav
Дата добавления - 03.09.2014 в 18:25
Nowhere_Man_1983 Дата: Четверг, 04.09.2014, 08:43 | Сообщение № 9
Группа: Пользователи
Ранг: Новичок
Сообщений: 11
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
hands hands hands
А вот так, из той же книги, все работает на отлично!! Спасибо!
 
Ответить
Сообщениеhands hands hands
А вот так, из той же книги, все работает на отлично!! Спасибо!

Автор - Nowhere_Man_1983
Дата добавления - 04.09.2014 в 08:43
Nowhere_Man_1983 Дата: Четверг, 04.09.2014, 17:12 | Сообщение № 10
Группа: Пользователи
Ранг: Новичок
Сообщений: 11
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Добрый вечер всем присутствующим!

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

УСЛОВИЕ: Есть документ, в котором в ряде ячеек стоят формулы, ссылающиеся на вышестоящую ячейку (например, в ячейке A2 прописано "=A1", в ячейке B4 прописано "=B3")
ЗАДАЧА: заменить формулы на значения во всех ячейках, в которых идет ссылка на вышестоящую ячейку, т.е. что-то типа (предположим, что XY - любая ячейка, X-буква, Y-цифра):

"Если ячейка XY содержит ссылку на ячейку X[Y-1] , то заменить формулу в ячейке XY на возвращаемое значение"

Для примера можно взять файл, приложенный к первому сообщению

Ну вдруг кому-то будет нечего делать... А я пока пошел читать "VBA для чайников"
 
Ответить
СообщениеДобрый вечер всем присутствующим!

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

УСЛОВИЕ: Есть документ, в котором в ряде ячеек стоят формулы, ссылающиеся на вышестоящую ячейку (например, в ячейке A2 прописано "=A1", в ячейке B4 прописано "=B3")
ЗАДАЧА: заменить формулы на значения во всех ячейках, в которых идет ссылка на вышестоящую ячейку, т.е. что-то типа (предположим, что XY - любая ячейка, X-буква, Y-цифра):

"Если ячейка XY содержит ссылку на ячейку X[Y-1] , то заменить формулу в ячейке XY на возвращаемое значение"

Для примера можно взять файл, приложенный к первому сообщению

Ну вдруг кому-то будет нечего делать... А я пока пошел читать "VBA для чайников"

Автор - Nowhere_Man_1983
Дата добавления - 04.09.2014 в 17:12
Gustav Дата: Четверг, 04.09.2014, 19:01 | Сообщение № 11
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2748
Репутация: 1137 ±
Замечаний: 0% ±

начинал с Excel 4.0, видел 2.1
Тоже мне, бином Ньютона! Меняем в коде одну строчку на другую и наслаждаемся:

[vba]
Код
    'If cell.Font.ColorIndex = 3 Then cell.Value = cell.Value

     If cell.FormulaR1C1 = "=R[-1]C" Then cell.Value = cell.Value
[/vba]


МОИ: Ник, Tip box: 41001663842605
 
Ответить
СообщениеТоже мне, бином Ньютона! Меняем в коде одну строчку на другую и наслаждаемся:

[vba]
Код
    'If cell.Font.ColorIndex = 3 Then cell.Value = cell.Value

     If cell.FormulaR1C1 = "=R[-1]C" Then cell.Value = cell.Value
[/vba]

Автор - Gustav
Дата добавления - 04.09.2014 в 19:01
Nowhere_Man_1983 Дата: Пятница, 05.09.2014, 07:15 | Сообщение № 12
Группа: Пользователи
Ранг: Новичок
Сообщений: 11
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Для некоторых очень даже бином))) Домо аригато, Густав-сенсей!
 
Ответить
СообщениеДля некоторых очень даже бином))) Домо аригато, Густав-сенсей!

Автор - Nowhere_Man_1983
Дата добавления - 05.09.2014 в 07:15
Мир MS Excel » Вопросы и решения » Вопросы по VBA » Макрос замены формул на значения + цвет текста (Макросы/Sub)
  • Страница 1 из 1
  • 1
Поиск:

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