Option Explicit
Function SumRef(s$)
Dim w As Worksheet, c%, Rg As Range, r&, m%, y%, i&, t$, wM%, wY%
Set w = Worksheets("fabrication")
Set Rg = w.Range(w.Cells(1, 2), w.Cells(1, Columns.Count))
If Rg.Find(s) Is Nothing Then
SumRef = "Ref. not found"
Exit Function
Else
c = Rg.Find(s).Column
End If
r = w.Cells(Rows.Count, c).End(xlUp).Row
If r = 1 Then
SumRef = "No data found"
Exit Function
End If
m = Month(Now): y = Year(Now)
For i = 2 To r
t = w.Cells(i, 1): wM = WhatMonth(t): wY = WhatYear(t)
If wM * wY = 0 Then
SumRef = "Typing error in line " & i
Exit Function
Else
If 12 * WhatYear(t) + WhatMonth(t) <= 12 * y + m + 1 Then SumRef = SumRef + w.Cells(i, c)
End If
Next i
End Function
Function WhatMonth%(s$)
s = Trim(LCase(s))
Select Case Left(s, 1)
Case "j"
If Left(s, 2) = "ja" Then
WhatMonth = 1
ElseIf Left(s, 4) = "juin" Then
WhatMonth = 6
ElseIf Left(s, 4) = "juil" Then
WhatMonth = 7
End If
Case "f"
WhatMonth = 2
Case "m"
If Left(s, 3) = "mar" Then
WhatMonth = 3
ElseIf Left(s, 3) = "mai" Then
WhatMonth = 5
End If
Case "a"
If Left(s, 2) = "av" Then
WhatMonth = 4
ElseIf Left(s, 2) = "ao" Then
WhatMonth = 8
End If
Case "s"
WhatMonth = 9
Case "o"
WhatMonth = 10
Case "n"
WhatMonth = 11
Case "d"
WhatMonth = 12
End Select
End Function
Function WhatYear%(s$)
s = Trim(LCase(s))
On Error Resume Next
If Len(Trim(Right(s, 4))) = 4 Then WhatYear = Right(s, 4)
End Function