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

Groups > microsoft.public.excel.programming > #110449 > unrolled thread

Enter value in compute cell location?

Back to article view | Back to microsoft.public.excel.programming

Contents

  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

#110449 — Enter value in compute cell location?

Is it possible to have the result of an expression generate the address 
into which to enter a value?  Example would be given a State variable of 
one of (say) three values, place another cell value in one of three 
columns dependent upon the state.  It's simple enough to duplicate a 
lookup function in every cell of all three columns, but that seems 
excessive.

       GIVEN

     A   B   C   D   E   F
1  10   'A
2   5   'C
3  20   'A
4   2   'B
.
.
.


         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...

--

[toc] | [next] | [standalone]

#110452

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

--

[toc] | [prev] | [next] | [standalone]

#110453

Worksheet function can't change the other cells' value. It's the Excel's 
rule.

And I don't understand what you say.

"dpb" <none@none.net> 写入消息 news:p3lu7i$btb$1@gioia.aioe.org...
> Is it possible to have the result of an expression generate the address 
> into which to enter a value?  Example would be given a State variable of 
> one of (say) three values, place another cell value in one of three 
> columns dependent upon the state.  It's simple enough to duplicate a 
> lookup function in every cell of all three columns, but that seems 
> excessive.
>
>       GIVEN
>
>     A   B   C   D   E   F
> 1  10   'A
> 2   5   'C
> 3  20   'A
> 4   2   'B
> .
> .
> .
>
>
>         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...
>
> --
> 

[toc] | [prev] | [next] | [standalone]

#110454

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...

--

--

[toc] | [prev] | [next] | [standalone]

#110455

On 1/22/2018 8:39 AM, dpb wrote:
> On 1/16/2018 7:06 PM, Jianguo Li wrote:
...

DANG!  Need to fix a couple typos...

> OK, here's the working implementation --
...


> 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

Just saw hadn't made intended edit to above comment -- should read as or 
equivalent to

' Uses SUB via .Evaluate to do dirty work since UDF can't directly alter
' cell location other than itself

...

> 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
...

For the specific example, there are four arguments so

      Dim ArgDesc(1 To 4) As String

--

[toc] | [prev] | [next] | [standalone]

#110456

dpb;1630101 Wrote: 
> 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!"  :)
> [color=blue][i]
> --

However, I like your style,Dude.




-- 
killme2008

[toc] | [prev] | [next] | [standalone]

#110457

Perhaps this Sub might be of a little help, aswell as the 'call' in front of the called sub appears not to be mandatory.

This macro does the trick as per your request:
What it performs is by 2 absolute cellreferences controling initial row and column numbering.

a few dims and a for next loop do the dirty work

have fun.



Option Explicit

Sub SplitValCol()
Dim colCount As Integer
Dim rowcount As Integer
Dim celVal As Range
Dim amt As Integer
Dim rownr As Long
ActiveCell.SpecialCells(xlLastCell).Select
rownr = ActiveCell.Row
Range("A1").Select

colCount = 2
rowcount = 2

For rowcount = 2 To rownr

If Cells(rowcount, colCount) = "a" Then
amt = Cells(rowcount, colCount - 1)
Cells(rowcount, colCount + 2) = amt

Else

If Cells(rowcount, colCount) = "b" Then
amt = Cells(rowcount, colCount - 1)
Cells(rowcount, colCount + 3) = amt

Else

If Cells(rowcount, colCount) = "c" Then
amt = Cells(rowcount, colCount - 1)
Cells(rowcount, colCount + 4) = amt

End If
End If
End If

Next rowcount

End Sub

[toc] | [prev] | [next] | [standalone]

#110458

On 1/24/2018 5:34 AM, Gerry Timber wrote:
> Perhaps this Sub might be of a little help, aswell as the 'call' in front of the called sub appears not to be mandatory.
> 
> This macro does the trick as per your request:
> What it performs is by 2 absolute cellreferences controling initial row and column numbering.
> 
> a few dims and a for next loop do the dirty work
> 
> have fun.
> 
> 
> 
> Option Explicit
> 
> Sub SplitValCol()
> Dim colCount As Integer
> Dim rowcount As Integer
> Dim celVal As Range
> Dim amt As Integer
> Dim rownr As Long
> ActiveCell.SpecialCells(xlLastCell).Select
> rownr = ActiveCell.Row
> Range("A1").Select
> colCount = 2
> rowcount = 2
> For rowcount = 2 To rownr
>   If Cells(rowcount, colCount) = "a" Then
>     amt = Cells(rowcount, colCount - 1)
>     Cells(rowcount, colCount + 2) = amt
>   Else
>     If Cells(rowcount, colCount) = "b" Then
>       amt = Cells(rowcount, colCount - 1)
>       Cells(rowcount, colCount + 3) = amt
>     Else
>       If Cells(rowcount, colCount) = "c" Then
>         amt = Cells(rowcount, colCount - 1)
>         Cells(rowcount, colCount + 4) = amt
>       End If
>     End If
>   End If
> Next rowcount
> End Sub
> 

This has the problem of being depending upon the size of the table 
explicitly; the example was just three columns for illustration 
purposes, the actual could be/is MUCH larger.

I don't know VBA well at all but looks like a variable naming issue?

 > rowcount = 2
 > For rowcount = 2 To rownr
 >   If Cells(rowcount, colCount) = "a" Then
 >     amt = Cells(rowcount, colCount - 1)

Is the ROWCOUNT variable as the loop index really different from the one 
of the same name outside the loop?  But even if were, the reference 
inside will pull the AMT value from the relative location to it, not 
from the fixed source location.

But, I see where you were headed; I started down that path but wasn't 
pleased with the result.  The solution posted above requires the lookup 
as the argument which is a little annoying and a superfluous reference 
to the cell location but if one uses ADDRESS() rather than the explicit 
reference one starts an evaluation cascade.

Seemed like one should be able to eliminate that argument entirely but I 
couldn't get it to work with Me.Address or other syntax--I'm pretty sure 
that is likely a result of simply not knowing enough VBA and that the 
doc is so tough to find what is the method/property that you're really 
looking for...so I finally just left it as is as "good enough" to go on 
to the next issue.

--

[toc] | [prev] | [next] | [standalone]

#110459

Adding to dpb's reply..

Row/column counts/indices are Type 'Long' and so should ALWAYS be defined as 
such since their number can easily exceed the bounds of Type 'Integer'!

-- 
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
  comp.lang.basic.visual.misc
  microsoft.public.vb.general.discussion

[toc] | [prev] | [next] | [standalone]

#110461

Perhaps...

Sub DisperseValues()
  Dim RefCol&, LastRow&, n&

  LastRow = Cells(Rows.Count, "B").End(xlUp).Row

  For n = 2 To LastRow
    Select Case UCase$(Cells(n, RefCol))
      Case "A": RefCol = 2
      Case "B": RefCol = 3
      Case "C": RefCol = 4
      '...
    End Select
    Cells(n, RefCol) = Cells(n, "A")
  Next 'n
End Sub

-- 
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
  comp.lang.basic.visual.misc
  microsoft.public.vb.general.discussion

[toc] | [prev] | [next] | [standalone]

#110462

Oops.., typo!

> Sub DisperseValues()
>   Dim RefCol&, LastRow&, n&
>
>   LastRow = Cells(Rows.Count, "B").End(xlUp).Row
>
>   For n = 2 To LastRow
    Select Case UCase$(Cells(n, "B"))
>       Case "A": RefCol = 2
>       Case "B": RefCol = 3
>       Case "C": RefCol = 4
>       '...
>     End Select
>     Cells(n, RefCol) = Cells(n, "A")
>   Next 'n
> End Sub

-- 
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
  comp.lang.basic.visual.misc
  microsoft.public.vb.general.discussion

[toc] | [prev] | [next] | [standalone]

#110460

On Tuesday, January 16, 2018 at 11:27:10 PM UTC+1, dpb wrote:
> Is it possible to have the result of an expression generate the address 
> into which to enter a value?  Example would be given a State variable of 
> one of (say) three values, place another cell value in one of three 
> columns dependent upon the state.  It's simple enough to duplicate a 
> lookup function in every cell of all three columns, but that seems 
> excessive.
> 
>        GIVEN
> 
>      A   B   C   D   E   F
> 1  10   'A
> 2   5   'C
> 3  20   'A
> 4   2   'B
> .
> .
> .
> 
> 
>          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...
> 
> --

@GS 
Yes, thank you for the type LONG adjustment

@dpb

As to the rowcount, it is the same during each step in the For Next loop. 
It is increased with 1 every step in the For  Next loop, as it goes through. 

This enables the 3 If Then constructs where rowcount to maintain same value. 

The - (substraction) which takes place to copy value "amt" and the + (addition) to place this copied "amt" in the subsequent column donot have effect on the variable values.
Anyhow, thank you.

[toc] | [prev] | [next] | [standalone]

#110463

A bit easier to work with than my 1st version because it uses column labels...

Sub DisperseValues2()
  Dim sCol$, LastRow&, n&

  LastRow = Cells(Rows.Count, "B").End(xlUp).Row

  For n = 2 To LastRow
    Select Case UCase$(Cells(n, "B"))
      Case "A": sCol = "D"
      Case "B": sCol = "E"
      Case "C": sCol = "F"
      '...
    End Select
    Cells(n, sCol) = Cells(n, "A")
  Next 'n
End Sub

-- 
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
  comp.lang.basic.visual.misc
  microsoft.public.vb.general.discussion

[toc] | [prev] | [standalone]

Back to top | Article view | microsoft.public.excel.programming

csiph-web