|
发表于 2022-2-10 15:31
|
显示全部楼层
本楼为最佳答案
- Option Explicit
- Option Compare Text
- Sub 匹配()
- Dim arr, h As Integer, i As Integer, brr(), crr()
- h = Sheet1.Cells(Rows.Count, "L").End(xlUp).Row
- If h = 1 Then End
- arr = Sheet1.Range("l6:l" & h)
- For i = 1 To UBound(arr)
- ReDim Preserve brr(1 To i)
- ReDim Preserve crr(1 To i)
- brr(i) = VBA.Split(arr(i, 1), "/")(0)
- crr(i) = VBA.Split(arr(i, 1), "/")(1)
- Next i
- Dim drr, j As Integer, f As Integer
- drr = Sheet1.Range("e6:e" & Sheet1.Cells(Rows.Count, "e").End(xlUp).Row)
- For i = 1 To UBound(drr)
- f = 0
- For j = 1 To UBound(arr)
- If drr(i, 1) = brr(j) Or drr(i, 1) = crr(j) Then
- f = 1
- drr(i, 1) = arr(j, 1)
- Exit For
- End If
- Next j
- If f = 0 Then drr(i, 1) = "请检查命名是否规范"
- Next i
- Sheet1.Range("G6:G" & Sheet1.Cells(Rows.Count, "e").End(xlUp).Row) = drr
- End Sub
复制代码 此代码需要注意事项:
1.E列的命名跟L列的命名要规范:E列使用括号跟L列要么全部是英文要么全部是中文
2.L列英文和后面中文描述,需要全部以“/”斜杠分开,不要有些斜杠在中间
3.因为此代码是完全匹配,近似匹配的显示结果为“请检查命名是否规范”,避免出错,药品还是要严谨
4.不需要区分大小写字母,E列或者L列前后不要有空格
|
|