Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > microsoft.public.excel.programming > #110452

Re: Enter value in compute cell location?

Show all headers | View raw

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!  :)

--

Back to microsoft.public.excel.programming | Previous | Next — Previous in thread | Next in thread | Find similar | Unroll thread

Thread

Enter value in compute cell location? dpb <none@none.net> - 2018-01-16 16:26 -0600
  Re: Enter value in compute cell location? dpb <none@none.net> - 2018-01-18 13:56 -0600
  Re: Enter value in compute cell location? "Jianguo Li" <2@1.com> - 2018-01-17 09:06 +0800
    Re: Enter value in compute cell location? dpb <none@none.net> - 2018-01-22 08:39 -0600
      Re: Enter value in compute cell location? dpb <none@none.net> - 2018-01-22 08:51 -0600
      Re: Enter value in compute cell location? killme2008 <killme2008.14adfdf8@excelbanter.com> - 2018-01-24 08:11 +0000
  Re: Enter value in compute cell location? Gerry Timber <swa2194@gmail.com> - 2018-01-24 03:34 -0800
    Re: Enter value in compute cell location? dpb <none@none.net> - 2018-01-24 09:26 -0600
    Re: Enter value in compute cell location? GS <gs@v.invalid> - 2018-01-24 10:37 -0500
    Re: Enter value in compute cell location? GS <gs@v.invalid> - 2018-01-24 19:09 -0500
      Re: Enter value in compute cell location? GS <gs@v.invalid> - 2018-01-24 19:12 -0500
  Re: Enter value in compute cell location? Gerry Timber <swa2194@gmail.com> - 2018-01-24 12:07 -0800
  Re: Enter value in compute cell location? GS <gs@v.invalid> - 2018-01-24 19:29 -0500

csiph-web