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...
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:
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
- the macro that is triggered by the worksheet calculation event; and
- a utility function
IsCellAddressto ensure that
target_cellis 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
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
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.
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.
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)
So if the condition is met (A1="yes"), B5 will contain "Specific line of text". Otherwise it stays blank.
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:
Other functions that can achieve the same effect includes CHOOSE, HLOOKUP, LOOKUP, VLOOKUP.