excel

' Převod českých znaků dle klíče:
'   é -> 0, ž -> 6, ý -> 7, + -> 1, ř -> 5, á -> 8, í -> 9, č -> 4, ě -> 2, š -> 3
Public Function ConvertCzechChars(sInput As String) As String
    Dim i As Long, sRes As String, ch As String
    sRes = ""
    For i = 1 To Len(sInput)
        ch = Mid(sInput, i, 1)
        Select Case ch
            Case "é": sRes = sRes & "0"
            Case "ž": sRes = sRes & "6"
            Case "ý": sRes = sRes & "7"
            Case "+": sRes = sRes & "1"
            Case "ř": sRes = sRes & "5"
            Case "á": sRes = sRes & "8"
            Case "í": sRes = sRes & "9"
            Case "č": sRes = sRes & "4"
            Case "ě": sRes = sRes & "2"
            Case "š": sRes = sRes & "3"
            Case Else: sRes = sRes & ch
        End Select
    Next i
    ConvertCzechChars = sRes
End Function

' Funkce pro rozpoznání názvu dílu – hledá v sloupci G a vrací odpovídající hodnotu ze sloupce F.
Public Function RozpoznejDMC(kod As String) As String
    Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("List1")
    Dim hledany As String, rng As Range, cell As Range
    
    Select Case Len(kod)
        Case 26
            If Left(kod, 1) = "P" Then
                hledany = Left(kod, 16)
                Set rng = ws.Range("G47:G54")
            Else
                hledany = Left(kod, 10)
                Set rng = ws.Range("G1:G40")
            End If
        Case 31
            hledany = Left(kod, 11)
            Set rng = ws.Range("G41:G46")
        Case Else
            RozpoznejDMC = ""
            Exit Function
    End Select
    
    For Each cell In rng
        If cell.Value = hledany Then
            RozpoznejDMC = cell.Offset(0, -1).Value ' Název ve sloupci F
            Exit Function
        End If
    Next cell
    RozpoznejDMC = ""
End Function

' Funkce pro získání čísla materiálu – hledá v rozsahu podle délky kódu a vrací hodnotu ze sloupce H.
Public Function ExtractKod(kod As String) As String
    Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("List1")
    Dim hledany As String, rng As Range, cell As Range
    
    Select Case Len(kod)
        Case 26
            If Left(kod, 1) = "P" Then
                hledany = Left(kod, 16)
                Set rng = ws.Range("G47:G54")
            Else
                hledany = Left(kod, 10)
                Set rng = ws.Range("G1:G40")
            End If
        Case 31
            hledany = Left(kod, 11)
            Set rng = ws.Range("G41:G46")
        Case Else
            ExtractKod = ""
            Exit Function
    End Select
    
    For Each cell In rng
        If cell.Value = hledany Then
            ExtractKod = cell.Offset(0, 1).Value ' Číslo materiálu ze sloupce H
            Exit Function
        End If
    Next cell
    ExtractKod = ""
End Function

' Funkce pro extrakci data – vrací datum podle kódu.
Public Function ExtractDatum(kod As String) As Date
    Dim rok As Integer, den As Integer
    Select Case Len(kod)
        Case 26
            If Left(kod, 1) = "P" Then
                rok = CInt(Mid(kod, 20, 2)) + 2000
                den = CInt(Mid(kod, 17, 3))
            Else
                rok = CInt(Right(kod, 2)) + 2000
                den = CInt(Mid(kod, 22, 3))
            End If
        Case 31
            rok = CInt(Mid(kod, 12, 2)) + 2000
            den = CInt(Mid(kod, 15, 3))
        Case Else
            ExtractDatum = 0
            Exit Function
    End Select
    ExtractDatum = DateSerial(rok, 1, den)
End Function

Public Function ExtractPoradi(kod As String) As String
    Select Case Len(kod)
        Case 26
            If Left(kod, 1) = "P" Then
                ExtractPoradi = Right(kod, 4) ' Stellantis: poslední 4
            Else
                ExtractPoradi = Mid(kod, 18, 4) ' Ostatní 26místné: 18-21
            End If
        Case 31
            ExtractPoradi = Mid(kod, 20, 4) ' BMW: 20-23
        Case Else
            ExtractPoradi = ""
    End Select
End Function