Results 1 to 6 of 6
  1. #1
    4 Star Lounger
    Join Date
    May 2002
    Posts
    410
    Thanks
    1
    Thanked 0 Times in 0 Posts

    assign variable in data fill

    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
    Attached Files Attached Files

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    I am not sure what you want to accomplish, exactly. Could you elaborate on the goal?

    Steve

  3. #3
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,634
    Thanks
    115
    Thanked 649 Times in 592 Posts
    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

    stepvalues1.png Stepvalues2.png

    Code:
    Public Sub IncrementValues()
    'DECLARE VARIABLES
    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
    '-------------------------------------------------------------
    'SET VARIABLES
    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))
    '-------------------------------------------------------------
    'CALCULATE AND INSERT INCREMENTS
    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
    '-------------------------------------------------------------
    'DETERMINE IF LAST SECTION CALCULATED
    If cell2.Row >= lastrow Then
        [b2].Select
        Exit Sub
    End If
    GoTo repeat
    Application.ScreenUpdating = True
    End Sub
    Attached Files Attached Files

  4. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,823
    Thanks
    135
    Thanked 482 Times in 459 Posts
    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
    Attached Files Attached Files

  5. The Following User Says Thank You to zeddy For This Useful Post:

    Maudibe (2013-10-04)

  6. #5
    4 Star Lounger
    Join Date
    May 2002
    Posts
    410
    Thanks
    1
    Thanked 0 Times in 0 Posts
    wow.
    THANKS!

  7. #6
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,634
    Thanks
    115
    Thanked 649 Times in 592 Posts
    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 to the column that has the points.
    2. Change b2 in the code line [b2].Select to the cell of the first point. There are 2 instances.
    3. Add the code to a standard module

    Maud

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •