assign variable in data fill

Topic

New Reply

I have a series of irregularly spaced data for which I need to fill in daily data. The data are recorded anywhere from every 1 to 3 weeks and the filled in data should be a linear series between the two data points. The macro below is what I recorded. How do I get the step value to vary to reflect the differing beginning and end values? A sample portion of the data is attached.

Thanks!

Sub Macro6()
Range(Selection, Selection.End(xlDown)).Select
Selection.DataSeries Rowcol:=xlColumns, Type:=xlLinear, Date:=xlDay, _
Step:=-1.99999999999984E-03, Trend:=False
Selection.End(xlDown).Select
Range(Selection, Selection.End(xlDown)).Select
End Sub

Reply | Quote

Replies

I am not sure what you want to accomplish, exactly. Could you elaborate on the goal?

Steve

Reply | Quote

jepalmer,

Here is code that will do what I think you want. It will fill in incremented values between the points in column B (yellow highlight). The increment is calculated by the difference of two consecutive points divided by the number of dates spanned between them.

HTH,
Maud

35054-stepvalues1 35055-Stepvalues2

Code:

Public Sub IncrementValues()
[COLOR=”#008000″]’DECLARE VARIABLES[/COLOR]
Dim rng As Range
Dim cell
Dim cell1 As Range
Dim cell2 As Range
Dim increment As Double
Dim stepvalue As Double
Dim lastrow As Integer

Application.ScreenUpdating = False
[COLOR=”#008000″][/COLOR][COLOR=”#008000″]’————————————————————-
‘SET VARIABLES[/COLOR]
lastrow = ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Row
[b2].Select
repeat:
Set cell1 = Selection
Set cell2 = Selection.End(xlDown)
Set rng = Range(cell1.Offset(1, 0), cell2.Offset(-1, 0))
[COLOR=”#008000″]’————————————————————-
‘CALCULATE AND INSERT INCREMENTS[/COLOR]
increment = (cell2.Value – cell1.Value) / (cell2.Row – cell1.Row)
rng.Select
stepvalue = cell1.Value
For Each cell In rng
    cell.Value = stepvalue + increment
    stepvalue = cell.Value
Next cell
cell1.Offset(0, 1).Value = increment
cell2.Select
[COLOR=”#008000″]’————————————————————-
‘DETERMINE IF LAST SECTION CALCULATED[/COLOR]
If cell2.Row >= lastrow Then
    [b2].Select
    Exit Sub
End If
GoTo repeat
Application.ScreenUpdating = True
End Sub

Reply | Quote

Hi Maudibe

Where have you been – you have been missed.

I like your solution.
Nice work.

I added a chart to your solution.
So I could see what’s going on.
Yeah, I know it wasn’t asked for.

zeddy

Reply | Quote

wow.
THANKS!

Reply | Quote

Zeddy,

Thanks, tweaks are cool. Nice touch with the graph!

JP,
you are more than welcome. Forgot to mention that you can add as many sections as to the number of rows the sheet will hold. The code will adjust. Just remember that the final section must have a trailing point in column B or that section will be ignored. If you choose to adapt the code in your project then:

1. Change the 2 in the line lastrow = ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Row [/COLOR]to the column that has the points.
2. Change b2 in the code line [b2].Select [/COLOR]to the cell of the first point. There are 2 instances.
3. Add the code to a standard module

Maud

Reply | Quote