Страницы: 1
RSS
Закончить автоматический пересчет формул и последующее выполнение кода макроса
 
Добрый день уважаемые форумчане, есть код, который прописывает в таблице формулы
Код
Dim cell_overdue As Object
Dim ñLastRow_overdue As Long
Dim clearLastRow_overdue As Long

clearLastRow_overdue = 2

For Each cell_overdue In ActiveSheet.Range("1:1").Cells
If cell_overdue.Value = "" Then Exit For
ñLastRow_overdue = ActiveSheet.Cells(Rows.Count, cell_overdue.Column).End(xlUp).Row
 If ñLastRow_overdue > clearLastRow_overdue Then clearLastRow_overdue = ñLastRow_overdue
Next cell_overdue

If clearLastRow_overdue > 1 Then

'блок формул 1

ActiveSheet.Range(Cells(2, 23), Cells(clearLastRow_overdue, 23)).FormulaR1C1 = _
        "=IF(AND(""Q1 Ìàð""=[@[Date.Q]],COUNTIFS(R1C10:RC[-13],RC[-13])=1),MAXIFS([Date.D],[Date.M-Y],[@[Date.M-Y]]),IF(AND(""Q2 Èþí""=[@[Date.Q]],COUNTIFS(R1C10:RC[-13],RC[-13])=1),MAXIFS([Date.D],[Date.M-Y],[@[Date.M-Y]]),IF(AND(""Q3 Ñåí""=[@[Date.Q]],COUNTIFS(R1C10:RC[-13],RC[-13])=1),MAXIFS([Date.D],[Date.M-Y],[@[Date.M-Y]]),IF(AND(""Q4 Äåê""=[@[Date.Q]],COUNTIFS(R1C10:R" & _
        "C[-13],RC[-13])=1),MAXIFS([Date.D],[Date.M-Y],[@[Date.M-Y]]),IF(AND(""Q1 Ôåâ""=[@[Date.Q]],COUNTIFS(R1C10:RC[-13],RC[-13])=1),MAXIFS([Date.D],[Date.M-Y],[@[Date.M-Y]]),IF(AND(""Q2 Ìàé""=[@[Date.Q]],COUNTIFS(R1C10:RC[-13],RC[-13])=1),MAXIFS([Date.D],[Date.M-Y],[@[Date.M-Y]]),IF(AND(""Q3 Àâã""=[@[Date.Q]],COUNTIFS(R1C10:RC[-13],RC[-13])=1),MAXIFS([Date.D],[Date.M-Y],[" & _
        "@[Date.M-Y]]),IF(AND(""Q4 Íîÿ""=[@[Date.Q]],COUNTIFS(R1C10:RC[-13],RC[-13])=1),MAXIFS([Date.D],[Date.M-Y],[@[Date.M-Y]]),IF(AND(""Q1 ßíâ""=[@[Date.Q]],COUNTIFS(R1C10:RC[-13],RC[-13])=1),MAXIFS([Date.D],[Date.M-Y],[@[Date.M-Y]]),IF(AND(""Q2 Àïð""=[@[Date.Q]],COUNTIFS(R1C10:RC[-13],RC[-13])=1),MAXIFS([Date.D],[Date.M-Y],[@[Date.M-Y]]),IF(AND(""Q3 Èþë""=[@[Date.Q]],COU" & _
        "NTIFS(R1C10:RC[-13],RC[-13])=1),MAXIFS([Date.D],[Date.M-Y],[@[Date.M-Y]]),IF(AND(""Q4 Îêò""=[@[Date.Q]],COUNTIFS(R1C10:RC[-13],RC[-13])=1),MAXIFS([Date.D],[Date.M-Y],[@[Date.M-Y]]),""""))))))))))))" & _
        ""

ActiveSheet.Range(Cells(2, 24), Cells(clearLastRow_overdue, 24)).FormulaR1C1 = "=CONCAT([@[Date.Q]],[@[Date.Y]])"
ActiveSheet.Range(Cells(2, 25), Cells(clearLastRow_overdue, 25)).FormulaR1C1 = "=LEFT([@[Date.Q]],2)&[@[Date.Y]]"
ActiveSheet.Range(Cells(2, 26), Cells(clearLastRow_overdue, 26)).FormulaR1C1 = "=MAXIFS([Date for MTD AR],[System_2],[@[System_2]])"
ActiveSheet.Range(Cells(2, 27), Cells(clearLastRow_overdue, 27)).FormulaR1C1 = "=IF(AND([@[System_3]]>0,COUNTIFS(R1C26:RC[-1],RC[-1])=1),SUMIFS([Ñóììà],[Date.D],[@[System_3]]),"""")"
ActiveSheet.Range(Cells(2, 28), Cells(clearLastRow_overdue, 28)).FormulaR1C1 = "=IF([@[Sum AR overdue_pivot]]<>"""",LEFT([@[System_1]],2),"""")"
ActiveSheet.Range(Cells(2, 29), Cells(clearLastRow_overdue, 29)).FormulaR1C1 = "=IF([@[Sum AR overdue_pivot]]<>"""",[@[Date.Y]],"""")"
ActiveSheet.Range(Cells(2, 30), Cells(clearLastRow_overdue, 30)).FormulaR1C1 = "=IF([@[Year_pivot]]<>"""",""ok"","""")"
ActiveSheet.Range(Cells(2, 31), Cells(clearLastRow_overdue, 31)).FormulaR1C1 = "=IF([@Flag]=""ok"",IFERROR(IF(SUMIFS(Total[Ñàëüäî íà êîíåö ïåðèîäà],Total[Date.D],[@[System_3]])=0,"""",SUMIFS(Total[Ñàëüäî íà êîíåö ïåðèîäà],Total[Date.D],[@[System_3]])),""""),"""")"

'блок формул 2

ActiveSheet.Range(Cells(2, 32), Cells(clearLastRow_overdue, 32)).FormulaR1C1 = "=CONCATENATE([@Date]&""_""&[@Çàêàç÷èê]&""_""&[@Ïîëó÷àòåëü]&""_""&[@[Ñïðàâ.¹]]&""_""&""_""&[@[System_'#]]&""_""&[@Ñóììà])"
ActiveSheet.Range(Cells(2, 33), Cells(clearLastRow_overdue, 33)).FormulaR1C1 = "=IF(LEN(CONCATENATE([@Çàêàç÷èê]&""_""&[@Ïîëó÷àòåëü]&""_""&[@[Ñïðàâ.¹]]&""_""&[@Ñóììà]&""_""&[@[Date.W]]))>255,CONCATENATE([@Çàêàç÷èê]&""_""&[@Ïîëó÷àòåëü]&""_""&[@Äîêóìåíò]&""_""&[@Ñóììà]&""_""&[@[Date.W]]),CONCATENATE([@Çàêàç÷èê]&""_""&[@Ïîëó÷àòåëü]&""_""&[@[Ñïðàâ.¹]]&""_""&[@Ñóììà]&""_""&[@[Date.W]]))"
ActiveSheet.Range(Cells(2, 34), Cells(clearLastRow_overdue, 34)).FormulaR1C1 = "=IF(LEN([@Çàêàç÷èê]&""_""&[@Ïîëó÷àòåëü]&""_""&[@[Ñïðàâ.¹]]&""_""&[@Ñóììà])>255,[@Çàêàç÷èê]&""_""&[@Ïîëó÷àòåëü]&""_""&[@Äîêóìåíò]&""_""&[@Ñóììà],[@Çàêàç÷èê]&""_""&[@Ïîëó÷àòåëü]&""_""&[@[Ñïðàâ.¹]]&""_""&[@Ñóììà])"
ActiveSheet.Range(Cells(2, 35), Cells(clearLastRow_overdue, 35)).FormulaR1C1 = "=COUNTIF(R1C33:RC[-2],RC[-2])"
ActiveSheet.Range(Cells(2, 36), Cells(clearLastRow_overdue, 36)).FormulaR1C1 = "=IF(AND([@[System_Ïîâòîðû]]=1,SUMIFS([System_Ïîâòîðû],[System_Êëþ÷_êîë-âî ïîâòîðîâ äàò â ðàìêàõ íåäåëè],RC[-3])>1),""blanks"","""")"
ActiveSheet.Range(Cells(2, 37), Cells(clearLastRow_overdue, 37)).FormulaR1C1 = "=COUNTIF(R1C34:RC[-3],RC[-3])"
ActiveSheet.Range(Cells(2, 38), Cells(clearLastRow_overdue, 38)).FormulaR1C1 = "=COUNTIFS([System_Êëþ÷_êîë-âî ïîâòîðîâ äàò â ðàìêàõ íåäåëè],RC[-5])"
ActiveSheet.Range(Cells(2, 39), Cells(clearLastRow_overdue, 39)).FormulaR1C1 = "=IF(AND([@[System_'#]]=1,[@[System_Îáùåå ÷èñëî ïîâòîðîâ]]=1),1,IF([@[System_Blanks]]<>""blanks"",MAXIFS(R1C39:R[-1]C,R1C34:R[-1]C[-5],RC[-5])+1,""""))"
ActiveSheet.Range(Cells(2, 40), Cells(clearLastRow_overdue, 40)).FormulaR1C1 = "=IFERROR(IF(IF([@[System_ñëó÷àé ïðîñðî÷êè]]=1,0,7*[@[System_ñëó÷àé ïðîñðî÷êè]])-7<0,IF([@[System_ñëó÷àé ïðîñðî÷êè]]=1,0,7*[@[System_ñëó÷àé ïðîñðî÷êè]])-7+7,IF([@[System_ñëó÷àé ïðîñðî÷êè]]=1,0,7*[@[System_ñëó÷àé ïðîñðî÷êè]])-7),"""")"


End If

затем происходит конвертация данных формул в значения

Код
Range("Overdue[[Date for MTD AR]:[Êîëè÷åñòâî äíåé ïðîñðî÷êè]]").Select

Dim smallrng_overdue As Range
    
For Each smallrng_overdue In Selection.Areas
    smallrng_overdue.Value = smallrng_overdue.Value
Next smallrng_overdue

проблема в том, что формулы не успевают пересчитаться и соответственно происходит конвертация не вычисленных данных в значения

пробовал использовать конструкцию

Код
''If Not Application.CalculationState = xlDone Then
''DoEvents
''End If

после блока формул, но ничего не выходит, формулы по прежнему продолжают не пересчитываться

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

 
Попробуйте после вставки формул
Код
Application.Calculate
Соблюдение правил форума не освобождает от модераторского произвола
 
Цитата
pinguindell написал:
код, который прописывает в таблице формулы
По моему вы должны изменить формулы на "чистый код" vba
Что делает этот монстр ?
Код
ActiveSheet.Range(Cells(2, 23), Cells(clearLastRow_overdue, 23)).FormulaR1C1 = _
"=IF(AND(""Q1 Ia?""=[@[Date.Q]],COUNTIFS(R1C10:RC[-13],RC[-13])=1),MAXIFS ... ? :-( ? ... _
,RC[-13])=1),MAXIFS([Date.D],[Date.M-Y],[@[Date.M-Y]]),""""))))))))))))" & ""
Изменено: ocet p - 16.08.2019 17:57:01
Страницы: 1
Наверх