Добрый день уважаемые форумчане, есть код, который прописывает в таблице формулы
затем происходит конвертация данных формул в значения
Код |
---|
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 |
после блока формул, но ничего не выходит, формулы по прежнему продолжают не пересчитываться
Подскажите пожалуйста, как можно решить выполнение до конца всего пересчета и последующую конвертацию в значения