On 1/16/2018 4:26 PM, dpb wrote:
Is it possible to have the result of an expression generate the address
into which to enter a value?Â* ...
....snip...
Â*Â*Â*Â*Â*Â*Â* WANTED
Â*Â*Â* AÂ*Â*Â* BÂ*Â*Â* CÂ*Â* DÂ*Â* EÂ*Â* F
1Â* 10Â*Â* 'AÂ*Â*Â* ?Â* 10
2Â*Â* 5Â*Â* 'CÂ*Â*Â* ?Â*Â*Â*Â*Â*Â*Â*Â*Â*Â* 5
3Â* 20Â*Â* 'AÂ*Â*Â* ?Â* 20
4Â*Â* 2Â*Â* 'BÂ*Â*Â* ?Â*Â*Â*Â*Â*Â* 2
.
.
.
A formula in column C using B and a lookup can create D thru F (the "?")
would be...
I did find a workaround on a blog site -- use the subterfuge of a UDF
that calls a SUB to do the dirty work...
Function SetCell(RefCell As Range)
' Sets a target cell from the range containing the UDF
RefCell.Parent.Evaluate "SetValue(" & RefCell.Address(False,False) & ")"
SetCell = "" ' Must set return value of UDF -- hides the formula
End Function
Sub SetValue(RefCell As Range)
' Set cell column+1 to value in cell column-2 relative current cell
RefCell.Offset(,1).value = RefCell.Offset(,-2).value
End Sub
So if enter
=SetCell(C1)
into C1 of the example spreadsheet above, the value '10' magically
appears in D1. That's the simplest of fixed offsets, simply computing
the desired offset and passing it to SETVALUE is easy enough as well as
adding target additional input parameters to SETCELL to have either the
target range or numeric offset to the beginning of the target passed.
This example use EVALUATE to execute the SUB; I wondered why CALL
couldn't be used instead to make coding a little less obtuse; seemed
like however the SUB got executed shouldn't matter?
But, however, using
Function SetCell(RefCell As Range)
' Sets a target cell from the range containing the UDF
Call SetValue(RefCell)
SetCell = "" ' Must set return value of UDF -- hides the formula
End Function
fails with the dreaded #VALUE error and setting breakpoint in SUB
SetValue shows that the LHS reference to RefCell.Offset(,1).Value is
empty whereas the RHS reference is valid.
I "know nuthink!" (as Sgt Schultzie would say) of VBA/Excel being a
Fortran/Matlab guy so I'm at a loss as to what is different between the
two invocations???
I haven't done anything to speak of w/ Excel since back in the '90s and
so have forgotten virtually everything I ever knew of
VB/VBA and find
the current documentation almost unusable in its organization as
essentially independent pages with no TOC or index or search... :(
Ran into the problem looking into some stuff for a nonprofit I volunteer
for. The target area can be sizable and just seems a pain to have to
copy the formulae over so much that I got intrigued in trying to make
something more concise. To get something done I'll just use what I
found or revert to the copy/paste route, but now I'm on a mission! :)
--