Solved: VB to insert missing rows with average of rows around it

[eversharp]

I have an Excel file that looks like this in Columns A, B, and C

1/1/2012 | $1.03 | Tool Type 7XA
1/4/2012 | $1.06 | Tool Type 7XA
1/8/2012 | $1.01 | Tool Type 7XA
1/9/2012 | $2.09 | Tool Type 7XA

I would like a Macro to insert the missing dates using the average of the date before and after them....

For example it would insert rows 1/2/2012 and 1/3/2012 with a price of $1.045 (the average of 1/1 and 1/4)

then it would insert 1/5, 1/6, and 1/7 with the average of the 1/4 and 1/8 prices... and so on.



Can anyone help with the syntax?

Thanks much!

[Bob Phillips]

[VBA]Public Sub ProcessData()
Dim inAmount As Double
Dim Lastrow As Long
Dim numRows As Long
Dim i As Long

Application.ScreenUpdating = False

With ActiveSheet

Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = Lastrow - 1 To 1 Step -1

If .Cells(i, "A").Value <> .Cells(i + 1, "A").Value - 1 Then

numRows = .Cells(i + 1, "A").Value - .Cells(i, "A").Value - 1
inAmount = Round((.Cells(i + 1, "B").Value + .Cells(i, "B").Value) / 2, 2)
.Rows(i + 1).Resize(numRows).Insert
.Cells(i, "A").AutoFill .Cells(i, "A").Resize(numRows + 1)
.Cells(i + 1, "B").Resize(numRows).Value = inAmount
End If
Next i
End With

Application.ScreenUpdating = True
End Sub[/VBA]

[eversharp]

Works great, thanks xld!