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

--

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

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

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.

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