Из пространства стека – Замена текста в формуле

Я пытаюсь выполнить поиск в формулах для конкретной части формулы, а затем заменить ее на новую часть. Для этого я использовал следующий код:

Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim i As Integer If Range("B1").Value = "National" Then For i = 1 To 135 Range("C" & i).Formula = Replace(Range("C" & i).Formula, "SUMIF('Site Data '!$CR:$CR,$B$1,", "SUM(") Next i End If End Sub 

Когда я изменяю значение в ячейке B1 на National , код запускается, а затем заканчивается с ошибкой 28 – из пространства стека. Я нажимаю OK, Excel падает и перезапускается. В восстановленном файле все экземпляры текста были заменены по желанию.

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

Ваш код работает рекурсивно, запуская каскад событий. То есть: каждый раз, когда ваш код обращается к листу, он снова срабатывает. Избегайте этого, установив для .EnableEvents приложения .EnableEvents значение False или True мере необходимости

https://msdn.microsoft.com/en-us/library/aa264523(v=vs.60).aspx

 Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim i As Integer Application.EnableEvents = False If Range("B1").Value = "National" Then For i = 1 To 135 Range("C" & i).Formula = Replace(Range("C" & i).Formula, "SUMIF('Site Data '!$CR:$CR,$B$1,", "SUM(") Next i End If Application.EnableEvents = True End Sub 

Приостановите различные настройки среды во время выполнения.

 Option Explicit Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Not Intersect(Target, Range("B1")) Is Nothing Then On Error GoTo Safe_Exit Application.EnableEvents = False Application.Calculation = xlCalculationManual Dim i As Long If Range("B1").Value = "National" Then For i = 1 To 135 Range("C" & i).Formula = Replace(Range("C" & i).Formula, "SUMIF('Site Data '!$CR:$CR,$B$1,", "SUM(") Next i End If End If Safe_Exit: Application.EnableEvents = True Application.Calculation = xlCalculationAutomatic End Sub 

Вы перезаписываете макрос события, записывая новые формулы, чтобы временно приостановить обработку событий. Точно так же нет необходимости пересчитывать в вашем цикле; только после того, как все формулы были изменены.

Вместо того, чтобы использовать событие Change, которое будет вызываться для каждого изменения значения / формулы, я бы рекомендовал вместо этого использовать нелетучую формулу Excel, если это возможно:

 = If( $B$1 = "National", SUMIF_formula, SUM_formula ) 

В дополнение к тому, чтобы сделать лист медленнее и менее отзывчивым, чем необходимо, полагаясь на событие Change, не понимая этого, это может привести к некоторым другим неожиданным проблемам. Например: макросы, заблокированные Центром доверия или пользователем, изменяются на диапазоны или имя листа, сохраняя книгу в формате, который не позволяет VBA, например .xlsx и т. Д.

Interesting Posts

Скользящее среднее из самых низких 3 из последних 4 значений

Excel 2007 – 1004 Ошибка времени выполнения Обновить таблицу запросов

Как я прохожу через Autofilter, используя VBA в excel?

VBA Excel Поиск ячейки, содержащей одно слово

в Excel 2007 как взять число в ячейке и извлечь только часть его

Есть ли способ заставить Microsoft.Jet.OLEDB получать столбцы даты в формате MM / DD / YYYY из Excel?

Получить диапазон из ячейки, отформатированной как (A1: A15)

VBA – конвертировать очень небольшое значение в процентах

Индекс VBA / Совпадение с несколькими критериями (уникальное значение и дата)

Форматировать стиль ячейки через c #

Excel VBA: переслать ParamArray в Application.Run

Сумма столбца на основе записей из предыдущих 12 столбцов (число оборотов)

, Мне нужно усреднять значения, хранящиеся в нескольких строках в расширенном листе excel, но исключать любые пустые строки

Найти совпадение во втором столбце, используя возможное не уникальное значение в первом столбце

Добавление строки в большой файл xlsx (вне памяти)

Давайте будем гением компьютера.