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

--

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

--

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

--

--

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

--

However, I like your style,Dude.

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

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.

--

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

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.

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

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

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