9

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...

Robert Jefferies
  • 111

5 Answers5

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
Community
  • 1
chuff
  • 3,534
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.

teylyn
  • 23,615
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.

Zimba
  • 1,291
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.

Anthony Oyedeji
  • 1
-1

=value(cell)

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

Kgaogelo Pitsi
  • 1