Доброго времени суток. Есть таблица, условно 200 столбцов, 200 строк. В некоторых ячейках введены формулы, с основном сумма других ячеек, как по строкам, так и по столбцам. А в некоторых ячейках вводятся данные ручками. Требуется во всех ячейках с нулевыми значениями отобразить длинное тире (прочерк). Длинное тире должно отображаться и в тех ячейках где не введены данные (пустая ячейка), и там где формула насчитала ноль. Если возможно, то формула не должна затираться длинным тире. Алгоритм_1 1. Изменить формат ячеек на общий. (может и не нужно...) 2. Макрос проверяет пустая ячейка или нет, если непустая то преобразует формулу в значение. Если значение (введено руками) оставляет как есть. 3. Если пустая то ставит в нее длинное тире (юникод 2014). 4. проверяет не пустые ячейки, если "значение=0", то заменить на длинное тире. Макрос выполняется в выделенном диапазоне.
Алгоритм_2 (не уверен в правильности) 1. Изменить формат ячеек на общий. (может и не нужно...) 3. Если ячейка не пустая, проверяет есть ли формула в ячейке, если есть, то изменить формулу на =ЕСЛИ(A1+A2=0;"-";A1+A2), где A1+A2 формула которая была в ячейке. 4. Если в ячейке нет формулы (есть значение введенное руками) проверяет "значение=0", если да, то заменить на длинное тире. !Но тут случается конфуз. Если эта ячейка участвует в формуле, формула выдаст ошибку. При этом если формула типа A1+A2 будет ошибка, если это =СУММ(B2:B15) то ошибки не будет. Что с этим сделать не знаю...! 5. Если значение не равно нулю (текст или число отличное от нуля), то оставить как есть.
Sub qqq()
With Range("A1").CurrentRegion
For Each x In .Cells.SpecialCells(xlCellTypeFormulas)
If x = 0 Then
x.Value = "-"
End if
Next
For Each x In .Cells.SpecialCells(xlCellTypeConstants, 1)
if x = 0 Then
x.Value = "-"
End if
Next
For Each x In .Cells.SpecialCells(xlCellTypeBlanks)
f x = 0 Then
x.Value = "-"
End if
Next
End With
End Sub
А потом начнутся страдания, как посчитать сумму, или еще что, там где стоят прочерки. "— Тебя как, сразу кончить, или желаешь помучиться? — Лучше, конечно, помучиться" (с) "Белое солнце пустыни"
Владимир написал: 1.Формулы нужно будет переписать, примерно так: =если(сумм(a1:a20)=0;"---";сумм(a1:a20))
В том то и дело, что в разных ячейках разные формулы. И надо что бы макрос дописывал (изменял) формулу на =ЕСЛИ(A1-A3=0;"-";A1-A3), где A1-A3 формула которая БЫЛА в ячейке. Я не знаю (уже не помню) VBA и прошу помочь мне описать на VBA этот алгоритм.
kuklp написал: А потом начнутся страдания, как посчитать сумму, или еще что, там где стоят прочерки.
Не начнутся ))) можно ведь скопировать лист\книгу. Скажем так, большой босс лучше понимает если стоит прочерк в ячейке. поэтому перед печатью, скопировал лист, выполнил макрос в выделенном диапазоне и напечатал ))) и все довольны.
в 14 строке, f x = 0 Then - syntax error дописал i получилось if x = 0 Then макрос выполнился, после выполнения Run-time error '13': type mismatch
Результат выполнения макроса: пустые ячейки (без формул и значений) остались пустыми, а должно быть тире. ячейки в которых значение_формулы=0 были переписаны на тире, а хочется что бы там оставалась формула вида =ЕСЛИ(A1-A3=0;"-";A1-A3), где A1-A3 формула которая БЫЛА в ячейке
Есть два варианта. Или в этот макрос добавить еще условие, что если в ячейке есть формула значение которой не равно нулю, заменить его на значение. Или второй вариант, даже если значение формулы равно нулю, то все равно ее менять на формулу вида =ЕСЛИ(A1-A3=0;"-";A1-A3), где A1-A3 формула которая БЫЛА в ячейке
И еще интересный вопрос, можно ли применить к пустой ячейке без формул и значений ту же формулу =ЕСЛИ(A1=0;"-"), где A1 адрес самой ячейки. Хотя по идее если в ячейке введено тире, то ячейка все равно должна корректно участвовать в формуле.?
nafanja2007, добрый вечер. Если нужно РЕАЛЬНО длинное тире, то используйте код 0150 или 0151. Результатом будет «–» или «—» соответственно. Коды набираются с зажатым Alt на дополнительной Num-клавиатуре (справа) или с помощью функции =СИМВОЛ() (в функции ведущие нули можно не указывать)
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
nafanja2007 написал: большой босс лучше понимает если стоит прочерк в ячейке.
Тогда мона не трогать документ. Копировать лист, в нем пустые без цикла заменить на тире, после заменить ноли на тире(ctrl+f) , отправить на печать и помножить отчет на ноль. Макрос несложный, но и неинтересный
kuklp написал: Копировать лист, в нем пустые без цикла заменить на тире, после заменить ноли на тире(ctrl+f) , отправить на печать и помножить отчет на ноль.
Тут есть два момента, в момент замены нулей на тире в тех ячейках которые участвуют в формуле, формула становится ...эээ плохой, почему-то ексель не хочет что бы в формуле были в качестве значений тире.
nafanja2007 написал: в момент замены нулей на тире в тех ячейках которые участвуют в формуле, формула становится ...эээ плохой
- голуба, а ну еще раз перечитайте №3 и свой ответ на него (Чемберлену, гы). Вы же не первый на форуме такой. Но выход все же есть - скопировали лист, поменяли формулы на значения... далее по списку.
Jack Famous написал: Если нужно РЕАЛЬНО длинное тире, то используйте код 0150 или 0151. Результатом будет «–» или «—» соответственно. Коды набираются с зажатым Alt на дополнительной Num-клавиатуре (справа) или с помощью функции =СИМВОЛ() (в функции ведущие нули можно не указывать)
Код среднего тире 2013, а длинного 2014 в юникоде, и 150 или 151 в ASCII и кириллица
с помощью функции =СИМВОЛ() Это и в макросах работает?
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
kuklp написал: - голуба, а ну еще раз перечитайте №3 и свой ответ на него (Чемберлену, гы).
Перечитал. Поправил свой первый пост. Лист отчета с которым это нужно сотворить, достаточно большой и возможно будет не один и все разные. поэтому и задумался о макросе, выделил таблицу или вообще весь лист, запустил макрос, получил готовый для печати документ. Макрос будет выполнятся бухгалтером, методом "нажатия на кнопку и получения результата".
У меня вопрос по теме. Идет копирование ячеек через цикл с предварительным вопросом, и если ячейки пустые или 0 она пишет все кроме заполненных. Как сделать запрос если она находит хотя бы одну >0 и далее уже без вопроса меняет все следующие? пробовал делать через ElseIf но она начинает работать с циклом совместно спрашивая для каждой ячейки
Скрытый текст
Sub FILL_TRUCK() Dim msg Dim r1, r2 As Range Set r1 = Sheets("GMATRIX").Range("A1:A31") Set r2 = Sheets("TRUCK").Range("C4:C34") msg = MsgBox("Вставить значения из Google maps?", 36, "Google maps") If msg = 6 Then: r1.Value = r2.Value For km = 1 To 31 If Sheets("TRUCK").Cells(km + 3, 5).Value = 0 Then Sheets("TRUCK").Cells(km + 3, 5).Value = Sheets("GMATRIX").Cells(km, 2).Value End If Next km If msg = 7 Then: Exit Sub End Sub
Настя_Nastya, теперь мне такой способ кажется самым логичным, только было это в далёком 2016ом)))
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
nafanja2007 написал: Код среднего тире 2013, а длинного 2014 в юникоде, и 150 или 151 в ASCII и кириллица
Если уж возвращаться к старому, то для юникода есть функции VBA ChrW и AscW и функции рабочего листа UNICODE и UNICHAR (ЮНИСИМВ) (только для версий Excel 2013+). Использование кодов 150 и 151 кодовой страницы Win-1251 не рекомендуется с точки зрения переносимости на другие конфигурации.