Доброго всем вечера! Во вложении пример, в котором используется формула массива для расчетов в столбце "Н". При ручном введении этой формулы в ячейку столбца, она работает корректно и без ошибок. При ручном распространении вниз методом CTRL+D при включенном фильтре, формула корректно вставляется в только видимые ячейки выделенного диапазона, как и положено. Но та же формула выдает ошибку при вставке через код VBA. Я смог релизовать данный алгоритм в VBA и он работает, но код получился слишком "топорным" и остались вопросы. Почему формула выдет ошибку при вставлении в ячейку через код? И как распрстранить эту формулу массива по столбцу с включенным фильтром только в видимые ячейки? Вырианты кода которые я использовал для этого - не работают (приведены в примере 'зеленым цветом примечаний Макрос вставлен в модуль листа и при клике на ячейку с надписью "Заказ", срабатывает фильтр и производятся расчеты. Может кто подскажет, как оптимизировать код и в чем моя ошибка? Зранее благодарен за ответ. С Увжением
Доброго всем вечера! Во вложении пример, в котором используется формула массива для расчетов в столбце "Н". При ручном введении этой формулы в ячейку столбца, она работает корректно и без ошибок. При ручном распространении вниз методом CTRL+D при включенном фильтре, формула корректно вставляется в только видимые ячейки выделенного диапазона, как и положено. Но та же формула выдает ошибку при вставке через код VBA. Я смог релизовать данный алгоритм в VBA и он работает, но код получился слишком "топорным" и остались вопросы. Почему формула выдет ошибку при вставлении в ячейку через код? И как распрстранить эту формулу массива по столбцу с включенным фильтром только в видимые ячейки? Вырианты кода которые я использовал для этого - не работают (приведены в примере 'зеленым цветом примечаний Макрос вставлен в модуль листа и при клике на ячейку с надписью "Заказ", срабатывает фильтр и производятся расчеты. Может кто подскажет, как оптимизировать код и в чем моя ошибка? Зранее благодарен за ответ. С Увжениемmachodg
Спасибо, но к сожалению нужно макросом, т.к. этот фаил пересылается "чайникам", которые должны лишь в столбец "Н" вписать количества и дальше посмотреть на результат путем всего лишь клика на ячейку "Заказ". (на самом деле этот фаил - небольшой фрагмент от большой статистической системы)
Желаю доброй ночи.
Спасибо, но к сожалению нужно макросом, т.к. этот фаил пересылается "чайникам", которые должны лишь в столбец "Н" вписать количества и дальше посмотреть на результат путем всего лишь клика на ячейку "Заказ". (на самом деле этот фаил - небольшой фрагмент от большой статистической системы)
Спасибо, что заинтересовались моим вопросом. Да, действительно, формула в моем примере и макрос при клике на ячейке "Заказ " - работают корректно. Но я хотел повысить свои знания VBA с помощью знатоков эксела. Я хотел оптимизировать код и выяснить как составить грамотный код, вставляющий формулу массива в видимые ячейки фключенного фильтра. Для этого я через код вставил ту же формулу, но в простом виде (без FormulaArray =) в первую ячейку диапазона, распространил на весь доапазон методом Selection.SpecialCells(xlCellTypeVisible).FillDown и далее "топорно" использовал в цикле: [SendKeys "{F2}^+~"], т.е. SHIFT+CTRL+ENTER для перевода постой формулы в формулу массива. (посмотрите код в примере):
[vba]
Код
Set Myrange = Application.Range(Cells(FirstRow, 8), Cells(LastRow, 8))
For Each c In Myrange.SpecialCells(xlVisible) c.Select With c SendKeys "{F2}^+~" DoEvents End With Next c
[/vba] Хочу правильно это сделать. На форумах нашел конструкции типа: [vba]
[/vba] - не работают с этой формулой. В чем проблема не могу понять? Есть идеи? Заранее благодарен Доброго дня.
Спасибо, что заинтересовались моим вопросом. Да, действительно, формула в моем примере и макрос при клике на ячейке "Заказ " - работают корректно. Но я хотел повысить свои знания VBA с помощью знатоков эксела. Я хотел оптимизировать код и выяснить как составить грамотный код, вставляющий формулу массива в видимые ячейки фключенного фильтра. Для этого я через код вставил ту же формулу, но в простом виде (без FormulaArray =) в первую ячейку диапазона, распространил на весь доапазон методом Selection.SpecialCells(xlCellTypeVisible).FillDown и далее "топорно" использовал в цикле: [SendKeys "{F2}^+~"], т.е. SHIFT+CTRL+ENTER для перевода постой формулы в формулу массива. (посмотрите код в примере):
[vba]
Код
Set Myrange = Application.Range(Cells(FirstRow, 8), Cells(LastRow, 8))
For Each c In Myrange.SpecialCells(xlVisible) c.Select With c SendKeys "{F2}^+~" DoEvents End With Next c
[/vba] Хочу правильно это сделать. На форумах нашел конструкции типа: [vba]
Кастати, на предварительном просмотре моего ответа, код. вставленный в тег "Код VBA" выглядел нормально, а на форуме - в одну сторчку. В идимо неправильно вставляю. Вот в эту конструкцию тега- [vba][code][/code][/vba], куда именно нужно вставлять код, чтобы он выглядел нормально? Подскажите пожалуйста С уважением
Кастати, на предварительном просмотре моего ответа, код. вставленный в тег "Код VBA" выглядел нормально, а на форуме - в одну сторчку. В идимо неправильно вставляю. Вот в эту конструкцию тега- [vba][code][/code][/vba], куда именно нужно вставлять код, чтобы он выглядел нормально? Подскажите пожалуйста С уважениемmachodg
machodg, Вы используете ФООРМУЛУ В R1C1 стиле и это правильно с точки зрения относительных ссылок, но FormulaArray ждет формулу в A1 стиле. возможно вам следует преобразовать её сперва, а потом уже вставлять, после чего и Fill Down пройдет для получения адресов можно использовать range.address получить адрес из номера строки и столбца с учетом стиля и абсолютных адресов,
machodg, Вы используете ФООРМУЛУ В R1C1 стиле и это правильно с точки зрения относительных ссылок, но FormulaArray ждет формулу в A1 стиле. возможно вам следует преобразовать её сперва, а потом уже вставлять, после чего и Fill Down пройдет для получения адресов можно использовать range.address получить адрес из номера строки и столбца с учетом стиля и абсолютных адресов,bmv98rus
Замечательный Временно просто медведь , процентов на 20.
Сообщение отредактировал bmv98rus - Понедельник, 04.12.2017, 20:16
Sub tt() Application.ScreenUpdating = 0 Application.Calculation = xlCalculationManual With ActiveSheet.AutoFilter.Range For Each r In .Rows(2).Columns(8).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).Cells r.FormulaArray = _ "=SUM(IF(R3C[-5]:R2186C[-5]=RC[-5],R3C[-2]:R2186C[-2]/R3C[-3]:R2186C[-3]*R3C[-1]:R2186C[-1]))/(SUM(IF(R3C[-5]:R2186C[-5]<>"""",R3C[-2]:R2186C[-2]/R3C[-3]:R2186C[-3]*R3C[-1]:R2186C[-1])))" Next End With Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = 1 End Sub
[/vba]
Так нужно? В свой макрос сами вставьте [vba]
Код
Sub tt() Application.ScreenUpdating = 0 Application.Calculation = xlCalculationManual With ActiveSheet.AutoFilter.Range For Each r In .Rows(2).Columns(8).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).Cells r.FormulaArray = _ "=SUM(IF(R3C[-5]:R2186C[-5]=RC[-5],R3C[-2]:R2186C[-2]/R3C[-3]:R2186C[-3]*R3C[-1]:R2186C[-1]))/(SUM(IF(R3C[-5]:R2186C[-5]<>"""",R3C[-2]:R2186C[-2]/R3C[-3]:R2186C[-3]*R3C[-1]:R2186C[-1])))" Next End With Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = 1 End Sub
Огромное всем спасибо!!! Особенно Вам Nic70y, и _Boroda_. Вся проблема видимо была в моей формуле. Формула оказывается не полностью удовлетворяла правилам написания формулы массива. Благодаря Вашему варианту, окончательный код получился очень шустрый. Чуть допилил и сработала процедура: [vba]
[/vba] Соответственно и цикл по заполнению ячеек не понадобился:
[vba]
Код
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim FirstRow, LastRow As Integer
If Target.Address = "$G$2" And Cells(1, 1) = 1 Then Cells(1, 1) = 0 Application.ScreenUpdating = 0 With Application .Calculation = xlAutomatic .MaxChange = 0.001 End With
If ActiveSheet.FilterMode = True Then ActiveSheet.ShowAllData End If
If Cells(3, 7) = 0 Or Cells(3, 7) = "" Then FirstRow = Range("G2").End(xlDown).Row Else FirstRow = 3 End If LastRow = Columns(7).Rows(1040000).End(xlUp).Row
End If Cells(1, 1) = 1 Application.ScreenUpdating = 1 End Sub
[/vba]
Еще раз большое спасибо. Всем желаю успехов.
Огромное всем спасибо!!! Особенно Вам Nic70y, и _Boroda_. Вся проблема видимо была в моей формуле. Формула оказывается не полностью удовлетворяла правилам написания формулы массива. Благодаря Вашему варианту, окончательный код получился очень шустрый. Чуть допилил и сработала процедура: [vba]
[/vba] Соответственно и цикл по заполнению ячеек не понадобился:
[vba]
Код
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim FirstRow, LastRow As Integer
If Target.Address = "$G$2" And Cells(1, 1) = 1 Then Cells(1, 1) = 0 Application.ScreenUpdating = 0 With Application .Calculation = xlAutomatic .MaxChange = 0.001 End With
If ActiveSheet.FilterMode = True Then ActiveSheet.ShowAllData End If
If Cells(3, 7) = 0 Or Cells(3, 7) = "" Then FirstRow = Range("G2").End(xlDown).Row Else FirstRow = 3 End If LastRow = Columns(7).Rows(1040000).End(xlUp).Row