Fill series macro with different steps

  • Thread starter Thread starter PMF
  • Start date Start date
P

PMF

HI

I want to fill a sereis based on the first and last numbers in the
selection, with the step set to linear interoplate between the first
and last numbers. Does anyone suggest some VBA code that would help?

This is the same as selecting two numbers with blanks in between and
then doing Edit:fill:Series. Excel automatically works out the correct
step. However, if I record a macro for this, the step doesn't is fixed
to the step value I recorded it with. So it doesn't help if I want to
do this many many times....

Cheers
 
Sub LinearFill()
Selection.DataSeries Type:=xlLinear, Trend:=True
End Sub

Does that do what you want?

Troy
 
Troy

Thanks, that's so much better than what I eventually came up with:

Sub linear_fill()
bot = UBound(Selection.Value, 1)
num = bot - 1

a = Selection(1, 1).Value
b = Selection(bot, 1).Value
For i = 2 To bot - 1
m = (b - a) / num
Selection(i, 1).Value = a + m * (i - 1)
Next i

End Sub
!!!
Cheers
Piers
 
Back
Top