|
Option Explicit
Sub test()
Dim arr, i, j, k, kk, ave
arr = Range("d3:t" & Cells(Rows.Count, "c").End(xlUp).Row)
For i = 1 To UBound(arr, 1)
For j = 1 To UBound(arr, 2)
If Len(arr(i, j)) Then Exit For
Next
If j > 1 Then
ave = arr(i, j) - arr(i, j + 1)
For j = j - 1 To 1 Step -1
arr(i, j) = arr(i, j + 1) + ave
Next
End If
For j = 1 To UBound(arr, 2)
If Len(arr(i, j)) = 0 Then
For k = j + 1 To UBound(arr, 2)
If Len(arr(i, k)) Then
ave = (arr(i, k) - arr(i, j - 1)) / (k - j + 1)
For kk = j To k - 1
arr(i, kk) = Round(arr(i, kk - 1) + ave, 2)
Next
j = k: Exit For
End If
Next
If k = UBound(arr, 2) + 1 Then
ave = arr(i, j - 2) - arr(i, j - 1)
For k = j To UBound(arr, 2)
arr(i, k) = Round(arr(i, k - 1) - ave, 2)
Next
End If
End If
Next
Next
[d3].Resize(UBound(arr, 1), UBound(arr, 2)) = arr
End Sub |
|