On 1/16/2018 7:06 PM, Jianguo Li wrote:
Worksheet function can't change the other cells' value. It's the Excel's
rule.

Explicitly, yes; but "rules are made to be broken!" :)

And I don't understand what you say.

OK, here's the working implementation --

Function SetCell(RefCell As Range, _
ColumnOffset As Long, _
SourceCell As Range, _
TargetCell As Range)
' Writes value from SourceCell at TargetCell plus above column Offset
' relative to RefCell, the location containing the function expression

offSrce=SourceCell.Column-RefCell.Column ' offset to source data
offTarg=TargetCell.Column-RefCell.Column+ColumnOffset ' offset target

' Build call to pass to EVALUATE method as
' SetValue(RefCell,OffSetData,OffSetTarget)

cmd = "SetValue(" & RefCell.Address(False, False) & "," & _
offSrce & "," & offTarg & ")"
' Pass command string to Evaluate to execute Sub
RefCell.Parent.Evaluate cmd

' Satisfy the "rule" to set value of RefCell where the function lives
SetCell = "" ' empty .Value doesn't destroy .Formula so
End Function


Sub SetValue(RefCell As Range, offSrce As Long, offTarg As Long)
' Set cell at OFFSET(REFCELL(0,offTARG) with value from
' OFFSET(REFCELL(0,offSRCE)
' Uses SUB to do dirty work since UDF can't directly alter itself

RefCell.Offset(0, offTarg).Value = RefCell.Offset(0, offSrce).Value
End Sub

And, just for funs, another function found that allows you to add
IntelliSense help for UDFs...

Sub DescribeFunction()
Dim FuncName As String
Dim FuncDesc As String
Dim Category As String
Dim ArgDesc(1 To 3) As String

FuncName = "SETCELL"
FuncDesc = "Sets cell at offset to target origin to source value"
Category = 14 ' user defined
ArgDesc(1) = "Range of Cell Containing Function"
ArgDesc(2) = "Offset from Target Origin as Value"
ArgDesc(3) = "Range (Cell) Containing Source Data"
ArgDesc(4) = "Range (Cell) Origin for Target Data"

Application.MacroOptions _
Macro:=FuncName, _
Description:=FuncDesc, _
Category:=Category, _
ArgumentDescriptions:=ArgDesc
End Sub

Execute the above only once after you've got the user interface for the
function defined in its final form.

EXAMPLE USE:

Entering

=SetCell($U9,MATCH(H9,LookupValues,0)-1,D9,V9)

in U9 writes the value in D9 at V9 plus the offset determined by the
MATCH index found for the value H9 found in the table LookupValues. The
values in LookupValues table must be in the order of the desired column
in the target, of course. (The "minus one" just converts to 0-based
indexing from the one-based result returned by MATCH.)

The "trick" is using the SUB to do the dirty work and .Evaluate method
to do so. I still haven't determined why one can't CALL the SUB; see
the earlier followup.

Took some tinkering and isn't _quite_ as clean as would like but does
save the manual copying of a large target area in lieu of simply the one
column. As conventional programming language user almost exclusively,
the overhead in the need to copy everything over and over and over again
in Excel is just horribly frustrating; this is one small step away from
that.

Wouldn't recommend for random use; as noted this was, in part, a quest
to break up the otherwise monotonous...

--

--