Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function to automatically fill in an array of cells
Hello,
Is it possible to construct a function for the following problem: Im 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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function to automatically fill in an array of cells
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. -- http://HelpExcel.com 1-888-INGENIO 1-888-464-3646 x0197758 "Maarten" wrote: Hello, Is it possible to construct a function for the following problem: Im 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function to automatically fill in an array of cells
I think you should be able to handle this in excel without using VBA code.
Can you give an example of two tables? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function to automatically fill in an array of cells
Maarten wrote:
Hello, Is it possible to construct a function for the following problem: Im 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function to automatically fill in an array of cells
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" wrote: Maarten wrote: Hello, Is it possible to construct a function for the following problem: Im 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function to automatically fill in an array of cells
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$1C$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 Maarten wrote: 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" wrote: Maarten wrote: Hello, Is it possible to construct a function for the following problem: Im 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to have cells automatically fill when entering value in ref | Excel Discussion (Misc queries) | |||
how to have cells automatically fill when entering value in ref | Excel Discussion (Misc queries) | |||
Fill in cells with an array | Excel Discussion (Misc queries) | |||
Help using the 'If' function to automatically fill in other cells | Excel Worksheet Functions | |||
need to fill cells automatically | Excel Worksheet Functions |