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

Вход

Регистрация

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

 

= Мир MS Excel/Макрос не сортирует (вылетает) после перехода на офис 2016 - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по VBA » Макрос не сортирует (вылетает) после перехода на офис 2016 (Макросы/Sub)
Макрос не сортирует (вылетает) после перехода на офис 2016
ABSh Дата: Среда, 15.01.2020, 13:57 | Сообщение № 1
Группа: Пользователи
Ранг: Прохожий
Сообщений: 6
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Здравствуйте уважаемые форумчане!
Прошу помощи в правке макроса. Сам имею зачаточные знания ВБА, поэтому в основном записываю небольшие макросы через встроенный макрорекордер, могу слегка подкорректировать :(
Смысл макроса простой - приводит открытый файл в нужный вид, удаляя некоторые столбцы, вытаскивает из эталонного файла значения для сравнения, сортирует выделенное по дате/времени и копирует в новый файл.
После смены компьютера и перехода на офис 2016 макрос стал вылетать на этапе сортировки, на строчке"ActiveWorkbook.ActiveSheet.Sort.SortFields.Clear", с ошибкой:
Run-tine error '9':
Subscript out of range

Прилагаю код
[vba]
Код
Sub withError()
'
' withError Макрос
'

'
    Columns("B:C").Select
    Selection.Delete Shift:=xlToLeft
    Columns("C:E").Select
    Selection.Delete Shift:=xlToLeft
    Columns("G:M").Select
    Selection.Delete Shift:=xlToLeft
    Columns("F:F").Select
    Selection.Cut
    Columns("B:B").Select
    Selection.Insert Shift:=xlToRight
    
    Range("G11").Select
    ActiveCell.FormulaR1C1 = "=ROUND((RC[-6]*1)&(RC[-5]*1),7)"
    Range("H11").Select
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC7,'C:\[Медиаскоп текущий.xlsx]Лист1'!C1:C29,2,0)"
    Range("I11").Select
    Selection.NumberFormat = "[h]:mm:ss;@"
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC7,'C:\[Медиаскоп текущий.xlsx]Лист1'!C1:C29,9,0)"
    Range("J11").Select
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC7,'C:\[Медиаскоп текущий.xlsx]Лист1'!C1:C29,11,0)"
    
    lr = Cells(Rows.Count, 2).End(xlUp).Row
    Range("G11:J11").AutoFill Destination:=Range("G11:J" & lr), Type:=xlFillDefault
    Range("E11").Select
    Selection.NumberFormat = "General"
    ActiveCell.FormulaR1C1 = _
        "=IF(RC[5]=""Ролик"",""Ролик"",IF(RC[5]=""Спонсорская заставка"",""Спонсорская заставка"",IF(RC[5]=""Анонс: спонсорская заставка"",""Спонсорская заставка"",""Спонсор показа"")))"
    Range("E11").AutoFill Destination:=Range("E11:E" & lr), Type:=xlFillDefault
    
    
    Range("A11:F" & lr).Select
    ActiveWorkbook.ActiveSheet.Sort.SortFields.Clear      
    ActiveWorkbook.ActiveSheet.Sort.SortFields.Add Key:=Range("A11:A" & lr), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortTextAsNumbers
    ActiveWorkbook.ActiveSheet.Sort.SortFields.Add Key:=Range("B11:B" & lr), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortTextAsNumbers
    With ActiveWorkbook.ActiveSheet.Sort
        .SetRange Range("A10:F" & lr)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

    
    Selection.Copy
    Workbooks.Open Filename:="C:\Рыба1.xlsx"
    Range("B2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
    ler = Cells(Rows.Count, 2).End(xlUp).Row
    lep = Cells(Rows.Count, 1).End(xlUp).Row
    Rows((ler + 1) & ":" & lep).Select
    Selection.Delete Shift:=xlUp
    Range("A1:G" & (ler + 2)).Select
    
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=ЕОШИБКА(A1)"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False

End Sub
[/vba]
 
Ответить
СообщениеЗдравствуйте уважаемые форумчане!
Прошу помощи в правке макроса. Сам имею зачаточные знания ВБА, поэтому в основном записываю небольшие макросы через встроенный макрорекордер, могу слегка подкорректировать :(
Смысл макроса простой - приводит открытый файл в нужный вид, удаляя некоторые столбцы, вытаскивает из эталонного файла значения для сравнения, сортирует выделенное по дате/времени и копирует в новый файл.
После смены компьютера и перехода на офис 2016 макрос стал вылетать на этапе сортировки, на строчке"ActiveWorkbook.ActiveSheet.Sort.SortFields.Clear", с ошибкой:
Run-tine error '9':
Subscript out of range

Прилагаю код
[vba]
Код
Sub withError()
'
' withError Макрос
'

'
    Columns("B:C").Select
    Selection.Delete Shift:=xlToLeft
    Columns("C:E").Select
    Selection.Delete Shift:=xlToLeft
    Columns("G:M").Select
    Selection.Delete Shift:=xlToLeft
    Columns("F:F").Select
    Selection.Cut
    Columns("B:B").Select
    Selection.Insert Shift:=xlToRight
    
    Range("G11").Select
    ActiveCell.FormulaR1C1 = "=ROUND((RC[-6]*1)&(RC[-5]*1),7)"
    Range("H11").Select
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC7,'C:\[Медиаскоп текущий.xlsx]Лист1'!C1:C29,2,0)"
    Range("I11").Select
    Selection.NumberFormat = "[h]:mm:ss;@"
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC7,'C:\[Медиаскоп текущий.xlsx]Лист1'!C1:C29,9,0)"
    Range("J11").Select
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC7,'C:\[Медиаскоп текущий.xlsx]Лист1'!C1:C29,11,0)"
    
    lr = Cells(Rows.Count, 2).End(xlUp).Row
    Range("G11:J11").AutoFill Destination:=Range("G11:J" & lr), Type:=xlFillDefault
    Range("E11").Select
    Selection.NumberFormat = "General"
    ActiveCell.FormulaR1C1 = _
        "=IF(RC[5]=""Ролик"",""Ролик"",IF(RC[5]=""Спонсорская заставка"",""Спонсорская заставка"",IF(RC[5]=""Анонс: спонсорская заставка"",""Спонсорская заставка"",""Спонсор показа"")))"
    Range("E11").AutoFill Destination:=Range("E11:E" & lr), Type:=xlFillDefault
    
    
    Range("A11:F" & lr).Select
    ActiveWorkbook.ActiveSheet.Sort.SortFields.Clear      
    ActiveWorkbook.ActiveSheet.Sort.SortFields.Add Key:=Range("A11:A" & lr), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortTextAsNumbers
    ActiveWorkbook.ActiveSheet.Sort.SortFields.Add Key:=Range("B11:B" & lr), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortTextAsNumbers
    With ActiveWorkbook.ActiveSheet.Sort
        .SetRange Range("A10:F" & lr)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

    
    Selection.Copy
    Workbooks.Open Filename:="C:\Рыба1.xlsx"
    Range("B2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
    ler = Cells(Rows.Count, 2).End(xlUp).Row
    lep = Cells(Rows.Count, 1).End(xlUp).Row
    Rows((ler + 1) & ":" & lep).Select
    Selection.Delete Shift:=xlUp
    Range("A1:G" & (ler + 2)).Select
    
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=ЕОШИБКА(A1)"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False

End Sub
[/vba]

Автор - ABSh
Дата добавления - 15.01.2020 в 13:57
ABSh Дата: Среда, 15.01.2020, 13:59 | Сообщение № 2
Группа: Пользователи
Ранг: Прохожий
Сообщений: 6
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
ОГО!
Прошу прощения, код нечитаем.
На предварительном просмотре все выглядит как надо.
Подскажите, что не так?
 
Ответить
СообщениеОГО!
Прошу прощения, код нечитаем.
На предварительном просмотре все выглядит как надо.
Подскажите, что не так?

Автор - ABSh
Дата добавления - 15.01.2020 в 13:59
Pelena Дата: Среда, 15.01.2020, 14:04 | Сообщение № 3
Группа: Админы
Ранг: Местный житель
Сообщений: 18551
Репутация: 4200 ±
Замечаний: ±

Excel 2016 & Mac Excel
Код надо оформлять с помощью кнопки #. Исправила на первый раз)


"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
СообщениеКод надо оформлять с помощью кнопки #. Исправила на первый раз)

Автор - Pelena
Дата добавления - 15.01.2020 в 14:04
Pelena Дата: Среда, 15.01.2020, 14:27 | Сообщение № 4
Группа: Админы
Ранг: Местный житель
Сообщений: 18551
Репутация: 4200 ±
Замечаний: ±

Excel 2016 & Mac Excel
приводит открытый файл в нужный вид, удаляя некоторые столбцы...
не хотите попробовать без макроса сделать, с помощью Power Query? Тем более
После смены компьютера и перехода на офис 2016

В любом случае нужно видеть структуру файлов


"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
Сообщение
приводит открытый файл в нужный вид, удаляя некоторые столбцы...
не хотите попробовать без макроса сделать, с помощью Power Query? Тем более
После смены компьютера и перехода на офис 2016

В любом случае нужно видеть структуру файлов

Автор - Pelena
Дата добавления - 15.01.2020 в 14:27
ABSh Дата: Среда, 15.01.2020, 14:37 | Сообщение № 5
Группа: Пользователи
Ранг: Прохожий
Сообщений: 6
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Честно говоря Power Query для меня еще более темный лес, чем VBA :(
Макрос использовался для проверки и быстрого приведения к "отчетному" виду большого количества однотипных файлов.
Файлы примера и эталонный прилагаю.
Количество записей может колебаться от одной до 2-3 тысяч.
К сообщению приложен файл: 7413155.xlsx(11.8 Kb) · 9175846.xlsx(59.6 Kb)
 
Ответить
СообщениеЧестно говоря Power Query для меня еще более темный лес, чем VBA :(
Макрос использовался для проверки и быстрого приведения к "отчетному" виду большого количества однотипных файлов.
Файлы примера и эталонный прилагаю.
Количество записей может колебаться от одной до 2-3 тысяч.

Автор - ABSh
Дата добавления - 15.01.2020 в 14:37
Pelena Дата: Среда, 15.01.2020, 15:51 | Сообщение № 6
Группа: Админы
Ранг: Местный житель
Сообщений: 18551
Репутация: 4200 ±
Замечаний: ±

Excel 2016 & Mac Excel
У меня не возникает ошибки при сортировке.
Загляните в редакторе VBA в меню Tools -- References. Если есть строчки с MISSING, уберите с них галки.
Ну, и для ускорения работы часть макроса до сортировки лучше немного сократить
[vba]
Код
Range("B:C,E:G,L:P").Delete Shift:=xlToLeft
    Columns("F:F").Cut
    Columns("B:B").Insert Shift:=xlToRight
    lr = Cells(Rows.Count, 2).End(xlUp).Row
    Range("G11:G" & lr).FormulaR1C1 = "=ROUND((RC[-6]*1)&(RC[-5]*1),7)"
    Range("H11:H" & lr).FormulaR1C1 = _
        "=VLOOKUP(RC7,'C:\[Медиаскоп текущий.xlsx]Лист1'!C1:C29,2,0)"
    Range("I11:I" & lr).NumberFormat = "[h]:mm:ss;@"
    Range("I11:I" & lr).FormulaR1C1 = _
        "=VLOOKUP(RC7,'C:\[Медиаскоп текущий.xlsx]Лист1'!C1:C29,9,0)"
    Range("J11:J" & lr).FormulaR1C1 = _
        "=VLOOKUP(RC7,'C:\[Медиаскоп текущий.xlsx]Лист1'!C1:C29,11,0)"
    Range("E11").NumberFormat = "General"
    Range("E11:E" & lr).FormulaR1C1 = _
        "=IF(RC[5]=""Ролик"",""Ролик"",IF(RC[5]=""Спонсорская заставка"",""Спонсорская заставка"",IF(RC[5]=""Анонс: спонсорская заставка"",""Спонсорская заставка"",""Спонсор показа"")))"
[/vba]


"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
СообщениеУ меня не возникает ошибки при сортировке.
Загляните в редакторе VBA в меню Tools -- References. Если есть строчки с MISSING, уберите с них галки.
Ну, и для ускорения работы часть макроса до сортировки лучше немного сократить
[vba]
Код
Range("B:C,E:G,L:P").Delete Shift:=xlToLeft
    Columns("F:F").Cut
    Columns("B:B").Insert Shift:=xlToRight
    lr = Cells(Rows.Count, 2).End(xlUp).Row
    Range("G11:G" & lr).FormulaR1C1 = "=ROUND((RC[-6]*1)&(RC[-5]*1),7)"
    Range("H11:H" & lr).FormulaR1C1 = _
        "=VLOOKUP(RC7,'C:\[Медиаскоп текущий.xlsx]Лист1'!C1:C29,2,0)"
    Range("I11:I" & lr).NumberFormat = "[h]:mm:ss;@"
    Range("I11:I" & lr).FormulaR1C1 = _
        "=VLOOKUP(RC7,'C:\[Медиаскоп текущий.xlsx]Лист1'!C1:C29,9,0)"
    Range("J11:J" & lr).FormulaR1C1 = _
        "=VLOOKUP(RC7,'C:\[Медиаскоп текущий.xlsx]Лист1'!C1:C29,11,0)"
    Range("E11").NumberFormat = "General"
    Range("E11:E" & lr).FormulaR1C1 = _
        "=IF(RC[5]=""Ролик"",""Ролик"",IF(RC[5]=""Спонсорская заставка"",""Спонсорская заставка"",IF(RC[5]=""Анонс: спонсорская заставка"",""Спонсорская заставка"",""Спонсор показа"")))"
[/vba]

Автор - Pelena
Дата добавления - 15.01.2020 в 15:51
ABSh Дата: Среда, 15.01.2020, 17:05 | Сообщение № 7
Группа: Пользователи
Ранг: Прохожий
Сообщений: 6
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Благодарю за подсказку о сокращении макроса!
В меню Tools -- References галки стоят всего на 4-х пунктах, с MISSING отсутствуют. Может быть какие-то галки не стоят?
Попытался заменить конструкцию
[vba]
Код
ActiveWorkbook.ActiveSheet
[/vba]
на конструкцию
[vba]
Код
Application.ActiveCell.Parent
[/vba]
все равно останавливается ровно в том же месте :(
 
Ответить
СообщениеБлагодарю за подсказку о сокращении макроса!
В меню Tools -- References галки стоят всего на 4-х пунктах, с MISSING отсутствуют. Может быть какие-то галки не стоят?
Попытался заменить конструкцию
[vba]
Код
ActiveWorkbook.ActiveSheet
[/vba]
на конструкцию
[vba]
Код
Application.ActiveCell.Parent
[/vba]
все равно останавливается ровно в том же месте :(

Автор - ABSh
Дата добавления - 15.01.2020 в 17:05
ABSh Дата: Среда, 15.01.2020, 18:54 | Сообщение № 8
Группа: Пользователи
Ранг: Прохожий
Сообщений: 6
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Pelena, спасибо за помощь в корректировке макроса!
А исходную проблему помогли решить на дружественном форуме, подменив конструкцию

[vba]
Код
ActiveWorkbook.ActiveSheet
[/vba]
на переменную

[vba]
Код
Dim ws as worksheet  
set ws = activecell.worksheet
[/vba]
Буду благодарен, если кто-то может объяснить почему в 16 офисе перестала работать предыдущая <_<
 
Ответить
СообщениеPelena, спасибо за помощь в корректировке макроса!
А исходную проблему помогли решить на дружественном форуме, подменив конструкцию

[vba]
Код
ActiveWorkbook.ActiveSheet
[/vba]
на переменную

[vba]
Код
Dim ws as worksheet  
set ws = activecell.worksheet
[/vba]
Буду благодарен, если кто-то может объяснить почему в 16 офисе перестала работать предыдущая <_<

Автор - ABSh
Дата добавления - 15.01.2020 в 18:54
Pelena Дата: Среда, 15.01.2020, 18:57 | Сообщение № 9
Группа: Админы
Ранг: Местный житель
Сообщений: 18551
Репутация: 4200 ±
Замечаний: ±

Excel 2016 & Mac Excel
Вообще, да ActiveWorkbook.ActiveSheet - масло масляное, достаточно просто ActiveSheet


"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
СообщениеВообще, да ActiveWorkbook.ActiveSheet - масло масляное, достаточно просто ActiveSheet

Автор - Pelena
Дата добавления - 15.01.2020 в 18:57
boa Дата: Четверг, 16.01.2020, 16:47 | Сообщение № 10
Группа: Друзья
Ранг: Ветеран
Сообщений: 542
Репутация: 165 ±
Замечаний: 0% ±

2013, 365


 
Ответить
Сообщениекросс: https://www.excel-vba.ru/forum/index.php?topic=6172.0

Автор - boa
Дата добавления - 16.01.2020 в 16:47
Мир MS Excel » Вопросы и решения » Вопросы по VBA » Макрос не сортирует (вылетает) после перехода на офис 2016 (Макросы/Sub)
  • Страница 1 из 1
  • 1
Поиск:

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