Ошибка 1004 в макросе excel 2010

[Eddi]

Добрый день,

Пытаюсь сделать макрос который переносит данные с одного листа на другой, при этом группируя данные и вставляя промежуточные итоги.

При выполнении возникает ошибка "run time error 1004: Application-defined or object-define error" в строке:

Worksheets(1).Cells(i + shapka + 1, NEx).FormulaLocal = "=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;R[" & -i + kBase - 1 & "]C:R[-1]C"

Сам макрос:

Sub formating()

Dim i, j, WidEx, NQu, NEx, kLine, kBase, x, s, shapka As Integer
Dim fileXLT, first, second As String

WidEx = 130

shapka = 8 'высота шапки
kLine = 0
kBase = 0
i = 0
 
 Do
        
        ' Первый уровень аналитики
        If Worksheets(1).Cells(i + shapka + 1, 2) <> Worksheets(1).Cells(i + shapka, 2) Then
        Worksheets(2).Range(Worksheets(2).Cells(i - kLine + 2, 1), Worksheets(2).Cells(i - kLine + 2, WidEx)).Copy
        Worksheets(1).Range(Worksheets(1).Cells(i + shapka + 1, 1), Worksheets(1).Cells(i + shapka + 1, WidEx)).Insert shift:=xlDown
        i = i + 1
        Excel.Application.Range(Worksheets(1).Cells(kLine + shapka, 1), Worksheets(1).Cells(i - 1 + shapka, 1)).EntireRow.Group
        'Worksheets(1).Cells(i + shapka, 2) = i - kLine + 2
        Worksheets(1).Cells(i + shapka, 2) = Worksheets(1).Cells(i + shapka - 1, 2)
        kLine = i + 1
        ' второй уровень аналитики
              If Worksheets(1).Cells(i + shapka + 1, 1) <> Worksheets(1).Cells(i + shapka - 1, 1) Then
              Worksheets(2).Range(Worksheets(2).Cells(57, 1), Worksheets(2).Cells(57, WidEx)).Copy
              Worksheets(1).Range(Worksheets(1).Cells(i + shapka + 1, 1), Worksheets(1).Cells(i + shapka + 1, WidEx)).Insert shift:=xlDown
              NEx = 5 'вставляем промежуточные итоги
                     Do
                      If NEx = 11 Or NEx = 15 Or NEx = 18 Or NEx = 32 Or NEx = 34 Or NEx = 40 Or NEx = 44 Or NEx = 47 Or NEx = 61 Or NEx = 63 Then NEx = NEx + 1
                      If NEx = 28 Or NEx = 57 Then NEx = NEx + 3
                      If NEx = 64 Then NEx = NEx + 21
                      If NEx = 96 Then NEx = NEx + 20
                      Worksheets(1).Cells(i + shapka + 1, NEx).FormulaLocal = "=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;R[" & -i + kBase - 1 & "]C:R[-1]C"
                      NEx = NEx + 1
                     Loop Until NEx > WidEx
              i = i + 1
              Worksheets(1).Cells(i + shapka, 1) = Worksheets(1).Cells(i + shapka - 2, 1)
              kBase = i + 1
              kLine = kLine + 1
              End If
        End If
 i = i + 1
 Loop Until Worksheets(1).Cells(i + shapka, 2) = 0
 
 
 'FINAL TOTAL
 Worksheets(2).Range(Worksheets(2).Cells(56, 1), Worksheets(2).Cells(56, WidEx)).Copy
 Worksheets(1).Range(Worksheets(1).Cells(i + shapka + 1, 1), Worksheets(1).Cells(i + shapka + 1, WidEx)).Insert shift:=xlDown
                   NEx = 5
                     Do
                     If NEx = 11 Or NEx = 15 Or NEx = 18 Or NEx = 32 Or NEx = 34 Or NEx = 40 Or NEx = 44 Or NEx = 47 Or NEx = 61 Or NEx = 63 Then NEx = NEx + 1
                      If NEx = 28 Or NEx = 57 Then NEx = NEx + 3
                      If NEx = 64 Then NEx = NEx + 21
                      If NEx = 96 Then NEx = NEx + 20
                      Worksheets(1).Cells(i + shapka + 1, NEx).FormulaLocal = "=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;R[" & -i - 1 & "]C:R[-1]C"
                      NEx = NEx + 1
                     Loop Until NEx > WidEx
 

Worksheets(1).PageSetup.PrintArea = "$A$1:$DZ$" & i + shapka + 1 & ""

End Sub

Прдскажите пожалуйста, как это исправить?

[Казанский]

Не подскажу, как исправить, а подскажу, как найти проблему.
Когда произойдет ошибка, выясните значение переменных в окне Locals (View - Locals Window) или просто наведя курсор на переменную i, shapka и т.д.
Можете распечатать формулу, которая создается: выделите и скопируйте фрагмент

"=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;R[" & -i + kBase - 1 & "]C:R[-1]C"

, перейдите в окно Immediate (Ctrl+G), нажмите ? Ctrl+V <Enter>
Проходите программу по шагам (F8).

[Hugo121]

А не нужно ли FormulaR1C1Local ?

[Eddi]

Сообщение от Казанский

Когда произойдет ошибка, выясните значение переменных в окне Locals (View - Locals Window) или просто наведя курсор на переменную i, shapka и т.д.

Первым делом при обнаружении ошибки в режиме трассировки по коду начинаю ползать. В формуле все переменные проинициализированы, значения правильные. Проблема не в этом.

Сообщение от Hugo121

А не нужно ли FormulaR1C1Local ?

Ни какой разницы нет, по моему, другие формулы прекрасно встают и с FormulaR1C1Local и с FormulaLocal.

Во вложении пример выгружаемого отчета с макросом и то, что в итоге должно получиться.
Мне думается что я неправильно обращаюсь к ячейке, но как это сделать правильно до сих пор не понял.

[EducatedFool]

Ни какой разницы нет, по моему, другие формулы прекрасно встают и с FormulaR1C1Local и с FormulaLocal.

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

исправьте в коде, и проверьте, будет ли ошибка


PS: гораздо быстрее формулу записывать сразу в несколько ячеек (диапазон),
а не перебирая ячейки в цикле

[Eddi]

Ошибка остается при обоих вариантах FormulaLocal и FormulaR1C1Local.
Пробовал менять резделитель с ";" на "," также не помогло.

[EducatedFool]

перед проблемной строкой добавьте строку

Debug.Print "=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;R[" & -i - 1 & "]C:R[-1]C"

увидите в окне Immediate:

=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;R[-5]C:R[-1]C

Вам не кажется, что в формуле не хватает закрывающейся скобки в конце?