With Range(Range("B1"),Range("B1").End(xlDown)).Offset(,-1)
.FormulaR1C1 = "=(ROW()-1)/COUNT(ColB)"
end with
"Saintsman" <(E-Mail Removed)> wrote in message
news:B06062A7-B60E-4631-895B-(E-Mail Removed)...
> I have 3 columns of data (ColB; ColD;ColF) Each column has a different
> number
> of rows. In colA I calculate a % value based on how many rows of data
> there
> are in colB ie in the table below there are 10 rows, so the values in colA
> are:
> first row = 10%; 2nd row = 20% etc up to 100%; if there were 20 rows then
> the values would be 5%; 10%; 15% etc up to 100%.
> I use the formula (ROW()-1)/(COUNT(ColB)) ColB is my named range
> I want to autofill the formula to the last row of data in the adjacent
> column
> ColA ColB
> 10.00% 18.26%
> 20.00% 11.59%
> 30.00% 9.69%
> 40.00% 6.67%
> 50.00% 3.53%
> 60.00% 1.78%
> 70.00% -2.19%
> 80.00% -3.33%
> 90.00% -10.90%
> 100.00% -14.63%
> I need to do this for each column with data, so the last row needs be
> specific to each column - I then produce a distribution chart for each set
> of
> data
> How do I code row()-1 in VB and then autofill the formula to run until no
> data in ColB?
> Any ideas?
>
|
Similar Threads
