# 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)

Regards,

Hal

• Hi Hal,

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

• 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]

• 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

## Files

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

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!