|
- Option Explicit
- Sub Test()
- Dim strFind As String, arr As Variant
-
- ' strFind = "咸鸭边腿"
- strFind = "琵琶腿"
- arr = FindByArr(strFind)
-
- If Not IsArray(arr) Then Exit Sub
-
- Sheet2.Range("M4:P19").ClearContents
- Sheets("测试表1").Range("M4").Resize(UBound(arr), 3) = arr
-
- End Sub
- Function FindByArr(strFind As String) As Variant
- Dim arrList As Variant, arrFind As Variant
- Dim strSplit() As String, lngRow As Long
-
- If GetValArr(arrList) = True Then
- strFind = "|" & Trim(strFind) & "|" '精准查找
- arrList = Filter(arrList, strFind)
- End If
-
- If IsArray(arrList) Then
- ReDim arrFind(1 To UBound(arrList) + 1, 1 To 3)
- For lngRow = LBound(arrList) To UBound(arrList)
- strSplit = Split(arrList(lngRow), "|")
- arrFind(lngRow + 1, 1) = strSplit(2)
- arrFind(lngRow + 1, 2) = strSplit(3)
- arrFind(lngRow + 1, 3) = strSplit(4)
- Next
- End If
- FindByArr = arrFind
- End Function
- '获取原始信息
- Function GetValArr(ArrVal As Variant) As Boolean
- Dim sh As Worksheet, lngRow As Long, arrData As Variant
- Dim intID As Integer, strDate As String
-
- Set sh = Sheets("测试表1")
- lngRow = sh.Range("A" & Rows.Count).End(xlUp).Row
-
- If lngRow < 25 Then
- GetValArr = False
- Exit Function
- End If
-
- arrData = sh.Range("A24:F" & lngRow)
-
- ReDim ArrVal(LBound(arrData) To UBound(arrData))
- intID = 1
-
- For lngRow = LBound(arrData) To UBound(arrData)
- If arrData(lngRow, 2) = "" Then
- strDate = arrData(lngRow, 1)
- Else
- ArrVal(intID) = "|" & arrData(lngRow, 1) & "|" & strDate & "|" & arrData(lngRow, 3) & "|" & arrData(lngRow, 4)
- intID = intID + 1
- End If
- Next
-
- ReDim Preserve ArrVal(1 To intID)
- GetValArr = True
- End Function
复制代码 |
|