Версия для печати темы Нажмите сюда для просмотра этой темы в оригинальном формате

Форум программистов > Программирование, связанное с MS Office > Обновление формул и замена на значения

Автор: Lapochka 5.3.2009, 11:17

Ребята, срочно нужна ваша помощь. У меня есть 2 функции на VBA, в которых я считаю остаток долга на начало периода (ОСТДОЛГНАЧ) и остаток долга на конец периода (ОСТДОЛГКОН). Есть макрос, где я считаю досрочку. Внутри этого макроса я и заполняю 2 столбца, используя эти функции. Так же есть еще 24 столбца, которые заполняются макросом, но используют стандартную ВПР. Все заполняется, все считается. Мои шаги (беру часть макроса, в которой проблемы возникли): 1) заполняю 24 столбца ВПР-ом (из 12 листов) 2) делаю range.value = range.value (чтобы не висло в последствии) 3) заполняю столбец с помощью функции ОСТДОЛГНАЧ 4) делаю range.value = range.value (чтобы не висло в последствии) 5) заполняю столбец с помощью функции ОСТДОЛГКОН 6) делаю range.value = range.value (чтобы не висло в последствии) В таблице, где я заполняю столбцы порядка 9000 строк. На 12 листах, откуда я беру значения с помощью ВПР, примерно столько же (на каждом из них). Все это иногда срабатывает на УРА, а иногда нет. 1) Столбец заполняется функцией 2) Считает для каждой строки значения 3) Макрос заменяет формулы на значения. И вот шаг 2 не всегда выполняется до конца. Т.е. до какой-то строки досчитал и не успел досчитать до конца, а уже делается замена функций на значения. Как этого можно избежать, чтобы макрос дождался пока пройдет весь расчет, и только потом заменил функции на значения? Функции заполняю следующим образом: Код cells(i,j).FormulaR1C1 = "... формула ..." cells(i,j).Select Selection.Autofill Destination:=range(cells(i,j),cells(i_end,j)) range(cells(i,j),cells(i_end,j)).value = range(cells(i,j),cells(i_end,j)).value

Автор: RockClimber 5.3.2009, 12:35

Я с 2007-м не работал никогда, но вот в 2003-м для пересчета формул можно было использовать Application.Calculate. Не подойдет?

Автор: Akina 5.3.2009, 14:18

Цитата(Lapochka @  5.3.2009,  12:17 ) до какой-то строки досчитал и не успел досчитать до конца, а уже делается замена функций на значения. Правильно, пересчёт - процесс асинхронный.

Автор: Sefko 5.3.2009, 19:15

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

Автор: Lapochka 11.3.2009, 15:55

OST_DOLG_OOD_START и OST_DOLG_OOD_END - функции (их код не стала писать). Сам основной макрос ниже (убрала разделы set  и dim): OOD new, start plan dolg и end plan dolg - вот тут и проблема, когда он не досчитывает и идет дальше (пометила "!") Код Private Sub DOLG_DATA()          wsh_OOD.Activate     row_ood = Cells(Rows.Count, 1).End(xlUp).Row     column_ood = Cells(2, Columns.Count).End(xlToLeft).Column          Set My_Cell = Range(Cells(1, 1), Cells(1, column_ood)).Find("1 mnth.", , , , , xlNext)     col_ood_st = My_Cell.Column     col_ood = My_Cell.Column              For row_akp = 1 To 12         If row_akp = 1 Then                 row_m = wsh_m1.Cells(Rows.Count, 1).End(xlUp).Row             ElseIf row_akp = 2 Then                 row_m = wsh_m2.Cells(Rows.Count, 1).End(xlUp).Row             ElseIf row_akp = 3 Then                 row_m = wsh_m3.Cells(Rows.Count, 1).End(xlUp).Row             ElseIf row_akp = 4 Then                 row_m = wsh_m4.Cells(Rows.Count, 1).End(xlUp).Row             ElseIf row_akp = 5 Then                 row_m = wsh_m5.Cells(Rows.Count, 1).End(xlUp).Row             ElseIf row_akp = 6 Then                 row_m = wsh_m6.Cells(Rows.Count, 1).End(xlUp).Row             ElseIf row_akp = 7 Then                 row_m = wsh_m7.Cells(Rows.Count, 1).End(xlUp).Row             ElseIf row_akp = 8 Then                 row_m = wsh_m8.Cells(Rows.Count, 1).End(xlUp).Row             ElseIf row_akp = 9 Then                 row_m = wsh_m9.Cells(Rows.Count, 1).End(xlUp).Row             ElseIf row_akp = 10 Then                 row_m = wsh_m10.Cells(Rows.Count, 1).End(xlUp).Row             ElseIf row_akp = 11 Then                 row_m = wsh_m11.Cells(Rows.Count, 1).End(xlUp).Row             Else                 row_m = wsh_m12.Cells(Rows.Count, 1).End(xlUp).Row         End If ' Dolg month         Cells(3, col_ood).FormulaR1C1 = "=IF(ISNA(VLOOKUP(RC1,'" & CStr(row_akp) & "mnth'!R4C1:R" & row_m & _                     "C9,9,FALSE)),IF(ISNA(VLOOKUP(RC2,'" & CStr(row_akp) & "mnth'!R4C2:" & _                     "R" & row_m & "C9,8,FALSE)),0,VLOOKUP(RC2,'" & CStr(row_akp) & "mnth'!" & _                     "R4C2:R" & row_m & "C9,8,FALSE)),VLOOKUP(RC1,'" & CStr(row_akp) & "mnth'!" & _                     "R4C1:R" & row_m & "C9,9,FALSE))"         Cells(3, col_ood).Select         Selection.AutoFill Destination:=Range(Cells(3, col_ood), Cells(row_ood, col_ood))         Cells(3, col_ood).Select ' Dolg month ekv         Cells(3, col_ood + 1).FormulaR1C1 = "=IF(ISNA(VLOOKUP(RC1,'" & CStr(row_akp) & "mnth'!R4C1:R" & row_m & _                     "C10,10,FALSE)),IF(ISNA(VLOOKUP(RC2,'" & CStr(row_akp) & "mnth'!R4C2:" & _                     "R" & row_m & "C10,9,FALSE)),0,VLOOKUP(RC2,'" & CStr(row_akp) & "mnth'!" & _                     "R4C2:R" & row_m & "C10,9,FALSE)),VLOOKUP(RC1,'" & CStr(row_akp) & "mnth'!" & _                     "R4C1:R" & row_m & "C10,10,FALSE))"         Cells(3, col_ood + 1).Select         Selection.AutoFill Destination:=Range(Cells(3, col_ood + 1), Cells(row_ood, col_ood + 1))         Cells(3, col_ood + 1).Select                  Range(Cells(3, col_ood), Cells(row_ood, col_ood + 1)).NumberFormat = "0.00"                  col_ood = col_ood + 2     Next row_akp          col_ood = col_ood - 1 ' !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! ' OOD new         Range(Cells(3, col_ood_st), Cells(row_ood, col_ood)).Value = _             Range(Cells(3, col_ood_st), Cells(row_ood, col_ood)).Value ' !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!     Range(Cells(3, col_ood_st), Cells(row_ood, col_ood)).Select          Columns.AutoFit               Set My_Cell = Range(Cells(1, 1), Cells(1, column_ood)).Find("Srart fact", , , , , xlNext)     col_data = My_Cell.Column      ' Start fact dolg         Cells(3, col_data).FormulaR1C1 = "=IF(INDIRECT(ADDRESS(ROW(RC1),IF(ISNA(MATCH(0,RC" & _                     col_ood_st & ":RC" & col_ood & ",1)),0,MATCH(0,RC" & col_ood_st & ":RC" & _                     col_ood & ",1))+" & col_ood_st & "))=0,"""",INDIRECT(ADDRESS(ROW(RC1)," & _                     "IF(ISNA(MATCH(0,RC" & col_ood_st & ":RC" & col_ood & ",1)),0,MATCH(0,RC" & _                     col_ood_st & ":RC" & col_ood & ",1))+" & col_ood_st & ")))"         Cells(3, col_data).Select         Selection.AutoFill Destination:=Range(Cells(3, col_data), Cells(row_ood, col_data))         Cells(3, col_data).Select ' Start fact dolg ekv         Cells(3, col_data + 1).FormulaR1C1 = "=IF(INDIRECT(ADDRESS(ROW(RC1),IF(ISNA(MATCH(0,RC" & _                     col_ood_st & ":RC" & col_ood & ",1)),0,MATCH(0,RC" & col_ood_st & ":RC" & _                     col_ood & ",1))+" & col_ood_st + 1 & "))=0,"""",INDIRECT(ADDRESS(ROW(RC1)," & _                     "IF(ISNA(MATCH(0,RC" & col_ood_st & ":RC" & col_ood & ",1)),0,MATCH(0,RC" & _                     col_ood_st & ":RC" & col_ood & ",1))+" & col_ood_st + 1 & ")))"         Cells(3, col_data + 1).Select         Selection.AutoFill Destination:=Range(Cells(3, col_data + 1), Cells(row_ood, col_data + 1))         Cells(3, col_data + 1).Select          ' End fact dolg         Cells(3, col_data + 2).FormulaR1C1 = "=IF(RC" & col_ood - 1 & "=0,"""",RC" & col_ood - 1 & ")"         Cells(3, col_data + 2).Select         Selection.AutoFill Destination:=Range(Cells(3, col_data + 2), Cells(row_ood, col_data + 2))         Cells(3, col_data + 2).Select          ' End fact dolg ekv         Cells(3, col_data + 3).FormulaR1C1 = "=IF(RC" & col_ood & "=0,"""",RC" & col_ood & ")"         Cells(3, col_data + 3).Select         Selection.AutoFill Destination:=Range(Cells(3, col_data + 3), Cells(row_ood, col_data + 3))         Cells(3, col_data + 3).Select          ' change fact dolg         Cells(3, col_data + 4).FormulaR1C1 = "=IF(RC[-4]="""","""",IF(RC[-4]-RC[-2]=0,"""",RC[-4]-RC[-2]))"         Cells(3, col_data + 4).Select         Selection.AutoFill Destination:=Range(Cells(3, col_data + 4), Cells(row_ood, col_data + 4))         Cells(3, col_data + 4).Select          ' change fact dolg ekv         Cells(3, col_data + 5).FormulaR1C1 = "=IF(RC[-4]="""","""",IF(RC[-4]-RC[-2]=0,"""",RC[-4]-RC[-2]))"         Cells(3, col_data + 5).Select         Selection.AutoFill Destination:=Range(Cells(3, col_data + 5), Cells(row_ood, col_data + 5))         Cells(3, col_data + 5).Select                      Range(Cells(3, col_data), Cells(row_ood, col_data + 5)).NumberFormat = "0.00" ' number of month         Cells(3, col_ood + 1).FormulaR1C1 = "=IF(INDIRECT(ADDRESS(1,IF(ISNA(MATCH(0,RC" & _                     col_ood_st & ":RC" & col_ood & ",1)),0,MATCH(0,RC" & col_ood_st & ":RC" & _                     col_ood & ",1))+" & col_ood_st & "))=0,"""",MID(INDIRECT(ADDRESS(1," & _                     "IF(ISNA(MATCH(0,RC" & col_ood_st & ":RC" & col_ood & ",1)),0,MATCH(0,RC" & _                     col_ood_st & ":RC" & col_ood & ",1))+" & col_ood_st & ")),1,LEN(INDIRECT" & _                     "(ADDRESS(1,IF(ISNA(MATCH(0,RC" & col_ood_st & ":RC" & col_ood & ",1)),0," & _                     "MATCH(0,RC" & col_ood_st & ":RC" & col_ood & ",1))+" & col_ood_st & ")))-5))"         Cells(3, col_ood + 1).Select         Selection.AutoFill Destination:=Range(Cells(3, col_ood + 1), Cells(row_ood, col_ood + 1))         Cells(3, col_ood + 1).Select             Set My_Cell = Range(Cells(1, 1), Cells(2, column_ood)).Find("Sum", , , , , xlNext)     sum_kred = My_Cell.Column          Set My_Cell = Range(Cells(1, 1), Cells(2, column_ood)).Find("Sum ekv", , , , , xlNext)     sum_kred_ekv = My_Cell.Column          Set My_Cell = Range(Cells(1, 1), Cells(2, column_ood)).Find("Stavka", , , , , xlNext)     Stavka = My_Cell.Column          Set My_Cell = Range(Cells(1, 1), Cells(2, column_ood)).Find("Srok", , , , , xlNext)     Srok = My_Cell.Column          Set My_Cell = Range(Cells(1, 1), Cells(2, column_ood)).Find("Date start", , , , , xlNext)     data_kred = My_Cell.Column          Set My_Cell = Range(Cells(1, 1), Cells(2, column_ood)).Find("OOD", , , , , xlNext)     grafik = My_Cell.Column                    Set My_Cell1 = Range(Cells(1, 1), Cells(1, column_ood)).Find("Start plan", , , , , xlNext)     col_data1 = My_Cell1.Column ' !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! ' start plan dolg         Cells(3, col_data1).FormulaR1C1 = "=IF(RC" & col_ood + 1 & "="""","""",OST_DOLG_OOD_START(" & _                     "RC" & sum_kred & ",RC" & Stavka & ",RC" & grafik & ",RC" & Srok & ",RC" & _                     data_kred & ",RC" & col_ood + 1 & "))"         Cells(3, col_data1).Select         Selection.AutoFill Destination:=Range(Cells(3, col_data1), Cells(row_ood, col_data1))         Cells(3, col_data1).Select         Range(Cells(3, col_data1), Cells(row_ood, col_data1)).Value = _                 Range(Cells(3, col_data1), Cells(row_ood, col_data1)).Value          ' end plan dolg         Cells(3, col_data1 + 2).FormulaR1C1 = "=IF(RC" & col_data & "="""","""",OST_DOLG_OOD_END(" & _                     "RC" & sum_kred & ",RC" & Stavka & ",RC" & grafik & ",RC" & Srok & ",RC" & _                     data_kred & "))"         Cells(3, col_data1 + 2).Select         Selection.AutoFill Destination:=Range(Cells(3, col_data1 + 2), Cells(row_ood, col_data1 + 2))         Cells(3, col_data1 + 2).Select         Range(Cells(3, col_data1 + 2), Cells(row_ood, col_data1 + 2)).Value = _                 Range(Cells(3, col_data1 + 2), Cells(row_ood, col_data1 + 2)).Value ' !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!                 ' change plan dolg         Cells(3, col_data1 + 4).FormulaR1C1 = "=IF(RC[-4]="""","""",IF(RC[-4]-RC[-2]=0,"""",RC[-4]-RC[-2]))"         Cells(3, col_data1 + 4).Select         Selection.AutoFill Destination:=Range(Cells(3, col_data1 + 4), Cells(row_ood, col_data1 + 4))         Cells(3, col_data1 + 4).Select           Set Dosrochka_Cell = Range(Cells(1, 1), Cells(1, column_ood)).Find("Dosrochka", , , , , xlNext)     col_dosrochka = Dosrochka_Cell.Column      ' dosrochka         Cells(3, col_dosrochka).FormulaR1C1 = "=IF(RC" & col_data + 4 & "="""",""""," & _                     "IF(RC" & col_data + 4 & ">RC" & col_data1 + 4 & ",RC" & col_data + 4 & _                     "-RC" & col_data1 + 4 & ",""""))"         Cells(3, col_dosrochka).Select         Selection.AutoFill Destination:=Range(Cells(3, col_dosrochka), Cells(row_ood, col_dosrochka))         Cells(3, col_dosrochka).Select          wsh_D.Activate ' Sum dosrochka     column_data = Cells(2, Columns.Count).End(xlToLeft).Column     row_data = Cells(Rows.Count, 1).End(xlUp).Row          Set Dosrochka_Cell_Data = Range(Cells(2, 1), _             Cells(2, column_data)).Find("Sum dosrochka", , , , , xlNext)     col_dosrochka_data = Dosrochka_Cell_Data.Column              Cells(3, col_dosrochka_data).FormulaR1C1 = "=VLOOKUP(CONCATENATE(RC1,""/"",RC3,""/"",RC7)," & _                 "OOD!R3C1:R" & row_ood & "C" & col_ood & "," & col_dosrochka & ",FALSE)"         Cells(3, col_dosrochka_data).Select         Selection.AutoFill Destination:=Range(Cells(3, col_dosrochka_data), _                 Cells(row_data, col_dosrochka_data))         Range(Cells(3, col_dosrochka_data), Cells(row_data, col_dosrochka_data)).Value = _                 Range(Cells(3, col_dosrochka_data), Cells(row_data, col_dosrochka_data)).Value     End Sub

Автор: Sefko 11.3.2009, 19:28

М-да, круто!  Я попытался воспроизвести наблюдаемый Вами дефект. Но не получилось. Все работает якобы правильно. Тем не менее... Вы же наблюдаете. Поэтому сделал попытку исправления кода (моего примера, естественно). Результата не знаю - у меня же и исходный вариант работает. Но может быть Вы узнаете результат? У Вас же исходный вариант не работает. Код ' Придурковатая функция6 просто долго мается дурью Public Function fff(ByVal x As Double) As Double   Dim y As Double, z As Double, n As Long   y = x / 2   z = x - Exp(Sin(y))   n = 0   Do While ((Abs(z) > 0.0000001) And (n < 1000))     y = y - z / 2     If y > 6.283185307 Then       y = y - 6.283185307     ElseIf y < 0 Then y = y + 6.283185307     End If     z = x - Exp(Sin(y))     n = n + 1   Loop   fff = y End Function ' Попытка воспроизвести Ваш вариант. ' Напоминаю: я не заметил наблюдаемого Вами дефекта Public Sub AAA()   Dim i As Long        Range("A1:B2000").Formula = "=RAND()"   Range("A1:B2000") = Range("A1:B2000").Value   Range("C1:V2000") = "Kyky" ' Так, на всякий случай   For i = 3 To 22     Cells(1, i).FormulaR1C1 = "=fff(RC[-2]+RC[-1])"     Cells(1, i).AutoFill Destination:=Range(Cells(1, i), Cells(2000, i))     Range(Cells(1, i), Cells(2000, i)).Value = Range(Cells(1, i), Cells(2000, i)).Value   Next End Sub ' Попытка что-то синхронизировать/оптимизировать ' Надеюсь, что идея понятна. ' Еще надеюсь на сообщение о результате Public Sub BBB()   Dim i As Long   Application.ScreenUpdating = False   Range("A1:B2000").Formula = "=RAND()"   Range("A1:B2000") = Range("A1:B2000").Value   Range("C1:V2000") = "Kyky" ' Так, на всякий случай      Application.Calculation = xlCalculationManual   For i = 3 To 22     Cells(1, i).FormulaR1C1 = "=fff(RC[-2]+RC[-1])"     Cells(1, i).AutoFill Destination:=Range(Cells(1, i), Cells(2000, i))     Range(Cells(1, i), Cells(2000, i)).Calculate   Next   Range(Cells(1, 3), Cells(2000, 22)).Value = Range(Cells(1, 3), Cells(2000, 22)).Value      Application.Calculation = xlCalculationAutomatic   Application.ScreenUpdating = True End Sub

Автор: Lapochka 12.3.2009, 17:18

у меня сработал application.calculate    не удалось воспроизвести из-за того, что стоит calculate ) спасибо всем огромное

Powered by Invision Power Board (http://www.invisionboard.com)
© Invision Power Services (http://www.invisionpower.com)