1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80
|
Function FindNextHole(ByVal sheet As String, ByVal line As Integer, ByVal CurrentCol As Integer, ByVal EndOfLineCol As Integer) As Integer
'Recherche de la première case vide
Dim col As Integer
col = CurrentCol
Do While Not IsEmpty(Worksheets(sheet).Cells(line, col)) And col <= EndOfLineCol
col = col + 1
Loop
FindNextHole = col
End Function
Function FindNextVal(ByVal sheet As String, ByVal line As Integer, ByVal CurrentCol As Integer, ByVal EndOfLineCol As Integer) As Integer
'Recherche de la première case non vide
Dim col As Integer
col = CurrentCol
Do While IsEmpty(Worksheets(sheet).Cells(line, col)) And col <= EndOfLineCol
col = col + 1
Loop
FindNextVal = col
End Function
Function FillCellsWithInterpolatedValues(ByVal sheet As String, ByVal line As Integer, ByVal ColFirstVal As Integer, ByVal ColEndVal As Integer) As Boolean
Dim step As Double
Dim FirstVal As Double
Dim NbEmptyCells As Integer
FirstVal = Worksheets(sheet).Cells(line, ColFirstVal)
NbEmptyCells = ColEndVal - ColFirstVal - 1
step = (Worksheets(sheet).Cells(line, ColEndVal) - FirstVal) / NbEmptyCells
For i = 1 To NbEmptyCells
Worksheets(sheet).Cells(line, ColFirstVal + i) = FirstVal + step * i
Next
FillCellsWithInterpolatedValues = True
End Function
Function InterpolateSheet(ByVal sheetName As String) As Boolean
For ligne = 2 To 101
Dim ok As Boolean
Dim col As Integer
Dim lookedCol As Integer
col = 6
lookedCol = FindNextVal(sheetName, ligne, col, 140)
If lookedCol < 1 Or lookedCol > 140 Then
Exit Function
End If
col = lookedCol
lookedCol = FindNextHole(sheetName, ligne, col, 140)
If lookedCol > 140 Then
Exit Function
End If
col = lookedCol - 1
Do While col < 140
lookedCol = FindNextVal(sheetName, ligne, col + 1, 140)
If lookedCol > 140 Then
Exit Do
End If
ok = FillCellsWithInterpolatedValues(sheetName, ligne, col, lookedCol)
lookedCol = FindNextHole(sheetName, ligne, lookedCol, 140)
If lookedCol > 140 Then
Exit Do
End If
col = lookedCol - 1
Loop
Next ligne
End Function
Sub MyInterpolation()
InterpolateSheet ("Varus Forcé")
InterpolateSheet ("Valgus Forcé")
InterpolateSheet ("Naturel")
InterpolateSheet ("Contact")
InterpolateSheet ("Rotation Interne")
InterpolateSheet ("Rotation Externe")
End Sub |
Partager