Fill in sequential values (count) between two numbers.

    Hi Everyone. Thanks in advance for helping me out.


    I am making a Gradient Calculator for export. I need your help on how to create a code to automatically fill in the values between the "Top Depth" and "Bottom Depth" in increments of 1.


    Example:
    Top Depth = 5 (Value is in cell H3) Manually input by user on active sheet
    Bottom Depth = 444 (Value is in cell I3) Manually input by user on active sheet


    I am looking for a code that puts the "Top Depth" into cell B3 and then counts down to 444 in increments of 1. (Basically auto filling without dragging the cells)


    In addition to this, I would like the formula in column C to auto fill down to "Bottom Depth" (I3)


    Your time is appreciated.


    Regards,


    Hal

    That's possible with an array formula in the maximum number of rows of column B, but it would be more efficient to use VBA. Is that allowed?


    How does what you want in column C differ from what you want in column B?

    Here is one way.
    [VBA]With Range("B3")
    .Value = Range("H3").Value
    .DataSeries Rowcol:=xlColumns, Type:=xlLinear, Date:=xlDay, Step _
    :=1, Stop:=Range("I3").Value, Trend:=False
    .Offset(, 1).Copy .Offset(, 1).Resize(.CurrentRegion.Rows.Count)
    End With[/VBA]

    Quote from JonathanVH

    That's possible with an array formula in the maximum number of rows of column B, but it would be more efficient to use VBA. Is that allowed?


    How does what you want in column C differ from what you want in column B?


    Column C will have a different step and start value. The Step is in cell F3 and start value is in G3.

    Hal - not sure what you're asking. Does this cover it?
    [VBA]Sub Gradient()


    Dim n As Long


    n = Range("B3", Range("B" & Rows.Count).End(xlUp)).Count - 1


    With Range("C3")
    .Value = Range("G3").Value
    .DataSeries Rowcol:=xlColumns, Type:=xlLinear, Date:=xlDay, Step:=Range("F3").Value, _
    Stop:=Range("G3").Value + n * Range("F3").Value, Trend:=False
    End With


    End Sub[/VBA]

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login