Function to automatically fill in an array of cells

[Guest]

Hello,

Is it possible to construct a function for the following problem:
I’m looking for a method in Excel to automatically fill in a range of cells
(column A), given a value for the step and an end value.
Example: If
InitialValue (A1) = 0.5
Step = 0.1
EndValue = 1
Then column A should go from 0.5 (in cell A1) to 1 (in cell A6) in steps of
0.1. If I adjust the step to 0.05, column A should automatically become 2
times longer.
Can somebody help me with this?
Thanks,
Maarten

 

[Guest]

Following is some code which I hope will put you on the right path

Public Function fill(initial_value, end_value, step_increment)

MaxVal = (end_value - initial_value) / step_increment + 1
Dim vl()
ReDim vl(MaxVal)

For z = initial_value To end_value Step step_increment


vl(cntr) = z
cntr = cntr + 1

Next

vl(cntr) = end_value
fill = vl

End Function

This function of course has to be entered as an array on a row, spanning the
number of columns for the total number of increments in your step that must
be taken. The application.thiscell method is extremely useful, but, not for
a function.

 

[HotRod]

I think you should be able to handle this in excel without using VBA code.
Can you give an example of two tables?

 

[Alan Beban]

Hello,

Is it possible to construct a function for the following problem:
I’m looking for a method in Excel to automatically fill in a range of cells
(column A), given a value for the step and an end value.
Example: If
InitialValue (A1) = 0.5
Step = 0.1
EndValue = 1
Then column A should go from 0.5 (in cell A1) to 1 (in cell A6) in steps of
0.1. If I adjust the step to 0.05, column A should automatically become 2
times longer.
Can somebody help me with this?
Thanks,
Maarten

Not quite enough info to provide a general "solution". E.g., if EndValue
is not included in the progression of values from InitialValue by steps
(e.g., InitialValue=.5, Step =.2, EndValue=1), what do you want to happen?

By the way, the previously posted code seems erratic. E.g, for your
illustration it produces .5,.6,.7,.8,.9,1,1

And for .5 to 1 with Step=.2 it produces .5,.7,.9,1,0

Alan Beban

 

[Guest]

I want to use the function in a model to automate the setting of the time
frame. So normally, an example like you gave, should not occur. But in case
it would, the function should return teh nearest integer.
If I get it right, the function would consist of a Do..loop sub, and
something like 'fill up next cell' as a statement, but I don't know much
about it ...
I hoped to be able to give in the funtion in the first cell (A1) and Excel
fills up automatically the rest of the cells as defined by the EndValue and
the StepValue.

Thanks in advance
Maarten

 

[Alan Beban]

Well, before resorting to VBA I would consider something like the following:

With your initial value in A1, the step value in B1 and the ending value
in C1, enter the following formula in A2 and fill down as far as will
likely be needed. After returning the end value it will return one
blank appearing cell then #VALUE!

=IF(OR(OFFSET($A$1,ROW(A1)-1,0)+$B$1>C$1,OFFSET($A$1,ROW(A1)-1,0)+$B$1=""),"",ROUND(OFFSET($A$1,ROW(A1)-1,0)+$B$1,10))

This assumes that you don't have the problem I referred to in my last
posting.

Alan Beban