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

Вход

Регистрация

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

 

= Мир MS Excel/Если диапазон содержит ошибку прервать макрос - Мир MS Excel

Регистрация · Логин: · Пароль: · · Забыли пароль?
Страница 1 из 11
Модератор форума: _Boroda_, Pelena, Manyasha, SLAVICK 
Мир MS Excel » Вопросы и решения » Вопросы по VBA » Если диапазон содержит ошибку прервать макрос (Макросы/Sub)
Если диапазон содержит ошибку прервать макрос
pabchek Дата: Вторник, 24.05.2016, 10:29 | Сообщение № 1
Группа: Проверенные
Ранг: Ветеран
Сообщений: 645
Репутация: 145 ±
Замечаний: 0% ±

Excel 2007
Здравствуйте, всем!
Подскажите, плз. Нужно чтобы выполнение макроса прерывалось если в столбце есть ошибка (Н/Д). Пробовал несколько способов, но никак не соображу. Вот один из них.
[vba]
Код
    If IsError(Range("A:A")) Then
        MsgBox "Есть НД"
    End If
[/vba]
(В файле формула исключительно для формирования ошибки)
К сообщению приложен файл: 1200444.xlsb(12Kb)


"Учиться, учиться и еще раз учиться!"
WM: R399923528092
 
Ответить
СообщениеЗдравствуйте, всем!
Подскажите, плз. Нужно чтобы выполнение макроса прерывалось если в столбце есть ошибка (Н/Д). Пробовал несколько способов, но никак не соображу. Вот один из них.
[vba]
Код
    If IsError(Range("A:A")) Then
        MsgBox "Есть НД"
    End If
[/vba]
(В файле формула исключительно для формирования ошибки)

Автор - pabchek
Дата добавления - 24.05.2016 в 10:29
wild_pig Дата: Вторник, 24.05.2016, 10:36 | Сообщение № 2
Группа: Проверенные
Ранг: Обитатель
Сообщений: 458
Репутация: 85 ±
Замечаний: 0% ±

2003, 2013
[vba]
Код
Sub dd()
    For Each cel In Cells(1, 1).CurrentRegion
        If IsError(cel.Value) Then
            MsgBox cel.Address
            Exit Sub 'Exit For
        End If
    Next
End Sub
[/vba]
 
Ответить
Сообщение[vba]
Код
Sub dd()
    For Each cel In Cells(1, 1).CurrentRegion
        If IsError(cel.Value) Then
            MsgBox cel.Address
            Exit Sub 'Exit For
        End If
    Next
End Sub
[/vba]

Автор - wild_pig
Дата добавления - 24.05.2016 в 10:36
_Boroda_ Дата: Вторник, 24.05.2016, 10:40 | Сообщение № 3
Группа: Модераторы
Ранг: Экселист
Сообщений: 9375
Репутация: 3948 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
А вот так не подойдет?
[vba]
Код
Sub dd()
    On Error Resume Next
    If Not WorksheetFunction.Sum(Range("A:A")) Then
        MsgBox "Есть ошибка"
    End If
End Sub
[/vba]
Вернее, вот так
[vba]
Код
Sub dd()
    On Error Resume Next
    a = WorksheetFunction.Sum(Range("A:A"))
    If Err.Number Then
        MsgBox "Есть ошибка"
    End If
End Sub
[/vba]
И вообще, если у тебя там вычисления какие-то, которые при наличии ошибки не работают (дебагер вылетает), то
[vba]
Код
Sub dd()
    On Error Resume Next
    'вычисления
    If Err.Number Then
        MsgBox "Есть ошибка"
Exit sub
    End If
End Sub
[/vba]


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеА вот так не подойдет?
[vba]
Код
Sub dd()
    On Error Resume Next
    If Not WorksheetFunction.Sum(Range("A:A")) Then
        MsgBox "Есть ошибка"
    End If
End Sub
[/vba]
Вернее, вот так
[vba]
Код
Sub dd()
    On Error Resume Next
    a = WorksheetFunction.Sum(Range("A:A"))
    If Err.Number Then
        MsgBox "Есть ошибка"
    End If
End Sub
[/vba]
И вообще, если у тебя там вычисления какие-то, которые при наличии ошибки не работают (дебагер вылетает), то
[vba]
Код
Sub dd()
    On Error Resume Next
    'вычисления
    If Err.Number Then
        MsgBox "Есть ошибка"
Exit sub
    End If
End Sub
[/vba]

Автор - _Boroda_
Дата добавления - 24.05.2016 в 10:40
pabchek Дата: Вторник, 24.05.2016, 10:45 | Сообщение № 4
Группа: Проверенные
Ранг: Ветеран
Сообщений: 645
Репутация: 145 ±
Замечаний: 0% ±

Excel 2007
wild_pig, не работает, если ошибка не в первой строке
_Boroda_, Саш, к сожалению, ввел в заблуждение проставив цифры. На самом деле в колонке текстовые значения. Не подумал, что это важно.
Но второй вариант работает. Но почему? Там же сумма.


"Учиться, учиться и еще раз учиться!"
WM: R399923528092


Сообщение отредактировал pabchek - Вторник, 24.05.2016, 10:58
 
Ответить
Сообщениеwild_pig, не работает, если ошибка не в первой строке
_Boroda_, Саш, к сожалению, ввел в заблуждение проставив цифры. На самом деле в колонке текстовые значения. Не подумал, что это важно.
Но второй вариант работает. Но почему? Там же сумма.

Автор - pabchek
Дата добавления - 24.05.2016 в 10:45
_Boroda_ Дата: Вторник, 24.05.2016, 11:06 | Сообщение № 5
Группа: Модераторы
Ранг: Экселист
Сообщений: 9375
Репутация: 3948 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Сумма текста = 0.
Первый вариант как раз не учитывал то, что там или текст, или все нули, или +1,-1,+1,-1
А сумма чего угодно, если в этом что угодно есть ошибка, даст ошибку.

wild_pig, не работает, если ошибка не в первой строке

Не совсем так. Обрати внимание на кусок Cells(1, 1).CurrentRegion - это текущая область относительно ячейки А1. Попробуй нажать F5 - выделить - Текущая область и поймешь сам. Только встань сначала вовнутрь таблицы.


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеСумма текста = 0.
Первый вариант как раз не учитывал то, что там или текст, или все нули, или +1,-1,+1,-1
А сумма чего угодно, если в этом что угодно есть ошибка, даст ошибку.

wild_pig, не работает, если ошибка не в первой строке

Не совсем так. Обрати внимание на кусок Cells(1, 1).CurrentRegion - это текущая область относительно ячейки А1. Попробуй нажать F5 - выделить - Текущая область и поймешь сам. Только встань сначала вовнутрь таблицы.

Автор - _Boroda_
Дата добавления - 24.05.2016 в 11:06
pabchek Дата: Вторник, 24.05.2016, 11:09 | Сообщение № 6
Группа: Проверенные
Ранг: Ветеран
Сообщений: 645
Репутация: 145 ±
Замечаний: 0% ±

Excel 2007
Сумма текста = 0.
Никак не привыкну, что в VBA отличаются результаты от обычных формул. Спасибо большое!


"Учиться, учиться и еще раз учиться!"
WM: R399923528092
 
Ответить
Сообщение
Сумма текста = 0.
Никак не привыкну, что в VBA отличаются результаты от обычных формул. Спасибо большое!

Автор - pabchek
Дата добавления - 24.05.2016 в 11:09
_Boroda_ Дата: Вторник, 24.05.2016, 11:16 | Сообщение № 7
Группа: Модераторы
Ранг: Экселист
Сообщений: 9375
Репутация: 3948 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
в VBA отличаются результаты от обычных формул

Не-не-не,
1.
Код
=СУММ({"й":"ц":"у"})
в Excel даст 0.
2. Слово WorksheetFunction как раз и подразумевает, что мы используем функцию Excel, а не VBA.

И еще я там выше дописал немного, посмотри.


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

Не-не-не,
1.
Код
=СУММ({"й":"ц":"у"})
в Excel даст 0.
2. Слово WorksheetFunction как раз и подразумевает, что мы используем функцию Excel, а не VBA.

И еще я там выше дописал немного, посмотри.

Автор - _Boroda_
Дата добавления - 24.05.2016 в 11:16
pabchek Дата: Вторник, 24.05.2016, 11:28 | Сообщение № 8
Группа: Проверенные
Ранг: Ветеран
Сообщений: 645
Репутация: 145 ±
Замечаний: 0% ±

Excel 2007
Не-не-не
Ну да, млин... Я ж это знаю. Тупанул((( А то, что дописал, да - это я уже понял и принял на вооружение


"Учиться, учиться и еще раз учиться!"
WM: R399923528092
 
Ответить
Сообщение
Не-не-не
Ну да, млин... Я ж это знаю. Тупанул((( А то, что дописал, да - это я уже понял и принял на вооружение

Автор - pabchek
Дата добавления - 24.05.2016 в 11:28
pabchek Дата: Вторник, 24.05.2016, 11:34 | Сообщение № 9
Группа: Проверенные
Ранг: Ветеран
Сообщений: 645
Репутация: 145 ±
Замечаний: 0% ±

Excel 2007
Cells(1, 1).CurrentRegion
Да, я понял. Тем не менее, для пробы я НД-шку перетащил в середину столбца и образовалась пустая ячейка А2. И в этом случае ошибку не ищет.


"Учиться, учиться и еще раз учиться!"
WM: R399923528092


Сообщение отредактировал pabchek - Вторник, 24.05.2016, 11:35
 
Ответить
Сообщение
Cells(1, 1).CurrentRegion
Да, я понял. Тем не менее, для пробы я НД-шку перетащил в середину столбца и образовалась пустая ячейка А2. И в этом случае ошибку не ищет.

Автор - pabchek
Дата добавления - 24.05.2016 в 11:34
_Boroda_ Дата: Вторник, 24.05.2016, 11:57 | Сообщение № 10
Группа: Модераторы
Ранг: Экселист
Сообщений: 9375
Репутация: 3948 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
в этом случае Текущая область нулевая
Не совсем. Текущая область равна одной ячейке - А1.

А вообще если, то CurrentRegion может именно из-за наличия пустых строк/столбцов дать не совсем нужный результат. Поэтому я, например, его практически не использую - у меня-то в таблицах безобразия с пустыми строками-столбцами не бывает, но завтра с моим файлом начнет работать девочка Виолетта (с ресницами длиннее ногтей, которые длиннее пальцев [ногти и пальцы на руках, естественно]) и с файлом может случиться что угодно.

Если очень нужно, то использую
[vba]
Код
    Dim d_ As Range
    ActiveSheet.UsedRange
    Set d_ = Range(Range("A1"), Range("A1").SpecialCells(xlLastCell))
[/vba]
а обычно считаю считаю строки и столбцы там, где точно знаю, что заполнено всегда
[vba]
Код
    Dim d_ As Range
    r1_ = Range("A" & Rows.Count).End(xlUp).Row
    c1_ = Cells(1, Columns.Count).End(xlToLeft).Column
    Set d_ = Range(Cells(1, 1), Cells(r1_, c1_))
[/vba]
В первом куске ActiveSheet.UsedRange очень желательно - эта штука нормализует диапазон. Например, ты написал случайно в ячейке L999 цифру 5, потом понял, что ошибся и стер ее. А таблица у тебя А1:В9 и все, больше ничего на листе нет. Тогда SpecialCells(xlLastCell) даст тебе ячейку L999 (и без разницы, что там уже ничего нет, главное, что было). Убрать это из памяти можно или сохранением файла, или строкой ActiveSheet.UsedRange


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

А вообще если, то CurrentRegion может именно из-за наличия пустых строк/столбцов дать не совсем нужный результат. Поэтому я, например, его практически не использую - у меня-то в таблицах безобразия с пустыми строками-столбцами не бывает, но завтра с моим файлом начнет работать девочка Виолетта (с ресницами длиннее ногтей, которые длиннее пальцев [ногти и пальцы на руках, естественно]) и с файлом может случиться что угодно.

Если очень нужно, то использую
[vba]
Код
    Dim d_ As Range
    ActiveSheet.UsedRange
    Set d_ = Range(Range("A1"), Range("A1").SpecialCells(xlLastCell))
[/vba]
а обычно считаю считаю строки и столбцы там, где точно знаю, что заполнено всегда
[vba]
Код
    Dim d_ As Range
    r1_ = Range("A" & Rows.Count).End(xlUp).Row
    c1_ = Cells(1, Columns.Count).End(xlToLeft).Column
    Set d_ = Range(Cells(1, 1), Cells(r1_, c1_))
[/vba]
В первом куске ActiveSheet.UsedRange очень желательно - эта штука нормализует диапазон. Например, ты написал случайно в ячейке L999 цифру 5, потом понял, что ошибся и стер ее. А таблица у тебя А1:В9 и все, больше ничего на листе нет. Тогда SpecialCells(xlLastCell) даст тебе ячейку L999 (и без разницы, что там уже ничего нет, главное, что было). Убрать это из памяти можно или сохранением файла, или строкой ActiveSheet.UsedRange

Автор - _Boroda_
Дата добавления - 24.05.2016 в 11:57
pabchek Дата: Вторник, 24.05.2016, 12:36 | Сообщение № 11
Группа: Проверенные
Ранг: Ветеран
Сообщений: 645
Репутация: 145 ±
Замечаний: 0% ±

Excel 2007
у меня-то в таблицах безобразия с пустыми строками-столбцами не бывает
У меня тоже, но правда жизни гораздо суровее с ногтеносными девочками. Оттуда же и
xlLastCell
. Кстати, я на это уже натыкался и знаю. На автомате удаляю пустой конец файла с последующим сохранением.
И да, спасибо за код подсчета строк/столбцов!


"Учиться, учиться и еще раз учиться!"
WM: R399923528092
 
Ответить
Сообщение
у меня-то в таблицах безобразия с пустыми строками-столбцами не бывает
У меня тоже, но правда жизни гораздо суровее с ногтеносными девочками. Оттуда же и
xlLastCell
. Кстати, я на это уже натыкался и знаю. На автомате удаляю пустой конец файла с последующим сохранением.
И да, спасибо за код подсчета строк/столбцов!

Автор - pabchek
Дата добавления - 24.05.2016 в 12:36
pabchek Дата: Вторник, 24.05.2016, 12:49 | Сообщение № 12
Группа: Проверенные
Ранг: Ветеран
Сообщений: 645
Репутация: 145 ±
Замечаний: 0% ±

Excel 2007
Да, вот еще натолкнулся, но сам сообразил:
Поскольку
'вычисления
у меня не единственные, и по ходу выполнения макроса нужно учитывать результат последних. Добавил
[vba]
Код
    Err.Clear
[/vba]
Получилось:


"Учиться, учиться и еще раз учиться!"
WM: R399923528092


Сообщение отредактировал pabchek - Вторник, 24.05.2016, 12:52
 
Ответить
СообщениеДа, вот еще натолкнулся, но сам сообразил:
Поскольку
'вычисления
у меня не единственные, и по ходу выполнения макроса нужно учитывать результат последних. Добавил
[vba]
Код
    Err.Clear
[/vba]
Получилось:

Автор - pabchek
Дата добавления - 24.05.2016 в 12:49
_Boroda_ Дата: Вторник, 24.05.2016, 13:33 | Сообщение № 13
Группа: Модераторы
Ранг: Экселист
Сообщений: 9375
Репутация: 3948 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Так у тебя будет пропускать вообще все ошибки. И в Вычислениях_1 тоже. А вдруг там ошибка в коде?
on error resume next в самом начале пишут обычно тогда, когда отдают код кому-то наружу. Чтобы на дебаг не выскакивало в случае чего. Да, в случае ошибки отрабатывать может неверно, но пугать пользователя окном VBA не будет. А при написании/отладке On Error... нужно писать только там, где он действительно необходим.
Возможно, второй вариант из поста 3 все-таки лучше. Что-то типа
[vba]
Код
Sub dd()
    'вычисления 1
    'вычисления 2
    Err.Clear'на всякий случай, вдруг ошибка уже была в коде раньше, мы ее пропустили, но не обнулили
    On Error Resume Next
    a = WorksheetFunction.Sum(Range("A:A"))
    If Err.Number Then
        MsgBox "Есть ошибка"
        Exit Sub
        ' здесь "on error goto 0" не нужно - мы все равно уже вышли из Sub
    End If
    ' здесь "on error goto 0" не нужно - ошибки и так не было
End Sub
[/vba]
Про On Error можно почитать здесь
https://msdn.microsoft.com/ru-ru/library/5hsw66as.aspx


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеТак у тебя будет пропускать вообще все ошибки. И в Вычислениях_1 тоже. А вдруг там ошибка в коде?
on error resume next в самом начале пишут обычно тогда, когда отдают код кому-то наружу. Чтобы на дебаг не выскакивало в случае чего. Да, в случае ошибки отрабатывать может неверно, но пугать пользователя окном VBA не будет. А при написании/отладке On Error... нужно писать только там, где он действительно необходим.
Возможно, второй вариант из поста 3 все-таки лучше. Что-то типа
[vba]
Код
Sub dd()
    'вычисления 1
    'вычисления 2
    Err.Clear'на всякий случай, вдруг ошибка уже была в коде раньше, мы ее пропустили, но не обнулили
    On Error Resume Next
    a = WorksheetFunction.Sum(Range("A:A"))
    If Err.Number Then
        MsgBox "Есть ошибка"
        Exit Sub
        ' здесь "on error goto 0" не нужно - мы все равно уже вышли из Sub
    End If
    ' здесь "on error goto 0" не нужно - ошибки и так не было
End Sub
[/vba]
Про On Error можно почитать здесь
https://msdn.microsoft.com/ru-ru/library/5hsw66as.aspx

Автор - _Boroda_
Дата добавления - 24.05.2016 в 13:33
pabchek Дата: Вторник, 24.05.2016, 14:28 | Сообщение № 14
Группа: Проверенные
Ранг: Ветеран
Сообщений: 645
Репутация: 145 ±
Замечаний: 0% ±

Excel 2007
Так у тебя будет пропускать вообще все ошибки
Не совсем. В файле я смоделировал ситуацию, в которой если On Error... не поставить первым, получается ошибка функции. И если во втором случае не поставить Err.Clear, то запоминается ошибка из первых вычислений и выскакивает MsgBox.
К сообщению приложен файл: 8238817.xlsb(14Kb)


"Учиться, учиться и еще раз учиться!"
WM: R399923528092


Сообщение отредактировал pabchek - Вторник, 24.05.2016, 14:30
 
Ответить
Сообщение
Так у тебя будет пропускать вообще все ошибки
Не совсем. В файле я смоделировал ситуацию, в которой если On Error... не поставить первым, получается ошибка функции. И если во втором случае не поставить Err.Clear, то запоминается ошибка из первых вычислений и выскакивает MsgBox.

Автор - pabchek
Дата добавления - 24.05.2016 в 14:28
_Boroda_ Дата: Вторник, 24.05.2016, 14:31 | Сообщение № 15
Группа: Модераторы
Ранг: Экселист
Сообщений: 9375
Репутация: 3948 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Вот и я как раз именно про то, что
On Error... нужно писать только там, где он действительно необходим


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

Автор - _Boroda_
Дата добавления - 24.05.2016 в 14:31
KuklP Дата: Вторник, 24.05.2016, 15:49 | Сообщение № 16
Группа: Проверенные
Ранг: Старожил
Сообщений: 2006
Репутация: 436 ±
Замечаний: 20% ±

[vba]
Код
Public Sub www()
    On Error Resume Next
    Set r = [A:a].CurrentRegion.SpecialCells(xlCellTypeFormulas, 16)
    If Err = 0 Then MsgBox "ERROR!"
    Err.Clear
End Sub
[/vba]


Ну, с НДС и мы чего-то стoим! kuklp@mail.ru
WM Z206653985942, R334086032478, U238399322728
 
Ответить
Сообщение[vba]
Код
Public Sub www()
    On Error Resume Next
    Set r = [A:a].CurrentRegion.SpecialCells(xlCellTypeFormulas, 16)
    If Err = 0 Then MsgBox "ERROR!"
    Err.Clear
End Sub
[/vba]

Автор - KuklP
Дата добавления - 24.05.2016 в 15:49
Мир MS Excel » Вопросы и решения » Вопросы по VBA » Если диапазон содержит ошибку прервать макрос (Макросы/Sub)
Страница 1 из 11
Поиск:

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