How do you write an Excel formula that will paste a specific value in a different cell?
The following approach makes use of a workaround described here and 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:
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.
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.