Function GenitivePostCase(sEpis1$, Optional sEpis2$, Optional sEpis3$, Optional sEpis4$, _
Optional sEpis5$, Optional sEpis6$) As String
Application.Volatile True ' автопересчёт формулы на листе
sEpis1$ = Replace(sEpis1$, " - ", "-"): sEpis1$ = Replace(Replace(sEpis1$, " -", "-"), "- ", "-")
sEpis2$ = Replace(sEpis2$, " - ", "-"): sEpis2$ = Replace(Replace(sEpis2$, " -", "-"), "- ", "-")
On Error Resume Next
If (sEpis2$ = "" And sEpis3$ = "" And sEpis4$ = "") Then
Arr = Split(Application.Trim(sEpis1$))
sEpis1$ = Arr(0): sEpis2$ = Arr(1): sEpis3$ = Replace(Arr(2), ".", ""): sEpis4$ = Arr(3): _
sEpis5$ = Arr(4): sEpis6$ = Arr(5): sEpis7$ = Arr(6): sEpis8$ = Arr(7): sEpis9$ = Arr(8): _
sEpis10$ = Arr(9): sEpis11$ = Arr(10): sEpis12$ = Arr(11): sEpis13$ = Arr(12): sEpis14$ = Arr(13): _
sEpis15$ = Arr(14): sEpis16$ = Arr(15): sEpis17$ = Arr(16): sEpis18$ = Arr(17): sEpis19$ = Arr(18)
End If
If Len(sEpis1) > 0 Then ' должность
arrEpis1 = Split(sEpis1, "-")
For i = LBound(arrEpis1) To UBound(arrEpis1) ' перебираем все части должностей, содержащих дефис
sRes = "": sEpis1Part = arrEpis1(i)
Select Case Right(sEpis1Part, 1)
Case "р", "к", "н", "т", "ч", "г", "д": sRes = Mid(sEpis1Part, 1, Len(sEpis1Part)) & "а"
Case "ь": sRes = Mid(sEpis1Part, 1, Len(sEpis1Part) - 1) & "я"
Case "я": sRes = Mid(sEpis1Part, 1, Len(sEpis1Part) - 1) & "ю"
If UBound(arrEpis1) > 0 And i = 0 Then sRes = sEpis1Part
Case Else: sRes = sEpis1Part & ""
End Select
Select Case Right(sEpis1Part, 2) ' добавлено, для редких должностей
Case "ая": sRes = Mid(sEpis1Part, 1, Len(sEpis1Part) - 2) & "ой"
Case "ый": sRes = Mid(sEpis1Part, 1, Len(sEpis1Part) - 2) & "ого"
Case "ий": sRes = Mid(sEpis1Part, 1, Len(sEpis1Part) - 2) & "его"
Case "ок": sRes = Mid(sEpis1Part, 1, Len(sEpis1Part) - 2) & "ка"
End Select
arrEpis1(i) = sRes
Next
GenitivePostCase = Join(arrEpis1, "-") & " " ' соединяем части склоняемой должности обратно в одну строку
End If
If Len(sEpis2) > 0 Then ' должность
arrEpis2 = Split(sEpis2, "-")
For i = LBound(arrEpis2) To UBound(arrEpis2) ' перебираем все части должностей, содержащих дефис
sRes = "": sEpis2Part = arrEpis2(i)
Select Case Right(sEpis2Part, 1)
Case "р", "к", "н", "т", "ч", "г", "д": sRes = Mid(sEpis2Part, 1, Len(sEpis2Part)) & "а"
Case "ь": sRes = Mid(sEpis2Part, 1, Len(sEpis2Part) - 1) & "я"
Case "я": sRes = Mid(sEpis2Part, 1, Len(sEpis2Part) - 1) & "ю"
If UBound(arrEpis2) > 0 And i = 0 Then sRes = sEpis2Part
Case Else: sRes = sEpis2Part & ""
End Select
Select Case Right(sEpis2Part, 2) ' добавлено, для редких должностей
Case "ая": sRes = Mid(sEpis2Part, 1, Len(sEpis2Part) - 2) & "ой"
Case "ый": sRes = Mid(sEpis2Part, 1, Len(sEpis2Part) - 2) & "ого"
Case "ий": sRes = Mid(sEpis2Part, 1, Len(sEpis2Part) - 2) & "его"
Case "ок": sRes = Mid(sEpis2Part, 1, Len(sEpis1Part) - 2) & "ка"
End Select
arrEpis2(i) = sRes
Next
GenitivePostCase = Join(arrEpis1, "-") & " " & Join(arrEpis2, "-") & " " ' соединяем части склоняемой должности обратно в одну строку
End If
If Len(sEpis3) > 0 Then ' должность
arrEpis3 = Split(sEpis3, "-")
For i = LBound(arrEpis3) To UBound(arrEpis3) ' перебираем все части должностей, содержащих дефис
sRes = "": sEpis3Part = arrEpis3(i)
Select Case Right(sEpis3Part, 1)
Case "р", "к", "н", "т", "ч", "г", "д": sRes = Mid(sEpis3Part, 1, Len(sEpis3Part)) & "а"
Case "ь": sRes = Mid(sEpis3Part, 1, Len(sEpis3Part) - 1) & "я"
Case "я": sRes = Mid(sEpis3Part, 1, Len(sEpis3Part) - 1) & "ю"
If UBound(arrEpis3) > 0 And i = 0 Then sRes = sEpis3Part
Case Else: sRes = sEpis3Part & ""
End Select
Select Case Right(sEpis3Part, 2) ' добавлено, для редких должностей
Case "ая": sRes = Mid(sEpis3Part, 1, Len(sEpis3Part) - 2) & "ой"
Case "ый": sRes = Mid(sEpis3Part, 1, Len(sEpis3Part) - 2) & "ого"
Case "ий": sRes = Mid(sEpis3Part, 1, Len(sEpis3Part) - 2) & "его"
Case "ок": sRes = Mid(sEpis3Part, 1, Len(sEpis3Part) - 2) & "ка"
End Select
arrEpis3(i) = sRes
Next
GenitivePostCase = Join(arrEpis1, "-") & " " & Join(arrEpis2, "-") & " " & Join(arrEpis3, "-") & " " ' соединяем части склоняемой должности обратно в одну строку
End If
If Len(sEpis4) > 0 Then ' должность
arrEpis4 = Split(sEpis4, "-")
For i = LBound(arrEpis4) To UBound(arrEpis4) ' перебираем все части должностей, содержащих дефис
sRes = "": sEpis4Part = arrEpis4(i)
Select Case Right(sEpis4Part, 1)
Case "р", "к", "н", "т", "ч", "г", "д": sRes = Mid(sEpis4Part, 1, Len(sEpis4Part)) & "а"
Case "ь": sRes = Mid(sEpis4Part, 1, Len(sEpis4Part) - 1) & "я"
Case "я": sRes = Mid(sEpis4Part, 1, Len(sEpis4Part) - 1) & "ю"
If UBound(arrEpis4) > 0 And i = 0 Then sRes = sEpis4Part
Case Else: sRes = sEpis4Part & ""
End Select
Select Case Right(sEpis4Part, 2) ' добавлено, для редких должностей
Case "ая": sRes = Mid(sEpis4Part, 1, Len(sEpis4Part) - 2) & "ой"
Case "ый": sRes = Mid(sEpis4Part, 1, Len(sEpis4Part) - 2) & "ого"
Case "ий": sRes = Mid(sEpis4Part, 1, Len(sEpis4Part) - 2) & "его"
Case "ок": sRes = Mid(sEpis4Part, 1, Len(sEpis4Part) - 2) & "ка"
End Select
arrEpis4(i) = sRes
Next
GenitivePostCase = Join(arrEpis1, "-") & " " & Join(arrEpis2, "-") & " " _
& Join(arrEpis3, "-") & " " & Join(arrEpis4, "-") & " " ' соединяем части склоняемой должности обратно в одну строку
End If
If Len(sEpis5) > 0 Then ' должность
arrEpis5 = Split(sEpis5, "-")
For i = LBound(arrEpis5) To UBound(arrEpis5) ' перебираем все части должностей, содержащих дефис
sRes = "": sEpis5Part = arrEpis5(i)
Select Case Right(sEpis5Part, 1)
Case "р", "к", "н", "т", "ч", "г", "д": sRes = Mid(sEpis5Part, 1, Len(sEpis5Part)) & "а"
Case "ь": sRes = Mid(sEpis5Part, 1, Len(sEpis5Part) - 1) & "я"
Case "я": sRes = Mid(sEpis5Part, 1, Len(sEpis5Part) - 1) & "ю"
If UBound(arrEpis5) > 0 And i = 0 Then sRes = sEpis5Part
Case Else: sRes = sEpis5Part & ""
End Select
Select Case Right(sEpis5Part, 2) ' добавлено, для редких должностей
Case "ая": sRes = Mid(sEpis5Part, 1, Len(sEpis5Part) - 2) & "ой"
Case "ый": sRes = Mid(sEpis5Part, 1, Len(sEpis5Part) - 2) & "ого"
Case "ий": sRes = Mid(sEpis5Part, 1, Len(sEpis5Part) - 2) & "его"
Case "ок": sRes = Mid(sEpis5Part, 1, Len(sEpis5Part) - 2) & "ка"
End Select
arrEpis5(i) = sRes
Next
GenitivePostCase = Join(arrEpis1, "-") & " " & Join(arrEpis2, "-") & " " _
& Join(arrEpis3, "-") & " " & Join(arrEpis4, "-") & " " & Join(arrEpis5, "-") & " " ' соединяем части склоняемой должности обратно в одну строку
End If
If Len(sEpis6) > 0 Then ' должность
arrEpis6 = Split(sEpis6, "-")
For i = LBound(arrEpis6) To UBound(arrEpis6) ' перебираем все части должностей, содержащих дефис
sRes = "": sEpis6Part = arrEpis6(i)
Select Case Right(sEpis6Part, 1)
Case "р", "к", "н", "т", "ч", "г", "д": sRes = Mid(sEpis6Part, 1, Len(sEpis6Part)) & "а"
Case "ь": sRes = Mid(sEpis6Part, 1, Len(sEpis6Part) - 1) & "я"
Case "я": sRes = Mid(sEpis6Part, 1, Len(sEpis6Part) - 1) & "ю"
If UBound(arrEpis6) > 0 And i = 0 Then sRes = sEpis6Part
Case Else: sRes = sEpis6Part & ""
End Select
Select Case Right(sEpis6Part, 2) ' добавлено, для редких должностей
Case "ая": sRes = Mid(sEpis6Part, 1, Len(sEpis6Part) - 2) & "ой"
Case "ый": sRes = Mid(sEpis6Part, 1, Len(sEpis6Part) - 2) & "ого"
Case "ий": sRes = Mid(sEpis6Part, 1, Len(sEpis6Part) - 2) & "его"
Case "ок": sRes = Mid(sEpis6Part, 1, Len(sEpis6Part) - 2) & "ка"
End Select
arrEpis6(i) = sRes
Next
GenitivePostCase = Join(arrEpis1, "-") & " " & Join(arrEpis2, "-") & " " _
& Join(arrEpis3, "-") & " " & Join(arrEpis4, "-") & " " & Join(arrEpis5, "-") & " " & Join(arrEpis6, "-") & " " ' соединяем части склоняемой должности обратно в одну строку
End If
If Len(sEpis7) > 0 Then ' должность
arrEpis7 = Split(sEpis7, "-")
For i = LBound(arrEpis7) To UBound(arrEpis7) ' перебираем все части должностей, содержащих дефис
sRes = "": sEpis7Part = arrEpis7(i)
Select Case Right(sEpis7Part, 1)
Case "р", "к", "н", "т", "ч", "г", "д": sRes = Mid(sEpis7Part, 1, Len(sEpis7Part)) & "а"
Case "ь": sRes = Mid(sEpis7Part, 1, Len(sEpis7Part) - 1) & "я"
Case "я": sRes = Mid(sEpis7Part, 1, Len(sEpis7Part) - 1) & "ю"
If UBound(arrEpis7) > 0 And i = 0 Then sRes = sEpis7Part
Case Else: sRes = sEpis7Part & ""
End Select
Select Case Right(sEpis7Part, 2) ' добавлено, для редких должностей
Case "ая": sRes = Mid(sEpis7Part, 1, Len(sEpis7Part) - 2) & "ой"
Case "ый": sRes = Mid(sEpis7Part, 1, Len(sEpis7Part) - 2) & "ого"
Case "ий": sRes = Mid(sEpis7Part, 1, Len(sEpis7Part) - 2) & "его"
Case "ок": sRes = Mid(sEpis7Part, 1, Len(sEpis7Part) - 2) & "ка"
End Select
arrEpis7(i) = sRes
Next
GenitivePostCase = Join(arrEpis1, "-") & " " & Join(arrEpis2, "-") & " " _
& Join(arrEpis3, "-") & " " & Join(arrEpis4, "-") & " " & Join(arrEpis5, "-") & " " _
& Join(arrEpis6, "-") & " " & Join(arrEpis7, "-") & " " ' соединяем части склоняемой должности обратно в одну строку
End If
If Len(sEpis8) > 0 Then ' должность
arrEpis8 = Split(sEpis8, "-")
For i = LBound(arrEpis8) To UBound(arrEpis8) ' перебираем все части должностей, содержащих дефис
sRes = "": sEpis8Part = arrEpis8(i)
Select Case Right(sEpis8Part, 1)
Case "р", "к", "н", "т", "ч", "г", "д": sRes = Mid(sEpis8Part, 1, Len(sEpis8Part)) & "а"
Case "ь": sRes = Mid(sEpis8Part, 1, Len(sEpis8Part) - 1) & "я"
Case "я": sRes = Mid(sEpis8Part, 1, Len(sEpis8Part) - 1) & "ю"
If UBound(arrEpis8) > 0 And i = 0 Then sRes = sEpis8Part
Case Else: sRes = sEpis8Part & ""
End Select
Select Case Right(sEpis8Part, 2) ' добавлено, для редких должностей
Case "ая": sRes = Mid(sEpis8Part, 1, Len(sEpis8Part) - 2) & "ой"
Case "ый": sRes = Mid(sEpis8Part, 1, Len(sEpis8Part) - 2) & "ого"
Case "ий": sRes = Mid(sEpis8Part, 1, Len(sEpis8Part) - 2) & "его"
Case "ок": sRes = Mid(sEpis8Part, 1, Len(sEpis8Part) - 2) & "ка"
End Select
arrEpis8(i) = sRes
Next
GenitivePostCase = Join(arrEpis1, "-") & " " & Join(arrEpis2, "-") & " " _
& Join(arrEpis3, "-") & " " & Join(arrEpis4, "-") & " " & Join(arrEpis5, "-") & " " _
& Join(arrEpis6, "-") & " " & Join(arrEpis7, "-") & " " & Join(arrEpis8, "-") & " " ' соединяем части склоняемой должности обратно в одну строку
End If
If Len(sEpis9) > 0 Then ' должность
arrEpis9 = Split(sEpis9, "-")
For i = LBound(arrEpis9) To UBound(arrEpis9) ' перебираем все части должностей, содержащих дефис
sRes = "": sEpis9Part = arrEpis9(i)
Select Case Right(sEpis9Part, 1)
Case "р", "к", "н", "т", "ч", "г", "д": sRes = Mid(sEpis9Part, 1, Len(sEpis9Part)) & "а"
Case "ь": sRes = Mid(sEpis9Part, 1, Len(sEpis9Part) - 1) & "я"
Case "я": sRes = Mid(sEpis9Part, 1, Len(sEpis9Part) - 1) & "ю"
If UBound(arrEpis9) > 0 And i = 0 Then sRes = sEpis9Part
Case Else: sRes = sEpis9Part & ""
End Select
Select Case Right(sEpis9Part, 2) ' добавлено, для редких должностей
Case "ая": sRes = Mid(sEpis9Part, 1, Len(sEpis9Part) - 2) & "ой"
Case "ый": sRes = Mid(sEpis9Part, 1, Len(sEpis9Part) - 2) & "ого"
Case "ий": sRes = Mid(sEpis9Part, 1, Len(sEpis9Part) - 2) & "его"
Case "ок": sRes = Mid(sEpis9Part, 1, Len(sEpis9Part) - 2) & "ка"
End Select
arrEpis9(i) = sRes
Next
GenitivePostCase = Join(arrEpis1, "-") & " " & Join(arrEpis2, "-") & " " _
& Join(arrEpis3, "-") & " " & Join(arrEpis4, "-") & " " & Join(arrEpis5, "-") & " " _
& Join(arrEpis6, "-") & " " & Join(arrEpis7, "-") & " " _
& Join(arrEpis8, "-") & " " & Join(arrEpis9, "-") & " " ' соединяем части склоняемой должности обратно в одну строку
End If
If Len(sEpis10) > 0 Then ' должность
arrEpis10 = Split(sEpis10, "-")
For i = LBound(arrEpis10) To UBound(arrEpis10) ' перебираем все части должностей, содержащих дефис
sRes = "": sEpis10Part = arrEpis10(i)
Select Case Right(sEpis10Part, 1)
Case "р", "к", "н", "т", "ч", "г", "д": sRes = Mid(sEpis10Part, 1, Len(sEpis10Part)) & "а"
Case "ь": sRes = Mid(sEpis10Part, 1, Len(sEpis10Part) - 1) & "я"
Case "я": sRes = Mid(sEpis10Part, 1, Len(sEpis10Part) - 1) & "ю"
If UBound(arrEpis10) > 0 And i = 0 Then sRes = sEpis10Part
Case Else: sRes = sEpis10Part & ""
End Select
Select Case Right(sEpis10Part, 2) ' добавлено, для редких должностей
Case "ая": sRes = Mid(sEpis10Part, 1, Len(sEpis9Part) - 2) & "ой"
Case "ый": sRes = Mid(sEpis10Part, 1, Len(sEpis10Part) - 2) & "ого"
Case "ий": sRes = Mid(sEpis10Part, 1, Len(sEpis10Part) - 2) & "его"
Case "ок": sRes = Mid(sEpis10Part, 1, Len(sEpis10Part) - 2) & "ка"
End Select
arrEpis10(i) = sRes
Next
GenitivePostCase = Join(arrEpis1, "-") & " " & Join(arrEpis2, "-") & " " _
& Join(arrEpis3, "-") & " " & Join(arrEpis4, "-") & " " & Join(arrEpis5, "-") & " " _
& Join(arrEpis6, "-") & " " & Join(arrEpis7, "-") & " " _
& Join(arrEpis8, "-") & " " & Join(arrEpis9, "-") & " " & Join(arrEpis10, "-") & " " ' соединяем части склоняемой должности обратно в одну строку
End If
If Len(sEpis11) > 0 Then
' Select Case Right(sEpis1Part, 1)
GenitivePostCase = GenitivePostCase & sEpis11 & " "
End If
If Len(sEpis12) > 0 Then
' Select Case Right(sEpis1Part, 1)
GenitivePostCase = GenitivePostCase & sEpis12 & " "
End If
If Len(sEpis13) > 0 Then
' Select Case Right(sEpis1Part, 1)
GenitivePostCase = GenitivePostCase & sEpis13 & " "
End If
If Len(sEpis14) > 0 Then
' Select Case Right(sEpis1Part, 1)
GenitivePostCase = GenitivePostCase & sEpis14 & " "
End If
If Len(sEpis15) > 0 Then
' Select Case Right(sEpis1Part, 1)
GenitivePostCase = GenitivePostCase & sEpis15 & " "
End If
If Len(sEpis16) > 0 Then
' Select Case Right(sEpis1Part, 1)
GenitivePostCase = GenitivePostCase & sEpis16 & " "
End If
If Len(sEpis17) > 0 Then
' Select Case Right(sEpis1Part, 1)
GenitivePostCase = GenitivePostCase & sEpis17 & " "
End If
If Len(sEpis18) > 0 Then
' Select Case Right(sEpis1Part, 1)
GenitivePostCase = GenitivePostCase & sEpis18 & " "
End If
If Len(sEpis19) > 0 Then
' Select Case Right(sEpis1Part, 1)
GenitivePostCase = GenitivePostCase & sEpis19 & " "
End If
GenitivePostCase = Replace(GenitivePostCase, "-", "- ")
GenitivePostCase = Replace(GenitivePostCase, "- ", "-")
End Function
|