Ошибка времени выполнения 1004 (добавление формул в диапазон ячеек с помощью цикла)

По какой-то причине приведенный ниже код генерирует ошибку времени выполнения 1004. Это простой цикл для добавления формулы в диапазон ячеек, используя значения, которые находятся в ячейках на 3 строки выше, и знак «=» в конкретной ячейке.

Ячейки 3 строками выше содержат формулу в виде текста, например: IF(OR($A5="",W5<=0),"",RANK(W5,$W$5:$W$9248,0))

Я попытался жестко закодировать значение «eq» (которое является знаком «=»), и ошибка все еще возникает, но если бы вы сделали его другим символом, он работал нормально. У меня сейчас нет идей.

Заранее спасибо.

Sub insertFormula()

Application.ScreenUpdating = False

'Set variables
Dim c, rng As Range
Dim eq As String

Set rng = ActiveSheet.Range("S5:W5")
eq = ActiveSheet.Range("S1").Value 'This cell holds the = sign

'Loop through range and apply formula
For Each c In rng
    c.Value = eq & c.Offset(-3, 0).Value
Next c

Application.ScreenUpdating = True

End Sub

Куда добавить формулу? c.formula = ... добавляет формулу, c.value = ... - нет.

Storax 10.09.2018 09:53

Вы хотели написать c.Offset(-3, 0).Value, или это должен быть c.Offset(-3, 0).Address?

Chronocidal 10.09.2018 10:00

Привет, Storax, сама формула сохраняется в виде строки в электронной таблице без знака = впереди, например: IF (OR ($ A5 = "", W5 <= 0), "", RANK (W5, $ W $ 5: $ W $ 9248,0)), поэтому идея заключалась в том, чтобы заставить vba соединить = и строку формулы вместе, чтобы формула работала.

Marc 10.09.2018 10:03

Привет, Chronocidal, да, это должно было быть .Value, поскольку я пытался объединить знак '=' и формулу, чтобы цикл вставил = (независимо от формулы в ячейке 3 строки выше) в диапазон. Спасибо.

Marc 10.09.2018 10:13

Опять же, вы должны установить формулу с . formula. Вы можете использовать составленную вами строку, но вы должны убедиться, что это действительная строка, и вы должны поместить ее в ""

Storax 10.09.2018 10:18
0
5
127
1

Ответы 1

Другое решение - использовать нотацию RC.

Sub insertFormula()

    'Set variables
    Dim rng As Range

    Set rng = ActiveSheet.Range("S5:W5")
    rng.Formula = "=R[-3]C"

End Sub

В одну строчку

 ActiveSheet.Range("S5:W5").Formula = "=R[-3]C"

Обновлять На основании комментария OP кажется, что что-то подобное

For Each c In rng
    c.Formula = "=" & c.Offset(-3, 0).Value & ""
Next c

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

Upadte 2 Кажется, что в ячейке есть пробелы, которые могут вызвать ошибку времени выполнения 1004. Попробуйте сначала очистить строку

For Each c In rng
    c.Formula = "=" & WorksheetFunction.Clean(c.Offset(-3, 0).Value) & ""
    'c.Formula = "=" & c.Offset(-3, 0).Value & ""
Next c

Обновление 3 Также возможно, что строка содержит неразрывный пробел. Вы тоже можете его удалить.

Dim s As String

For Each c In rng
    s = WorksheetFunction.Clean(c.Offset(-3, 0).Value)
    s = Replace(s, Chr(160), "")
    c.Formula = "=" & s
    'c.Formula = "=" & c.Offset(-3, 0).Value & ""
Next c

Спасибо за предложение. Я пробовал c.Formula = "=" & c.Offset (-3, 0) .Value & "", но это все равно возвращает ошибку.

Marc 10.09.2018 10:31

Вам не нужен & "" в конце линии. @Marc - пробовали ли вы вручную выполнить одну из ячеек, чтобы проверить, действительно ли вычисленная формула действительна или нет?

Chronocidal 10.09.2018 10:31

Да, конечно, & "" в конце строки вам не нужен, но вреда от этого не будет. Если вы по-прежнему получаете ошибку, значит, сама формула неверна. Я пробовал это с вашим примером из комментария, и это сработало. И `" возвращает ошибку "не является полезным описанием ошибки. Также попробуйте вручную выполнить формулу, вызывающую ошибку.

Storax 10.09.2018 10:34

Привет, Storax, формула работает без ошибок при вставке вручную в ячейку. Проблема, похоже, связана с конкатенацией знака '=' и формулы, когда vba пытается соединить их. Спасибо.

Marc 10.09.2018 10:47

Поскольку указанный код работает с IF (OR ($ A5 = "", W5 <= 0), "", RANK (W5, $ W $ 5: $ W $ 9248,0)) для меня. Какая строка у вас не работает с кодом?

Storax 10.09.2018 10:49

Ошибка связана с этой строкой в ​​соответствии с отладкой: c.Value = "=" & c.Offset (-3, 0) .Value Я заменил 'eq' на '=', чтобы вы могли видеть, что я пытаюсь объединиться. Спасибо.

Marc 10.09.2018 11:10

Не в этом вопрос. Я имею ввиду содержимое c.Offset(-3, 0).Value

Storax 10.09.2018 11:11

Извините, Storax, я не понял вопроса. Если вы имеете в виду, каково содержимое c.Offset (-3, 0) .Value, то это IF (OR ($ A5 = "", W5 <= 0), "", RANK (W5, $ W $ 5 : $ W $ 9248,0))

Marc 10.09.2018 11:27

Эта строка работает для меня. Если вы по-прежнему получаете ошибку времени выполнения 1004, возможно, в строке есть пробелы.

Storax 10.09.2018 11:31

Ага, нашла, дальше по листу была еще одна формула с пустым местом в конце. Большое спасибо за вашу помощь.

Marc 10.09.2018 11:52

Другие вопросы по теме