Как автоматически рассчитать / разрешить именованную ячейку в формуле?

Question

У меня А1 = 5, и я добавил А1 в качестве имени « Главный герой ».

Если я это сделаю =Protagonist он придумает 5.

Скажем, в D12 я хочу сделать =Protagonist+1 Собирается придумать 6.

• Чего я хочу добиться, так это заменить " главный герой " на 5 и оставить формулу D12 =5+1 .

Я должен сделать это, так как A1 постоянно меняет свое значение, и мне нужно только сохранить значение, которое он имеет во время создания формулы, которая его использует.

(Вручную я знаю, что могу выбрать Protagonist и нажать F9, чтобы разрешить только эту часть формулы, но это основное время, затрачиваемое на количество, которое я должен сделать)

Это может произойти после того, как формула введена и разрешена. Хотя было бы удивительно, если бы это могло произойти в строке формул при наборе текста, но я бы предположил, что vba не слишком далеко.

Answer 1

Если вам нужно заменить его на рассчитанное значение, то:

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Count = 1 Then
    If Left(Target.Formula, 1) = "=" And IsNumeric(Intersect(Range("B1:F1000"), Target)) Then
      Dim a As Variant
      For Each a In Array("protagonist", "anotherNameToChange")
        While InStr(1, Target.Formula, a, 1)
          Target.Formula = Replace(Target.Formula, a, Evaluate(ThisWorkbook.Names(a).Value), , , 1)
        Wend
      Next
    End If
  End If
End Sub

Будет делать это для всех имен в массиве также автоматически по желанию ...

Если необходимо сделать это для каждого имени, измените следующие 2 строки:

For Each a In Array("protagonist", "anotherNameToChange")

изменения в:

For Each a In ThisWorkbook.Names

а также

Target.Formula = Replace(Target.Formula, a, Evaluate(ThisWorkbook.Names(a).Value), , , 1)

в

Target.Formula = Replace(Target.Formula, a, Evaluate(a.Value), , , 1)

Просто отметьте, что это не чувствительно к регистру :)

Answer 2

Попробуйте это:

Sub qwerty()
   Dim N As Name, FixString As String

   Set N = ActiveWorkbook.Names("Protagonist")
   FixString = N.RefersToRange

   For Each cell In Cells.SpecialCells(xlCellTypeFormulas)
      cell.Formula = Replace(cell.Formula, "Protagonist", FixString)
   Next cell
End Sub

EDIT # 1:

Чтобы вызвать qwerty из макроса события, используйте что-то вроде:

Private Sub Worksheet_Change(ByVal Target As Range)
   If Intersect(Target, Range("A1:C100")) Is nothen Then
   Else
      Application.EnableEvents = False
         Call qwerty
      Application.EnableEvents = True
   End If
End Sub

Answer 3

Как я понимаю ваш вопрос, это невозможно.

Я подозреваю, что ответ, если он вообще существует, заключается в том, чтобы понять причину (бизнес-логику), стоящую за вами, желая такого поведения, а затем переформатировать рабочую книгу в соответствии с требованиями.