1

I have A1 = 5 and I've added A1 as a name "Protagonist".

If i do =Protagonist its going to come up with 5.

Say in D12 I want to do =Protagonist+1 Its going to come up with 6.

  • What i want achieve is replace the "protagonist" with the 5 and have the D12 formula remain =5+1.

I need to do this as A1 constantly changes its value and I only need to keep the value it has at the time of making a formula that uses it.

(Manually i know i can select Protagonist and hit F9 to resolve only that part of the formula, but thats a major time consumption in the amounts i have to do it)

This can happen after the formula is entered and resolved. Though it would be so amazing if it could happen in the formula bar while typing, but I would guess vba doesn't stretch that far.

7
  • Do you want D12 to become: =A1 + 1 or =5 + 1 or =6 or 6 ?? Dec 14, 2015 at 14:33
  • It is mentioned - i want it to remain/become =5+1
    – helena4
    Dec 14, 2015 at 14:34
  • 1
    Could you please explain why you want to do this? If you explain the underlying problem, someone may very well know how to resolve the problem without resorting to this kind of solution.
    – Excellll
    Dec 14, 2015 at 14:49
  • So every time you type a formula with a named range, you want it to instead inject the current value of that named range and remain static going forward? Dec 14, 2015 at 15:03
  • Gary solved it. And if gave you guys the document and had you analyze it and me explain it to you it would be wasting much of your time.
    – helena4
    Dec 14, 2015 at 15:04

3 Answers 3

3

If you need to replace it with the calculated value then:

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

Will do it for all names in array also automatically as desired...

If there is need to do it for every name change the followimg 2 lines:

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

changes to:

For Each a In ThisWorkbook.Names

and

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

to

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

Just note that this is not case sensitive :)

8
  • Yeah, i use a ton of other names, doing it for everything is going to stab me. Hence the oddly named protagonist - that has no chance of appearing on my document in any form of text.
    – helena4
    Dec 14, 2015 at 15:18
  • as long as it is working like expected everything is fine... if you need any adjustements, just tell me... i'll add a second way... just a sec... Dec 14, 2015 at 15:32
  • Yeah i only need it done for protagonist. not every name. And you can bind it to a range A1 to C1000 if you don't mind. Doesn't need to scour the whole document looking for it.
    – helena4
    Dec 14, 2015 at 15:32
  • @helena4 changed it... just put all names to replace in the array (also works with just 1 item) Dec 14, 2015 at 15:37
  • Cool, feel free to bump up gary's answer, he put sweat into it too :) We are done.
    – helena4
    Dec 14, 2015 at 15:40
2

Give this a try:

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:

To call qwerty from an Event Macro, use something like:

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
4
  • Do you think it would be much of a performance drag if this is bound searching 3 columns 1000 rows and performing the replacement on cells found at the time a standard recalc triggers or me pressing enter? Otherwise i don't mind binding it to a button - Just asking.
    – helena4
    Dec 14, 2015 at 15:11
  • @helena4 You can always restrict it by using something like: Range("A1:C1000").SpecialCells(xlCellTypeFormulas) Dec 14, 2015 at 15:20
  • As far I grasp (and its not large knowledge base) this is only manually run. Can you bind it to a worksheet change and call it a day? And btw how much time does this vba stuff take to learn. I might lean on my boss to send me to a course so i can stop bothering you guys here with odd requests every now and then :)
    – helena4
    Dec 14, 2015 at 15:26
  • @helena4 Sure.....create an event macro and then have the Event macro call qwerty. Dec 14, 2015 at 15:30
0

As I understand your question, this isn't possible.

I suspect the answer, if one exists at all, is to understand the reasoning (the business logic) behind you wanting this behaviour, then reformatting the workbook to suit.

3
  • I'm a bit skeptical - I've used vba to replace stuff in cells before, just not parts of the whole. The name getting replaced after the formula is entered should be sufficient, even if it can't happen in the formula bar. If vba is a dead end this is solve-able with peripherals. I just need to buy a mouse that can bind macros to extra keys and paste ="="&Protagonist then hit 9 and continue onward. As for the logic can't fit a brief in here. Suffice to say i have to refer to a dynamic match in a ever growing entry list. And I haven't been able to simplify the goal/means any more.
    – helena4
    Dec 14, 2015 at 14:53
  • If you want to retrospectively change the cell value (protagonist+1) to its source value (5+1) then sure, that's simple and can even happen automatically with a WorksheetChange event, but that's not what I took from the question.
    – Lunatik
    Dec 14, 2015 at 14:56
  • Yes I clarified it to avoid any further interpretation. It simply meant I'd prefer it happen in the formula bar, but its cool it took place after the formula is entered.
    – helena4
    Dec 14, 2015 at 15:02

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .