Страницы: 1
RSS
Ошибка при заполнении диапазона формулами, Вываливается в ошибку 1004 - Range of Object _Worksheet failed
 
Доброго времени суток!
Очередной вопрос от нуба.
Есть несколько листов с данными, которые надо обрабатывать подставляя формулы (фактически подстановка критериев для работы формул типа суммеслимн и иже с ней). Количество строк на листах 45 000 строк.
Код работал при количестве строк до 32 000 (переменные шли как Integer). Заменил на Long и ппц(((
Рылся и на форуме и в инете... так и не смог найти объяснение.
Основные части кода (однотипные части не выкладываю, т.к. идет однотипная обработка 6 листов, отличается только количество колонок куда подставляются формулы).
Код
Sub Tchpok()
Application.ScreenUpdating = False
Application.DisplayAlerts = False

Dim wb As Workbook
Set wb = ThisWorkbook

Dim sales As Worksheet
Set sales = wb.Sheets("Продажи")

Dim dz As Worksheet
Set dz = wb.Sheets("ДЗ")

Dim pay As Worksheet
Set pay = wb.Sheets("Оплаты")

Dim i As Long, n As Long, z As Long


i = Application.WorksheetFunction.CountA(sales.Columns(2)) + 5

sales.Range(Cells(8, 1), Cells(i, 1)).FormulaR1C1 = "=IFERROR(INDEX(Оплаты!C1,MATCH(Продажи!RC3,Оплаты!C3,0)),"""")"
    sales.Range(Cells(8, 1), Cells(i, 1)).Copy
    sales.Range(Cells(8, 1), Cells(i, 1)).PasteSpecial xlPasteValues

sales.Range(Cells(8, 17), Cells(i, 17)).FormulaR1C1 = "=IFERROR(INDEX(Менеджер_для_премии,MATCH(RC[-9],Менеджер_в_отчетах,0)),"""")"
    sales.Range(Cells(8, 17), Cells(i, 17)).Copy
    sales.Range(Cells(8, 17), Cells(i, 17)).PasteSpecial xlPasteValues

n = Application.WorksheetFunction.CountA(dz.Columns(1)) + 50

dz.Range(Cells(8, 15), Cells(n, 15)).FormulaR1C1 = "=IFERROR(INDEX(Продажи!C17,MATCH(ДЗ!RC1,Продажи!C3,0)),"""")"
    dz.Range(Cells(8, 15), Cells(n, 15)).Copy
    dz.Range(Cells(8, 15), Cells(n, 15)).PasteSpecial xlPasteValues

dz.Range(Cells(8, 16), Cells(n, 16)).FormulaR1C1 = "=IFERROR(INDEX(Месяц_период,MATCH(RC3,Дата_ДЗ,0)),R[-1]C)"
    dz.Range(Cells(8, 16), Cells(n, 16)).Copy
    dz.Range(Cells(8, 16), Cells(n, 16)).PasteSpecial xlPasteValues

dz.Range(Cells(8, 17), Cells(n, 17)).FormulaR1C1 = "=IFERROR(INDEX(Продажи!C6,MATCH(ДЗ!RC1,Продажи!C3,0)),"""")"
    dz.Range(Cells(8, 17), Cells(n, 17)).Copy
    dz.Range(Cells(8, 17), Cells(n, 17)).PasteSpecial xlPasteValues

z = Application.WorksheetFunction.CountA(pay.Columns(2)) + 50

pay.Range(Cells(7, 17), Cells(z, 17)).FormulaR1C1 = "=IFERROR(INDEX(Месяц_период,MATCH(RC[1],Месяц_период,0)),R[-1]C)"
    pay.Range(Cells(8, 17), Cells(z, 17)).Copy
    pay.Range(Cells(8, 17), Cells(z, 17)).PasteSpecial xlPasteValues
    
Application.ScreenUpdating = True
Application.DisplayAlerts = True
    
End Sub
При выполнении выдает ошибку на этой строчке, и других строках, аналогичных этой:
sales.Range(Cells(8, 1), Cells(i, 1)).FormulaR1C1 = "=IFERROR(INDEX(Оплаты!C1,MATCH(Продажи!RC3,Оплаты!C3,0)),"""")"




Буду очень благодарен, если еще подскажете как обойти вот эту ерунду (подстановку результатов вычисления формулы в ячейку):
 
sales.Range(Cells(8, 1), Cells(i, 1)).Copy
   sales.Range(Cells(8, 1), Cells(i, 1)).PasteSpecial xlPasteValues

Насколько я понимаю можно использовать .Value
Но с синтаксисом туплю((( Никак не могу понять как правильно использовать

Заранее спасибо за помощь и внимание!
 
Зачем Вы вставляете макросом формулу, если затем, тут-же, меняете ее на значение?
Считайте нужное сразу в коде, без 'прокладки' в виде формулы листа, и заносите на лист готовое значение
Согласие есть продукт при полном непротивлении сторон
 
Предположу, что ошибка из-за того, что в момент запуска макроса лист "Продажи" не является активным. Если это так, то могу предложить два варианта решения проблемы.
1. Переходите макросом на лист "Продажи" перед вставкой формул:
Код
sales.Select
sales.Range(Cells(8, 1), Cells(i, 1)).FormulaR1C1 = _
    "=IFERROR(INDEX(Оплаты!C1,MATCH(Продажи!RC3,Оплаты!C3,0)),"""")"

2. Или измените вставку формул так:
Код
sales.Range(sales.Cells(8, 1), sales.Cells(i, 1)).FormulaR1C1 = _
    "=IFERROR(INDEX(Оплаты!C1,MATCH(Продажи!RC3,Оплаты!C3,0)),"""")"

То есть если явно не указывать в скобках лист, то подразумевается активный лист. А за скобками и внутри скобок должен быть один лист.
 
Цитата
Sanja написал: Зачем Вы вставляете макросом формулу, если затем, тут-же, меняете ее на значение?
Формулы индекса с вложенным поиском позиции, когда их, формул, 200 000+++, мягко говоря замедляют работу. Это не учитывая нагрузки в виду вычисляемой части формул отчета итогового и графиков. Другого варианта пока не придумал(((
Немного помогло сохранение файла в бинарном формате. Анализ выгрузок из 1С двух разных организаций. Все ведется в разных базах. Глубина анализа - 12 месяцев от отчетной даты.

Как в коде считать пока не понял. Это был мой второй вопрос )))
Цитата
Karataev написал: Переходите макросом на лист "Продажи" перед вставкой формул
Спасибо! Я только сейчас сообразил, что убрал активацию листа, когда переименовывал Integer в Long, т.к. явно задал имена листов через DIm... Хотя, честно говоря, не особо понятно, почему заковыка в этом выходит. Я специально везде указывал привязку к листу. Впрочем работает и слава богу. Спасибо!
 
Sanja,
пробовал
Код
sales.Range(Cells(8, 1), Cells(i, 1)).value = "=IFERROR(INDEX(Оплаты!C1,MATCH(Продажи!RC3,Оплаты!C3,0)),"""")" 

вставляет формулу в ячейку
Код
sales.Range(Cells(8, 1), Cells(i, 1)).value = "=IFERROR(INDEX(Оплаты!C1,MATCH(Продажи!RC3,Оплаты!C3,0)),"""")" .value

выдает ошибку.но если сделать так
Код
sales.Range(Cells(8, 1), Cells(i, 1)).value = "=IFERROR(INDEX(Оплаты!C1,MATCH(Продажи!RC3,Оплаты!C3,0)),"""")" 
sales.Range(Cells(8, 1), Cells(i, 1)).value =sales.Range(Cells(8, 1), Cells(i, 1)).value 

то все ок...

как то можно все уместить в одну строку?  
 
Цитата
Павел написал:
формул, 200 000+++, мягко говоря замедляют работу
Вам правильно советуют: вычисляйте все программно, без вставки формул - в ячейку сразу результат (значение).
 
Цитата
Юрий М написал:
Вам правильно советуют: вычисляйте все программно, без вставки формул - в ячейку сразу результат (значение).
В том то и проблема, что не особо понимаю как. Я обращал внимание на этот свой недостаток(((

Через Application.WorksheetFunction для меня пока сложновато. Через стандартные формулы единственный путь, работающий, который нашел, описал в своем предыдущем посте.
Буду благодарен, если подскажете как... ну или направите мои поиски в нужном направлении ссылкой на ресурс. Пока для меня финансово-экономический анализ и прогноз на несколько порядков проще чем VBA
 
Чтобы что-то подсказать, нужно понимать задачу, иметь исходные данные и видеть желаемый результат. Ничего из этого у потенциальных помощников нет )
Создайте небольшой файл-пример с аналогичной структурой, в нём немного данных и опишите задачу. Не формулы, которые Вы используете, а ЗАДАЧУ.
 
Возможно некоторое косоязычие меня накрыло в конце рабочей недели(((

Проблема одна - синтаксис команды для подстановки результата вычисления формулы в ячейку вместо самой формулы.

Иными словами, эти две строки
Код
sales.Range(Cells(8, 1), Cells(i, 1)).value = "=IFERROR(INDEX(Оплаты!C1,MATCH(Продажи!RC3,Оплаты!C3,0)),"""")"
sales.Range(Cells(8, 1), Cells(i, 1)).value =sales.Range(Cells(8, 1), Cells(i, 1)).value

возможно ли заменить одной?
Или без вариантов - учить матчасть, связанную с Application.WorksheetFunction?
 
Цитата
Павел написал: Проблема одна - синтаксис команды для подстановки результата вычисления формулы в ячейку
Проблема одна - в использовании формулы там, где можно (а в Вашем случае нужно) обойтись без нее. Но без файла-примера и описания ЗАДАЧИ, а не СПОСОБА, которым ВЫ ее пытаетесь решить, помочь очень сложно.
Вопрос должен звучать примерно так - Найти какие-то данные там-то и там-то, по такому-то условию, и вставить их вот сюда.
А пока обдумываете, почитайте про метод .Find, может и надобность в помощниках отпадет
Согласие есть продукт при полном непротивлении сторон
 
Sanja, спасибо!
Наверное излишне зациклился на формулах)))
 
Цитата
Павел написал: спасибо!
Пожалуйста, только за что? Помогла инфа про Find?
Согласие есть продукт при полном непротивлении сторон
 
Пока в процессе. Трудновато воспринимается пока(((
Страницы: 1
Наверх