Макрос исправляет экспортированные как текст числовые данные в числа в соответствии с форматом ячеек. При внедрении (экспорте) в Excel данных из отчётов внешних баз данных информация часто воспринимается ячейками листа как текст, а не как числа. При этом на взгляд на листе всё выглядит нормально, но получаются ошибки в формулах листа, обрабатывающих такие данные, и фильтрах. Чтобы формат ячейки нормально автоматически переопределился достаточно дважды кликнуть в каждую ячейку или воспользоваться спец.вставкой со сложением с 0 (или умножением на 1). "Даблкликать" в каждую ячейку - это, конечно, не выход когда их больше десятка В ручную делать спец.вставку - лениво. Изначально я написал макрос, реализующий спец.вставку со сложением с 0:[vba]
Код
Sub Repair_Value_() ' исправить(обновить)записи в ячейках чтобы приняли формат ' не все данные почему-то обрабатывает... <img src="http://s5.ucoz.net/sm/1/sad.gif" border="0" align="absmiddle" alt="sad" /> On Error Resume Next Application.CutCopyMode = False ' сбросить буфер обмена чтобы при ошибке копирования ничего не прибавилось к ячейкам спец.вставкой Intersect(ActiveWindow.RangeSelection, ActiveSheet.UsedRange).Cells.SpecialCells(xlCellTypeLastCell).Offset(1).Copy ActiveWindow.RangeSelection.SpecialCells(xlCellTypeConstants).PasteSpecial Paste:=xlPasteValues, Operation:=xlAdd Application.CutCopyMode = False ' только чтобы погасить пунктирную рамку выделения скопированного диапазона If Err Then MsgBox Err.Description, vbExclamation, "Не получилось!" End Sub
[/vba] Но он почему-то не всегда все данные обрабатывает и потому нельзя быть 100%-но уверенным в результате Потом где-то кем-то был предложен другой подход [offtop]Извините, ребята, не помню, с кем вместе и где это "полировалось", потому не могу назвать автора :([/offtop] Этот макрос даёт достоверный результат всегда:[vba]
Код
Sub Repair_Value() ' в выделенных ячейках исправить экспортированные как текст данные чтобы нормально опознались числа Dim rArea As Range On Error Resume Next ActiveWindow.RangeSelection.SpecialCells(xlCellTypeConstants).Select If Err Then Exit Sub With Application: .ScreenUpdating = False: .EnableEvents = False: .Calculation = xlManual: End With For Each rArea In Selection.Areas rArea.FormulaLocal = rArea.FormulaLocal Next rArea With Application: .ScreenUpdating = True: .EnableEvents = True: .Calculation = xlAutomatic: End With End Sub
[/vba]
Макрос исправляет экспортированные как текст числовые данные в числа в соответствии с форматом ячеек. При внедрении (экспорте) в Excel данных из отчётов внешних баз данных информация часто воспринимается ячейками листа как текст, а не как числа. При этом на взгляд на листе всё выглядит нормально, но получаются ошибки в формулах листа, обрабатывающих такие данные, и фильтрах. Чтобы формат ячейки нормально автоматически переопределился достаточно дважды кликнуть в каждую ячейку или воспользоваться спец.вставкой со сложением с 0 (или умножением на 1). "Даблкликать" в каждую ячейку - это, конечно, не выход когда их больше десятка В ручную делать спец.вставку - лениво. Изначально я написал макрос, реализующий спец.вставку со сложением с 0:[vba]
Код
Sub Repair_Value_() ' исправить(обновить)записи в ячейках чтобы приняли формат ' не все данные почему-то обрабатывает... <img src="http://s5.ucoz.net/sm/1/sad.gif" border="0" align="absmiddle" alt="sad" /> On Error Resume Next Application.CutCopyMode = False ' сбросить буфер обмена чтобы при ошибке копирования ничего не прибавилось к ячейкам спец.вставкой Intersect(ActiveWindow.RangeSelection, ActiveSheet.UsedRange).Cells.SpecialCells(xlCellTypeLastCell).Offset(1).Copy ActiveWindow.RangeSelection.SpecialCells(xlCellTypeConstants).PasteSpecial Paste:=xlPasteValues, Operation:=xlAdd Application.CutCopyMode = False ' только чтобы погасить пунктирную рамку выделения скопированного диапазона If Err Then MsgBox Err.Description, vbExclamation, "Не получилось!" End Sub
[/vba] Но он почему-то не всегда все данные обрабатывает и потому нельзя быть 100%-но уверенным в результате Потом где-то кем-то был предложен другой подход [offtop]Извините, ребята, не помню, с кем вместе и где это "полировалось", потому не могу назвать автора :([/offtop] Этот макрос даёт достоверный результат всегда:[vba]
Код
Sub Repair_Value() ' в выделенных ячейках исправить экспортированные как текст данные чтобы нормально опознались числа Dim rArea As Range On Error Resume Next ActiveWindow.RangeSelection.SpecialCells(xlCellTypeConstants).Select If Err Then Exit Sub With Application: .ScreenUpdating = False: .EnableEvents = False: .Calculation = xlManual: End With For Each rArea In Selection.Areas rArea.FormulaLocal = rArea.FormulaLocal Next rArea With Application: .ScreenUpdating = True: .EnableEvents = True: .Calculation = xlAutomatic: End With End Sub
Не всё правильно преобразуется в число, например когда системный разделитель стоит запятая, а импортировались числа с обоими видами разделителей (точка,запятая). Например число такого ввида 3.000 (разделитель точка при системном разделителе запятая и перед числом стоит апостроф).Это число располагается посередине ячейки, а не слева или справа.также число 1.2(разделитель точка при системном разделителе запятая,апострофа нет,excel показывает,что формат текстовый. Более точен такой приведенный здесь алгоритм: http://www.planetaexcel.ru/forum....N_1=193 в сообщении #11
Не всё правильно преобразуется в число, например когда системный разделитель стоит запятая, а импортировались числа с обоими видами разделителей (точка,запятая). Например число такого ввида 3.000 (разделитель точка при системном разделителе запятая и перед числом стоит апостроф).Это число располагается посередине ячейки, а не слева или справа.также число 1.2(разделитель точка при системном разделителе запятая,апострофа нет,excel показывает,что формат текстовый. Более точен такой приведенный здесь алгоритм: http://www.planetaexcel.ru/forum....N_1=193 в сообщении #11Неопытный
Неопытный, я не понимаю цели Вашего поста. Вы хотите подвигнуть меня к переработке моего макроса под чей-то чужой алгоритм, изложенный хоть и на очень мною уважаемом, но всё-таки стороннем форуме? Так этого не будет, т.к. приведённая в старт-топике процедура вполне устраивает меня и большинство пользователей, с которыми я поделился ею. К тому же, я предпочитаю при сборе данных из разных документов сначала хоть немного обрабатывать их на отдельном листе, а потом уже копировать в сборную таблицу (не говорю "сводную" чтобы не было разночтений). Да и вставляю я данные чаще всего спецвставкой чтобы были приняты мои форматы ячеек. Так намного проще, чем потом в одной таблице, набранной по кускам из разных источников, искать, где разделитель - точка, а где перед нормальным числом стоит невидимый апостроф.
Неопытный, я не понимаю цели Вашего поста. Вы хотите подвигнуть меня к переработке моего макроса под чей-то чужой алгоритм, изложенный хоть и на очень мною уважаемом, но всё-таки стороннем форуме? Так этого не будет, т.к. приведённая в старт-топике процедура вполне устраивает меня и большинство пользователей, с которыми я поделился ею. К тому же, я предпочитаю при сборе данных из разных документов сначала хоть немного обрабатывать их на отдельном листе, а потом уже копировать в сборную таблицу (не говорю "сводную" чтобы не было разночтений). Да и вставляю я данные чаще всего спецвставкой чтобы были приняты мои форматы ячеек. Так намного проще, чем потом в одной таблице, набранной по кускам из разных источников, искать, где разделитель - точка, а где перед нормальным числом стоит невидимый апостроф.Alex_ST
Alex_ST, Круто, СПС. [offtop]Не берусь формулировать новую тему, боюсь "убежать" в сторону. Поэтому спрошу здесь. А, нельзя-ли проделать все тоже самое (формат ячейки), только из пользовательской формы? Нет, из формы все ЭТО работает на УРА!. Вот только, когда работаешь с NamPad, то вставляется по-умолчанию "," и решение какой-либо формулы работает только до второго знака. Если в форме (TextBox) вставляешь число, также работая с NamPad, содержащее знаков после запятой больше двух, то получается косяк-переделывает вводимое дробное число и меняет формат ячейки на числовой с округлением до 0 после запятой, хотя стоит общий. Прошу прощение за сумбур. А, может, я совсем все неправильно делаю? Подскажите. Приложу файлик примера, чтобы Вам не делать лишних движений. Спасибо![/offtop]
Alex_ST, Круто, СПС. [offtop]Не берусь формулировать новую тему, боюсь "убежать" в сторону. Поэтому спрошу здесь. А, нельзя-ли проделать все тоже самое (формат ячейки), только из пользовательской формы? Нет, из формы все ЭТО работает на УРА!. Вот только, когда работаешь с NamPad, то вставляется по-умолчанию "," и решение какой-либо формулы работает только до второго знака. Если в форме (TextBox) вставляешь число, также работая с NamPad, содержащее знаков после запятой больше двух, то получается косяк-переделывает вводимое дробное число и меняет формат ячейки на числовой с округлением до 0 после запятой, хотя стоит общий. Прошу прощение за сумбур. А, может, я совсем все неправильно делаю? Подскажите. Приложу файлик примера, чтобы Вам не делать лишних движений. Спасибо![/offtop]=XYZ
К сожалению, из Вашего объяснения ничего не понял Что за зверь такой NamPad ? Может быть, Вы имели в виду боковую секцию клавиатуры с цифрами? Так она называется NumPad Я чаще всего именно её для ввода цифр и использую (NumLock выставлен по умолчанию при запуске) и никаких проблем с вводом данных куда бы то ни было никогда не было. А файлик Ваш я посмотреть не могу - на работе параноики-сисадмины своей антивирью откоцывают от файлов модули с кодом VBA.
К сожалению, из Вашего объяснения ничего не понял Что за зверь такой NamPad ? Может быть, Вы имели в виду боковую секцию клавиатуры с цифрами? Так она называется NumPad Я чаще всего именно её для ввода цифр и использую (NumLock выставлен по умолчанию при запуске) и никаких проблем с вводом данных куда бы то ни было никогда не было. А файлик Ваш я посмотреть не могу - на работе параноики-сисадмины своей антивирью откоцывают от файлов модули с кодом VBA.Alex_ST
[offtop]Да, я ошибся, прошу прощения. Именно NumPad. Ну, и видимо совсем замакросился. Смысл того, что хотел сказать, так это то, как сделать, чтобы и "," и "." всегда распознавалась как запятая. Просто при работе с клавиатурой где NumPad вынесен в боковую панель проблем нет, а на ноуте нужны дополнительные переключения (конечно если работаешь с русской раскладкой). Короче: ничего не переключая, не трогая "Fn", не зажимая "Shift" пользоваться одной клавишей при этом вводя цифры с TextBox в ячейку(ки), не обращая внимания на то, какая языковая раскладка на клаве, и не трогая "Fn". А, коды там такие. В форме с одним TextBox и кнопкой выхода из формы: [vba]
Код
Private Sub TextBox1_Change() [A1] = Me.TextBox1 Dim rArea As Range On Error Resume Next ActiveWindow.RangeSelection.SpecialCells(xlCellTypeConstants).Select If Err Then Exit Sub With Application: .ScreenUpdating = False: .EnableEvents = False: .Calculation = xlManual: End With For Each rArea In Selection.Areas rArea.FormulaLocal = rArea.FormulaLocal Next rArea With Application: .ScreenUpdating = True: .EnableEvents = True: .Calculation = xlAutomatic: End With End Sub
Private Sub CommandButton1_Click() Unload Me End Sub
[/vba] В стандартном модуле для запуска формы с листа: [vba]
Код
Sub Macro() UserForm1.Show End Sub
[/vba][/offtop]
[offtop]Да, я ошибся, прошу прощения. Именно NumPad. Ну, и видимо совсем замакросился. Смысл того, что хотел сказать, так это то, как сделать, чтобы и "," и "." всегда распознавалась как запятая. Просто при работе с клавиатурой где NumPad вынесен в боковую панель проблем нет, а на ноуте нужны дополнительные переключения (конечно если работаешь с русской раскладкой). Короче: ничего не переключая, не трогая "Fn", не зажимая "Shift" пользоваться одной клавишей при этом вводя цифры с TextBox в ячейку(ки), не обращая внимания на то, какая языковая раскладка на клаве, и не трогая "Fn". А, коды там такие. В форме с одним TextBox и кнопкой выхода из формы: [vba]
Код
Private Sub TextBox1_Change() [A1] = Me.TextBox1 Dim rArea As Range On Error Resume Next ActiveWindow.RangeSelection.SpecialCells(xlCellTypeConstants).Select If Err Then Exit Sub With Application: .ScreenUpdating = False: .EnableEvents = False: .Calculation = xlManual: End With For Each rArea In Selection.Areas rArea.FormulaLocal = rArea.FormulaLocal Next rArea With Application: .ScreenUpdating = True: .EnableEvents = True: .Calculation = xlAutomatic: End With End Sub
Private Sub CommandButton1_Click() Unload Me End Sub
[/vba] В стандартном модуле для запуска формы с листа: [vba]
[offtop]Понял Вас. Сам, пользуясь NumPad, постоянно наступаю на эти грабли. Можно, наверное, попробовать поиграться с заданным в системе разделителем целой и дробной части (Настройки - Панель управления - Язык и региональные стандарты на вкладке "Форматы" кнопка "Дополнительные параметры...") Вот только не знаю, не вылезут ли где-нибудь другие грабли из-за этого? А конкретно в Вашем текстбоксе можно устроить программную автозамену запятой на точку с запретом ввода букв: [vba]
Код
Private Sub TextBox1_Change() 'by ZVI: http://www.planetaexcel.ru/forum.php?thread_id=7712 'Ввод в TextBox только чисел в заданном диапазоне _ с любым децимальным разделителем (можно и точку, и запятую) _ и ограничением количества десятичных разрядов Const min = 0 ' минимальное значение Const max = 100000000 ' максимальное значение Const DecSYMB = 2 ' количество десятичных разрядов Dim DecSEP: DecSEP = Application.DecimalSeparator ' децимальный разделитель приложения Dim VAL#, j%, sVAL$ Static sOldVAL$ On Error Resume Next sVAL = Replace((Replace(Replace(TextBox1.Value, " ", ""), ",", DecSEP)), ".", DecSEP) If Len(sVAL) = 0 Then sOldVAL = "": TextBox1.Value = "": Exit Sub TextBox1.Value = sVAL VAL = CDbl(sVAL) j = InStr(sVAL, DecSEP) If j > 0 Then j = Len(sVAL) - j If Err Or VAL < min Or VAL > max Or j > DecSYMB Then TextBox1.Value = sOldVAL Else sOldVAL = TextBox1.Value End If End Sub
[/vba] Но, вообще-то, здесь - это ОФФТОП. Так что дискуссию прекращаем, а то получим люлей от Сержа. И он будет абсолютно прав.[/offtop]
[offtop]Понял Вас. Сам, пользуясь NumPad, постоянно наступаю на эти грабли. Можно, наверное, попробовать поиграться с заданным в системе разделителем целой и дробной части (Настройки - Панель управления - Язык и региональные стандарты на вкладке "Форматы" кнопка "Дополнительные параметры...") Вот только не знаю, не вылезут ли где-нибудь другие грабли из-за этого? А конкретно в Вашем текстбоксе можно устроить программную автозамену запятой на точку с запретом ввода букв: [vba]
Код
Private Sub TextBox1_Change() 'by ZVI: http://www.planetaexcel.ru/forum.php?thread_id=7712 'Ввод в TextBox только чисел в заданном диапазоне _ с любым децимальным разделителем (можно и точку, и запятую) _ и ограничением количества десятичных разрядов Const min = 0 ' минимальное значение Const max = 100000000 ' максимальное значение Const DecSYMB = 2 ' количество десятичных разрядов Dim DecSEP: DecSEP = Application.DecimalSeparator ' децимальный разделитель приложения Dim VAL#, j%, sVAL$ Static sOldVAL$ On Error Resume Next sVAL = Replace((Replace(Replace(TextBox1.Value, " ", ""), ",", DecSEP)), ".", DecSEP) If Len(sVAL) = 0 Then sOldVAL = "": TextBox1.Value = "": Exit Sub TextBox1.Value = sVAL VAL = CDbl(sVAL) j = InStr(sVAL, DecSEP) If j > 0 Then j = Len(sVAL) - j If Err Or VAL < min Or VAL > max Or j > DecSYMB Then TextBox1.Value = sOldVAL Else sOldVAL = TextBox1.Value End If End Sub
[/vba] Но, вообще-то, здесь - это ОФФТОП. Так что дискуссию прекращаем, а то получим люлей от Сержа. И он будет абсолютно прав.[/offtop]Alex_ST
С уважением, Алексей MS Excel 2003 - the best!!!
Сообщение отредактировал Alex_ST - Вторник, 15.04.2014, 15:05
Private Sub TextBox1_Change() [A1] = Me.TextBox1 Cells.Replace ".", ".", 2 Dim rArea As Range On Error Resume Next ActiveWindow.RangeSelection.SpecialCells(xlCellTypeConstants).Select If Err Then Exit Sub With Application: .ScreenUpdating = False: .EnableEvents = False: .Calculation = xlManual: End With For Each rArea In Selection.Areas rArea.FormulaLocal = rArea.FormulaLocal Next rArea With Application: .ScreenUpdating = True: .EnableEvents = True: .Calculation = xlAutomatic: End With End Sub
[/vba] Добавил строчку к Вашему коду. Всем СПАСИБО![/offtop]
[offtop]Вот и я нашел. СПАСИБО KuklP [vba]
Код
Private Sub TextBox1_Change() [A1] = Me.TextBox1 Cells.Replace ".", ".", 2 Dim rArea As Range On Error Resume Next ActiveWindow.RangeSelection.SpecialCells(xlCellTypeConstants).Select If Err Then Exit Sub With Application: .ScreenUpdating = False: .EnableEvents = False: .Calculation = xlManual: End With For Each rArea In Selection.Areas rArea.FormulaLocal = rArea.FormulaLocal Next rArea With Application: .ScreenUpdating = True: .EnableEvents = True: .Calculation = xlAutomatic: End With End Sub
[/vba] Добавил строчку к Вашему коду. Всем СПАСИБО![/offtop]=XYZ
Сообщение отредактировал =XYZ - Вторник, 15.04.2014, 15:40
Добрый день, не могли бы пояснить: если я вместо [vba]
Код
For Each rArea In Selection.Areas rArea.FormulaLocal = rArea.FormulaLocal Next rArea
[/vba]
буду использовать [vba]
Код
rAreaF.FormulaLocal = rAreaF.FormulaLocal
[/vba] где rAreaF - это полный диапазон (который выделяли) какие могут быть нюансы и грабли? просто при использовании конструкции For Each ... Next в составе большого макроса почему то долго работает, хотя в отдельном файле все проходит быстро - почему - так и не разобрался [p.s.]таблица примерно 800х10 ячеек
Добрый день, не могли бы пояснить: если я вместо [vba]
Код
For Each rArea In Selection.Areas rArea.FormulaLocal = rArea.FormulaLocal Next rArea
[/vba]
буду использовать [vba]
Код
rAreaF.FormulaLocal = rAreaF.FormulaLocal
[/vba] где rAreaF - это полный диапазон (который выделяли) какие могут быть нюансы и грабли? просто при использовании конструкции For Each ... Next в составе большого макроса почему то долго работает, хотя в отдельном файле все проходит быстро - почему - так и не разобрался [p.s.]таблица примерно 800х10 ячеекYujin
Сообщение отредактировал Yujin - Понедельник, 29.05.2017, 14:42
Alex_ST, спасибо, я понял - не разобрался в коде до конца ну у меня просто создание таблицы данных без пропусков, поэтому выделяю я весь диапазон с константами вручную (границы мне заранее известны) я так понял в случае моем время расчета должно быть примерно одинаковым
Alex_ST, спасибо, я понял - не разобрался в коде до конца ну у меня просто создание таблицы данных без пропусков, поэтому выделяю я весь диапазон с константами вручную (границы мне заранее известны) я так понял в случае моем время расчета должно быть примерно одинаковымYujin
Такую мелочь процедура должна обрабатывать практически мгновенно. А Вы случайно не удалили отключение/включение обновления листа, обработки событий и пересчёта: [vba]
Код
With Application: .ScreenUpdating = False: .EnableEvents = False: .Calculation = xlManual: End With ................. With Application: .ScreenUpdating = True: .EnableEvents = True: .Calculation = xlAutomatic: End With
[/vba] Тормоза очень часто бывают именно из-за пренебрежения этими методами.
Такую мелочь процедура должна обрабатывать практически мгновенно. А Вы случайно не удалили отключение/включение обновления листа, обработки событий и пересчёта: [vba]
Код
With Application: .ScreenUpdating = False: .EnableEvents = False: .Calculation = xlManual: End With ................. With Application: .ScreenUpdating = True: .EnableEvents = True: .Calculation = xlAutomatic: End With
[/vba] Тормоза очень часто бывают именно из-за пренебрежения этими методами.Alex_ST
Alex_ST, ScreenUpdating и EnableEvents я отключаю перед запуском основного модуля.а вот при установке .Calculation=xlManual - ругается, поэтому стоит на автомате но я проверял в отдельном созданном файле - с такой же таблицей - работает быстро и без отключения данных событий, и, что характерно, с установкой .Calculation=xlManual наверное и ваш код работает долго из-за того же , почему вычисления не ставятся на ручной режим...
Alex_ST, ScreenUpdating и EnableEvents я отключаю перед запуском основного модуля.а вот при установке .Calculation=xlManual - ругается, поэтому стоит на автомате но я проверял в отдельном созданном файле - с такой же таблицей - работает быстро и без отключения данных событий, и, что характерно, с установкой .Calculation=xlManual наверное и ваш код работает долго из-за того же , почему вычисления не ставятся на ручной режим...Yujin
Сообщение отредактировал Yujin - Среда, 31.05.2017, 10:06
Спасибо, очень полезный код! Но вот момент. В соответствии с кодом числа исправляются на числа, ТОЛЬКО если формат области, в которой они находятся, является Общий, Числовой. Если формат области равен Текстовый, то числа так и останутся текстом. Если же вначале указать формат принимаемой области как Общий или Числовой, то часть экспортируемых данных, например, 03.04.01 (код специальности обучения), преобразуются в число, что недопустимо. Поэтому заранее формат всех ячеек устанавливаю как Текстовый. Можно ли скорректировать макрос, чтобы он вначале определял области, содержащие "числа как числа", затем менял формат ячейки на Общий/Числовой, и в конце обрабатывал числовое значение?
Спасибо, очень полезный код! Но вот момент. В соответствии с кодом числа исправляются на числа, ТОЛЬКО если формат области, в которой они находятся, является Общий, Числовой. Если формат области равен Текстовый, то числа так и останутся текстом. Если же вначале указать формат принимаемой области как Общий или Числовой, то часть экспортируемых данных, например, 03.04.01 (код специальности обучения), преобразуются в число, что недопустимо. Поэтому заранее формат всех ячеек устанавливаю как Текстовый. Можно ли скорректировать макрос, чтобы он вначале определял области, содержащие "числа как числа", затем менял формат ячейки на Общий/Числовой, и в конце обрабатывал числовое значение?Мурад
Мурад, эту проблему методом, обсуждаемым в топике, исправить можно вряд ли. Общеизвестная "бяка" шибко дюже грамотного Excel'я - самовольное преобразование вводимого текста и разделителями ./- в дату. А моя процедура именно это гадское свойство и использует - копирует данные из ячеек и имитирует ручной ввод данных обратно во все ячейки выделенного диапазона.
Мурад, эту проблему методом, обсуждаемым в топике, исправить можно вряд ли. Общеизвестная "бяка" шибко дюже грамотного Excel'я - самовольное преобразование вводимого текста и разделителями ./- в дату. А моя процедура именно это гадское свойство и использует - копирует данные из ячеек и имитирует ручной ввод данных обратно во все ячейки выделенного диапазона.Alex_ST
Вход
Регистрация
Напомнить пароль
Автосумма
Часто используемые
ВПР
ЕСЛИ
СУММЕСЛИ
Финансовые
Логические
Текстовые
Даты и времени 
Ссылки и массивы 
Математические
Другие функции
Проверка свойств и значений
Баз данных
Математические
Обработки массивов
Обработки строк
Преобразования типов данных 
Загрузки данных
Работы с файлами
Обработки системных параметров
Обработки цвета
Работы с датами и временем
Преобразования в системы счисления
Финансовые
