8

So basically I want to write a formula that will check a condition, and if that condition is met, then I want to paste a specific line of text in a different cell. I should note that I do not want the formula to exist in the cell I want to paste a value into. So for example, if I want to paste a value into B5, I don't want the formula to be in cell B5...

7
  • 1
    Sounds more like you want a macro rather than a formula May 31, 2013 at 15:54
  • ^could you possible give me an example of a macro that would do that? I've never written a macro before... thanks! May 31, 2013 at 16:00
  • Welcome to Stack Overflow! When you need to change the nature of what you're asking, it is greatly preferred that you do so by editing your existing question, rather than by creating a new one. May 31, 2013 at 16:21
  • 1
    Please edit your question with more details like; what condition is being checked, how the data is arranged, how you intend to trigger this, etc. More importantly, what have you tried so far.
    – CharlieRB
    May 31, 2013 at 16:54
  • 1
    +1 to @Brad’s comment; I believe that it is impossible for a formula in one cell to modify another cell. Jun 1, 2013 at 1:26

5 Answers 5

8

The following approach makes use of a workaround described here to enable a worksheet function defined in VBA to set the value of another cell.

The custom function stores in global variables the address of the target cell and the value to which that cell is to be set. Then, a macro that is triggered when the worksheet recalculates reads the global variables and sets the target cell to the specified value.

Use of the custom function is straightforward:

  =SetCellValue(target_cell, value)

where target_cell is a string reference to a cell in the worksheet (e.g., "A1") or an expression that evaluates to such a reference. This includes an expression such as =B14 where the value of B14 is "A1". The function can be used in any valid expression.

SetCellValue returns 1 if the value is successfully written to the target cell, and 0 otherwise. Any previous contents of the target cell are overwritten.

Three pieces of code are needed:

  • the code defining SetCellValue itself
  • the macro that is triggered by the worksheet calculation event; and
  • a utility function IsCellAddress to ensure that target_cell is a valid cell address.

Code for SetCellValue Function

This code needs to be pasted into a standard module inserted into the workbook. The module can be inserted via the menu for the Visual Basic editor, which is accessed by selecting Visual Basic from the Developer tab of the ribbon.

  Option Explicit

  Public triggerIt As Boolean
  Public theTarget As String
  Public theValue As Variant

  Function SetCellValue(aCellAddress As String, aValue As Variant) As Long

      If (IsCellAddress(aCellAddress)) And _
             (Replace(Application.Caller.Address, "$", "") <> _
              Replace(UCase(aCellAddress), "$", "")) Then
          triggerIt = True
          theTarget = aCellAddress
          theValue = aValue
          SetCellValue = 1
      Else
          triggerIt = False
          SetCellValue = 0
      End If

  End Function

Worksheet_Calculate Macro Code

This code must be included in the code specific to the worksheet in which you will use SetCellValue. The easiest way to do this is to right-click the worksheet's tab in the Home view, select View Code, and then paste the code into the editor pane that comes up.

  Private Sub Worksheet_Calculate()

      If Not triggerIt Then
          Exit Sub
      End If
      triggerIt = False
      On Error GoTo CleanUp
      Application.EnableEvents = False
      Range(theTarget).Value = theValue
  CleanUp:
      Application.EnableEvents = True
      Application.Calculate

  End Sub

Code for IsCellAddress Function

This code can be pasted into the same module as the SetCellValue code.

  Function IsCellAddress(aValue As Variant) As Boolean

      IsCellAddress = False

      Dim rng As Range           ' Input is valid cell reference if it can be
      On Error GoTo GetOut       ' assigned to range variable
      Set rng = Range(aValue)
      On Error GoTo 0

      Dim colonPos As Long            'convert single cell "range" address to
      colonPos = InStr(aValue, ":")   'single cell reference ("A1:A1" -> "A1")
      If (colonPos <> 0) Then
          If (Left(aValue, colonPos - 1) = _
                Right(aValue, Len(aValue) - colonPos)) Then
              aValue = Left(aValue, colonPos - 1)
          End If
      End If

      If (rng.Rows.Count = 1) And _
          (rng.Columns.Count = 1) And _
          (InStr(aValue, "!") = 0) And _
          (InStr(aValue, ":") = 0) Then
          IsCellAddress = True
      End If                          'must be single cell address in this worksheet
      Exit Function

  GetOut:

  End Function
1
  • in "Worksheet_Calculate" wouldn't you Application.Calculate before Application.EnableEvents = True? My function gets calculated 94 times otherwise.
    – Nicolau
    Apr 30, 2018 at 17:35
2

Let's assume you want the text "Text A' to show in cell C5 if the cell B5 contains the value "green".

You can use a formula approach, but since formulas cannot change values in other cells, the formula will need to be entered into cell C5.

=IF(ISNUMBER(FIND("green",B5)),"Text A","")

Cell C5 will now only show "Text A" if B5 contains the word "green".

A formula like this can be built to work with many conditions. You need to define your requirements in order to get help on your specific situation.

If you don't want C5 to have a formula, you can also use a VBA approach. You can run a Worksheet Change event that will run whenever cell B5 is changed, either by manually editing the value or by pasting something into it.

An example for such a macro could be

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B5")) Is Nothing Then
    If InStr(1, Target, "green", vbTextCompare) Then
        Target.Offset(0, 1) = "Text A"
    End If
End If
End Sub

The conditions and the placement of the output are just an example, of course and need to be adjusted to your requirements.

The difference between the formula and the macro approach is

  • with the formula approach, cell C5 will contain a formula. If the user accidentally deletes the formula, the functionality it delivers will be deleted, too. (There are ways to manage that, though)
  • with the VBA macro, cell C5 will not show any formula, and will have the verbatim text as a value, but changing the setup requires knowledge of Excel VBA. Also, with a VBA approach, the workbook must be saved as a macro-enabled workbook and the user must allow macros or make the file a trusted file.

Note: the above is just an example. You need to define your requirements, whether to evaluate numbers or text, whether evaluation is case sensitive, what the evaluation rules are, where to place the result, etc.

1

To check if a condition is met, write an IF formula in some cell to update "a specific line of text in a different cell". The result cell will only contain the value of the formula, NOT the formula that generated that value, like so:

Working cell (eg. J5) =IF(A1="yes","Specific line of text","")
Result cell (eg. B5) =J5

So if the condition is met (A1="yes"), B5 will contain "Specific line of text". Otherwise it stays blank.

Notes:
Cells that have variable values will normally contain some kind of formula to keep updating it's values.

If you don't want anyone to know the real formula that's creating the value, the formula can go in some other cell to generate the value, which will then be copied to the result cell eg. B5.

To hide formula that generated the value or to hide the cell that B5 refers to (to keep checking if value needs updating), refer to:
https://support.office.com/en-us/article/display-or-hide-formulas-f7f5ab4e-bf24-4efc-8fc9-0c1b77a5356f

Other functions that can achieve the same effect includes CHOOSE, HLOOKUP, LOOKUP, VLOOKUP.

0

While using the IF function, Vineet wants to retain the old value in the cell if the condition is false. In other words, the value in a cell where the IF function is used should change only if the condition being tested by the IF function is true. By default, however, the IF function makes the value 0 if the condition is False.

The IF function can take up to three parameters. The first parameter is the comparison that is to be made, the second parameter is what should be returned if the comparison is true, and the third is what should be returned if the comparison is false. It is possible to leave off the last parameter, but if you do then Excel will return the value 0 if the comparison is false. (This is what Vineet is seeing returned by his IF function usage.)

The obvious solution, then, is to make sure that you provide the IF function with something that should be returned when the comparison is false. For instance, let's say that your formula is in cell B1 and you are comparing something in cell A1. The formula you use may look like this:

=IF(A1<10,"under ten",B1) Note that the words "under ten" are returned if the value in A1 is less than 10. If this condition is not met, then the value in B1 is returned. Since this formula is in cell B1, this means that the previous value in the cell is returned if the condition is false.

It also means that the formula contains a circular reference. For circular references to work OK you need to let Excel know that it is OK for them to occur in your worksheet. Follow these steps if you are using Excel 2010 or a later version:

1.Display the File tab of the ribbon and then click Options. Excel displays the Excel Options dialog box. 2. At the left side of the dialog box, choose Formulas. Make sure the Enable Iterative Calculation check box is selected. 3. Click OK.

-1

=value(cell)

Straight forward formula to use, had same issue and it works.

1
  • 1
    Exactly what issue did you have? It doesn’t look like you had the same issue, as your post doesn’t address any of the concerns in this question. Jan 18, 2020 at 8:00

You must log in to answer this question.

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