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.

--