Announcement

Collapse
No announcement yet.

Fill in sequential values (count) between two numbers.

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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


  • #2
    Hi Hal,

    I still not understand your request, can you attach your excel file?

    Comment


    • #3
      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?

      Comment


      • #4
        Here is one way.
        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

        Comment


        • #5
          Thank you StephenR. Much appreciated. Could you also help me with computing the gradient automatically? I have attached the worksheet. in Sheet1, Column C, there is the computed gradient. The start Temperature is in cell G3 and the step is in F3. I need that to populate using the "Make Gradient" button which also does the array for depth (Column B).

          Thanks,

          Hal
          Attached Files

          Comment


          • #6
            Originally posted by JonathanVH View Post
            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.

            Comment


            • #7
              Hal - not sure what you're asking. Does this cover it?
              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

              Comment


              • #8


                Thanks StephenR. Champion.

                Comment

                Working...
                X