Как оптимизировать?

[А)-(дрей]

Подскажите пожалуйста, как правильно оптимизировать код для ускорения подсчетов. Проблема такая - во вложении есть файл с написанным кодом для подсчета значений. Хотелось бы оптимизировать код для ускорения его работы (ведь количество строк данных может быть очень большое). Что именно хочу сделать - так это избавиться от заполнения листа промежуточными вычислениями (важны столбцы подсвеченные красным цветом). Хотел запихать все эти столбцы в массивы и дальше работать с ними внутри кода. Наткнулся на такую проблему, попробую наглядно изобразить. Пусть в A и B столбцах имеютя какие либо данные, тогда:

Sub запись_в_ячейки()
For i = 1 To 10
Cells(i + 1, 3) = "=MAX(RC[-3]:RC[-2])"
Next
End Sub
Sub запись_в_массив()
 Dim massiv(1 To 10)
 For i = 1 To 10
 massiv(i) = "=MAX(B(i+1):E(i+1)" ' как правильно записать?
 Next
End Sub

Можно ли как то принцип реализованный в коде "запись в ячейке" заменить на "запись в массив", ну или что то в этом роде? Я знаю что напиши я например massiv(i) = i - тут проблем не было бы, а когда хочу оформить значения массива значениями формул, не могу сформулировать

[SAS888]

massiv(i) = "=MAX(B(i+1):E(i+1)" ' как правильно записать?

Формулу нужно формировать по всем правилам работы со строками. Например, так:

massiv(i) = "=MAX(B" & i + 1 & ":E" & i + 1 & ")"

Может проще все подсчитать внутри VBA, а на лист поместить только полученные значения (результирующий массив)? Тогда рабочий лист вообще не будет содержать формул. Или Ваша конечная задача - получить именно формулы в ячейках?
Так, например, для рассматриваемой строки:

massiv(i) = Application.Max(Range(Cells(i + 1, 2), Cells(i + 1, 5)))

[SAS888]

**********

[А)-(дрей]

Вот я и хочу в принципе посчитать все внутри VBA, просто изначально это был лист с формулами, по мере моего изучения VBA - появился этот код, теперь я уже понял, что не обязательно все расчеты вести на листах книги - поэтому и хочу оптимизировать код записав все промежуточные вычисления в массивы, проделав с ними внутри кода все операции и заполнив в итоге лист только теми данными, которые собственно и хотел получить.
Спасибо за приведенные примеры, я это и хотел узнать.
У меня еще один вопрос из кода:

    Range("F1").Select
    ActiveCell.FormulaR1C1 = "=COUNTIF(C[-4],"">0"")"
    Data = Range("F1")

Когда я хочу подставить параметр Data в массив

Dim M6(1 To Data)

В этой строчке возникает ошибка, как от неё избавиться?


Спасибо, синтаксис именно такой, какой нужно было узнать.

[А)-(дрей]

И еще вопрос

Dim M8(1 To 999)
    M8(i) = "=IF(G" & i + 1 & "=G" & i + 2 & ",1,0)"
    
    Dim M9(1 To 999)
    M9(i) = "=IF(G" & i + 1 & "=G" & i & ",1,0)"
    
    Dim M10(1 To 999)
    M10(i) = "=IF(M8(i)+M9(i)>0,1,0)" ' Можно ли так?

Можно ли в такой формуле подставить значения ранее вычесленных массивов, либо нужно пользоваться If.. then... else?

[А)-(дрей]

Понял... вообще не так делаю, массивы создаю с текстами формул, а не с их значениями. Последний пост тогда вообще смысла не имеет.
Нужно тогда пользоваться принципом

M7(i) = Application.Max(Range(Cells(i + 1, 2), Cells(i + 1, 5)))

только вот другие формулы я уже сам не запишу, например

M8(i) = Application.If(Range(Cells(i + 1, 7)) = Range(Cells(i + 2, 7)), 1, 0)

уже не работает

[SAS888]

Код:
M8(i) = Application.If(Range(Cells(i + 1, 7)) = Range(Cells(i + 2, 7)), 1, 0)
уже не работает

Синтаксис VBA нужно соблюдать. В данном случае, можно так:

M8(i) = IIf(Cells(i + 1, 7) = Cells(i + 2, 7), 1, 0)

но, опять же, для ускорения работы макроса, от непосредственной работы с ячейками листа нужно уходить. Т.е. все значения (как исходные, так и результирующие) должны находиться в массивах. Например:

M8(i) = IIf(a(i + 1, 7) = a(i + 2, 7), 1, 0)

где a(x, y) - двумерный массив (строки х столбцы).

Когда я хочу подставить параметр Data в массив
Код:
Dim M6(1 To Data)
В этой строчке возникает ошибка, как от неё избавиться?

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

Dim M6()
ReDim M6(1 To Data)

Range("F1").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(C[-4],"">0"")"
Data = Range("F1")

Это можно заменить на:

Data = Application.CountIf([B:B], ">0")

Dim M8(1 To 999)
M8(i) = "=IF(G" & i + 1 & "=G" & i + 2 & ",1,0)"
Dim M9(1 To 999)
M9(i) = "=IF(G" & i + 1 & "=G" & i & ",1,0)"
Dim M10(1 To 999)
M10(i) = "=IF(M8(i)+M9(i)>0,1,0)" ' Можно ли так?

Нужно так:

If Cells(i, "G") = Cells(i + 1, "G") Or Cells(i + 1, "G") = Cells(i + 2, "G") Then M10(i) = 1 Else M10(i) = 0

Повторюсь, что вместо значений ячеек листа нужно использовать значения заранее сформированного массива. Это быстрее в десятки (иногда сотни) раз!!!

[Hugo121]

SAS888, можно мне спросить?
Ведь работает и так:

Option Explicit
sub tt()
Dim Data
Data=10
ReDim M6(1 To Data)
end sub

Т.е. Dim M6() практически лишнее?

[doober]

Отличия между массивами М5 и М6 видно.М6 заполнен 0

Option Explicit
sub tt()
Dim Data
Dim M6() As Double
Data=10
ReDim M6(1 To Data)
ReDim M5(1 To Data)
end sub

[SAS888]

Так работать будет. Но в данном случае, Вы делаете то же самое, т.е. переопределяете неопределенный (не объявленный ранее) массив (ReDim M6(1 To Data)). Если же определять массив (Dim M6(1 To Data), как пробовал делать автор вопроса, то будет ошибка. Описание Dim M6() нужно так же для того, чтобы массив был пуст.