|
是不是这个意思
- Sub Test()
- Dim shData As Worksheet, shDic As Worksheet
- Dim arrData As Variant, lngRow As Long
- Dim strVal As String, strCheckResult As String
- Dim objDic_Basic As Object
- Dim objReg As Object, strPat As String, strCkeck As String
-
- Set shData = Sheets("Sheet1")
- Set shDic = Sheets("数据库")
-
- Set objDic_Basic = InicDic(shDic, 2, 1)
- strCkeck = "【" & Join(objDic_Basic.keys, "】【") & "】" '生成关键字 字符中
- strPat = "(" & Join(objDic_Basic.keys, ")|(") & ")"
-
- Set objReg = CreateObject("VBScript.RegExp")
- With objReg
- .Global = True
- .Pattern = strPat
- End With
-
- lngRow = shData.Range("B" & Rows.Count).End(xlUp).Row
- arrData = shData.Range("B2:B" & lngRow)
-
- For lngRow = LBound(arrData) To UBound(arrData)
- strVal = Trim(arrData(lngRow, 1))
- If strVal <> "" Then
- ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
- '这里你自己灵活处理, 现在是逐项弹出提示
- strCheckResult = CheckHasStr(strVal, objReg, strCkeck)
- If strCheckResult <> "" Then
- MsgBox "第" & lngRow & "项,缺少以下内容:" & strCheckResult
- End If
- ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
- End If
- Next
-
-
- MsgBox "检查完毕"
- End Sub
- '返回不存在的关键字
- Private Function CheckHasStr(strVal As String, objReg As Object, ByVal strCkeck As String) As String
- Dim objTmp As Object, arr As Variant
- Dim objMatchs As Object, objMatch As Object
- Dim strReturn As String
-
- strReturn = strCkeck
- Set objMatchs = objReg.Execute(strVal)
- For Each objMatch In objMatchs
- strKey = Trim(objMatch.Value)
- strReturn = Replace(strReturn, "【" & strKey & "】", "")
- Next
- CheckHasStr = strReturn
- End Function
- '生成关键字 字典列表
- Private Function InicDic(sh As Worksheet, lngColID As Long, lngStartRow As Long) As Object
- Dim arrData As Variant, lngRow As Long
- Dim objDic As Object, strKey As String
-
- Set objDic = CreateObject("Scripting.Dictionary")
-
- arrData = sh.UsedRange
-
- For lngRow = LBound(arrData) To UBound(arrData)
- strKey = Trim(arrData(lngRow, lngColID))
- If strKey <> "" Then objDic(strKey) = strKey
- Next
-
- Set InicDic = objDic
- End Function
复制代码 |
评分
-
1
查看全部评分
-
|