|
- Option Explicit
- Sub Test()
- Dim SH As Worksheet
- Dim arr As Variant, lngRow As Long
- Dim strID As String, strName As String
-
- Set SH = Sheets("Sheet1")
- lngRow = SH.Range("H" & Rows.Count).End(xlUp).Row
- arr = SH.Range("H3:H" & lngRow)
- arr = Application.WorksheetFunction.Transpose(arr)
- strID = Trim(SH.Range("H1").Value)
- strName = Mid(Trim(SH.Range("E" & lngRow + 1).Value), 1, 2)
-
- If strID <> "" And strName <> "" Then
- SH.Range("H" & lngRow + 1).Value = GetPYByChar(strName) & strID & GetMaxIDBystr(strID, arr)
- End If
-
- End Sub
- Function GetMaxIDBystr(strSplit As String, arr As Variant) As Long
- Dim arrTemp As Variant, lngID As Long, lngMax As Long
- Dim arrResult As Variant
-
- arrTemp = Filter(arr, strSplit)
-
- If UBound(arrTemp) = -1 Then
- lngMax = 0
- Else
- ReDim arrResult(LBound(arrTemp) To UBound(arrTemp)) As Long
- For lngID = LBound(arrTemp) To UBound(arrTemp)
- arrResult(lngID) = Split(arrTemp(lngID), strSplit)(1)
- Next
- lngMax = Application.WorksheetFunction.Max(arrResult)
- End If
-
- GetMaxIDBystr = lngMax + 1
- End Function
- Function GetPYByChar(strChar As String) As String
- Dim lngID As Long, lngChar As Long
- Dim strTemp As String, strResult As String
-
- For lngID = 1 To Len(strChar)
- strTemp = Mid(strChar, lngID, 1)
- lngChar = 65536 + Asc(strTemp)
- Select Case lngChar
- Case 45217 To 45252: strTemp = "A"
- Case 45253 To 45760: strTemp = "B"
- Case 45761 To 46317: strTemp = "C"
- Case 46318 To 46825: strTemp = "D"
- Case 46826 To 47009: strTemp = "E"
- Case 47010 To 47296: strTemp = "F"
- Case 47297 To 47613: strTemp = "G"
- Case 47614 To 48118: strTemp = "H"
- Case 48119 To 49061: strTemp = "J"
- Case 49062 To 49323: strTemp = "K"
- Case 49324 To 49895: strTemp = "L"
- Case 49896 To 50370: strTemp = "M"
- Case 50371 To 50613: strTemp = "N"
- Case 50614 To 50621: strTemp = "O"
- Case 50622 To 50905: strTemp = "P"
- Case 50906 To 51386: strTemp = "Q"
- Case 51387 To 51445: strTemp = "R"
- Case 51446 To 52217: strTemp = "S"
- Case 52218 To 52697: strTemp = "T"
- Case 52698 To 52979: strTemp = "W"
- Case 52980 To 53640: strTemp = "X"
- Case 53689 To 54480: strTemp = "Y"
- Case 54481 To 62289: strTemp = "Z"
- End Select
- strResult = strResult & strTemp
- Next
- GetPYByChar = strResult & "ID"
- End Function
复制代码 |
|