Inserting Rows based on cell differences and interpolating in between

[unknown_member]

Hello -

I have a complicated issue. I have a file with 5 columns (A, B, C, D, E).
A is fixed string value.
B is a number increasing irregularly.
C, D, E, are given values.

What I need to do is have column B regular (i.e. every 1). So I have to insert rows between B i.e.(B2-B1) will determine how many rows between B1 & B2 and then (B3-B2) will determine how many rows between B2 & B3 and so on.
After inserting the rows, I have to linear interpolate columns C, D & E in the newly created empty rows. In all cases I want to keep the original values and interpolate between them.

The other issue is that column B has decimal/fraction, but I think I can round this to the nearest integer to make it easier for interpolation.

Example Input: http://i65.tinypic.com/v30kz6.<wbr>jpg
Desired output: http://i66.tinypic.com/<wbr>2hrod4n.jpg

 

[unknown_member]

No help?

 

[unknown_member]

I tried this one but I think I have a problem with the insert loop!

Option Explicit
Sub Test01()
Application.ScreenUpdating = False
Dim numRows As Long
Dim r As Long
Dim Rng As Range
Dim lastrw As Long
Dim Ar As Range
Dim StepValue1
Dim StepValue2
Dim StepValue3
Dim Ar1 As Range
Dim AR2 As Range

Dim i As Integer
lastrw = Cells(Rows.Count, "A").End(xlUp).Row
   
    i = 1
    For i = i + 0 To lastrw Step 1
        
    Set Rng = Range(Cells(i, "A"), Cells(lastrw, "A"))
    numRows = Cells(i + 1, 2).Value - Cells(i + 0, 2).Value
        
        For r = Rng.Rows.Count To 1 Step -1
        Rng.Rows(r + i).Resize(numRows - 1).EntireRow.Insert
        Next r
    Next i
      
Set Rng = Columns(1).SpecialCells(xlBlanks)
For Each Ar In Rng.Areas
Set Ar1 = Ar.Offset(-1, 0).Resize(Ar.Rows.Count + 1)
Set AR2 = Ar1.Resize(Ar1.Rows.Count + 1)

StepValue1 = (AR2(AR2.Count).Offset(0, 2) - _
Ar1(1).Offset(0, 2)) / Ar1.Count

StepValue2 = (AR2(AR2.Count).Offset(0, 3) - _
Ar1(1).Offset(0, 3)) / Ar1.Count

StepValue3 = (AR2(AR2.Count).Offset(0, 4) - _
Ar1(1).Offset(0, 4)) / Ar1.Count

Ar1.Offset(0, 2).DataSeries Rowcol:=xlColumns, _
Type:=xlLinear, Date:=xlDay, _
Step:=StepValue1, Trend:=False

Ar1.Offset(0, 3).DataSeries Rowcol:=xlColumns, _
Type:=xlLinear, Date:=xlDay, _
Step:=StepValue2, Trend:=False

Ar1.Offset(0, 4).DataSeries Rowcol:=xlColumns, _
Type:=xlLinear, Date:=xlDay, _
Step:=StepValue3, Trend:=False

Next

End Sub

 

[unknown_member]

Example worksheet

11111xx222	0.00	525.00	252400.00	9707553.00
11111xx222	12.58	629.00	257845.00	9713438.00
11111xx222	26.90	630.00	263445.00	9719433.00
11111xx222	243.00	850.00	269234.00	9726318.00

<tbody>
</tbody>

https://ufile.io/f8944

 

[unknown_member]

Other posts I have in other forums - I apologize for not posting these earlier:
Inserting Rows based on cell differences and interpolating in between
Inserting Rows based on cell differences and interpolating in between - ExcelBanter
Question Inserting Rows based on cell differences and interpolating in between
https://groups.google.com/forum/#!topic/microsoft.public.excel.programming/gjmgjk3KyU8

 

[unknown_member]

I had help here: [SOLVED] Inserting Rows based on cell differences and interpolating in between and it works perfectly now.