同时搜索两个词

比如我可以搜索两个词。LED LIGHT

我想立即搜索 “LED LIGHT “或 “LIGHT LED”,无论 “LED “或 “LIGHT “在文本中的位置。

用通配符 “*”代替单词之间的 “空格 “确实有助于搜索 “LED LIGHT”,无论单词在文本中的位置,但不会反向搜索 “LIGHT LED”。

Dim ws As Worksheet
Dim firstWord As String
Dim secondWord As String
Dim thirdWord As String
Dim LastRow1 As Long
Dim LastRow2 As Long
Dim LastRow3 As Long

On Error GoTo Whoa

Set ws = Sheet1

firstWord = InputBox("Enter word for bullet_points", "Keyword BOX")
secondWord = InputBox("Enter word for item_name", "Keyword BOX")
thirdWord = InputBox("Enter word for product_description", "Keyword BOX")
LastRow1 = Cells(Rows.Count, 8).End(xlUp).Row + 1

If firstWord = "" Then
    ActiveSheet.Cells(LastRow1, 8).Value = "No INPUT"
Else
    ActiveSheet.Cells(LastRow1, 8).Value = firstWord
End If

LastRow2 = Cells(Rows.Count, 9).End(xlUp).Row + 1
If secondWord = "" Then
    ActiveSheet.Cells(LastRow2, 9).Value = "No INPUT"
Else
    ActiveSheet.Cells(LastRow2, 9).Value = secondWord
End If

LastRow3 = Cells(Rows.Count, 10).End(xlUp).Row + 1
If thirdWord = "" Then
    ActiveSheet.Cells(LastRow3, 10).Value = "No INPUT"
Else
    ActiveSheet.Cells(LastRow3, 10).Value = thirdWord
End If

With ws
    If firstWord <> "" Then ReplaceText ws.Range("B17:B4001"), firstWord
    If secondWord <> "" Then ReplaceText ws.Range("C17:C4001"), secondWord
    If thirdWord <> "" Then ReplaceText ws.Range("D17:D4001"), thirdWord
End With

Exit Sub

Whoa:
    msgbox Err.Description
End Sub

Private Sub ReplaceText(rng As Range, txt As String)
Dim aCell As Range
Dim bCell As Range
Dim rngFound As Range

Set aCell = rng.Find(What:=txt, LookIn:=xlValues, _
                LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                MatchCase:=False, SearchFormat:=False)

If Not aCell Is Nothing Then
    Set bCell = aCell
    Set rngFound = aCell

    Do
        Set aCell = rng.FindNext(After:=aCell)
        If Not aCell Is Nothing Then
            If aCell.Address = bCell.Address Then Exit Do
            Set rngFound = Union(rngFound, aCell)
        Else
            Exit Do
        End If
    Loop
End If

If Not rngFound Is Nothing Then
    rngFound.Value = "XXXXXXXXXXXXX"
End If

解决方案:

恐怕你的意图只是部分清楚。因此,我下面的解决方案由两个不相干的部分组成。在第一部分中,用户输入3个搜索词,然后在下面的记录中做一个记录 Sheet1 输入的内容。在第二部分中,函数的名称不恰当地称为 ReplaceText,寻找一个包含用户输入的所有单词的单元格。请注意,每个单元格中都会有””。因此,如果用户输入了空格,它们不会对搜索产生影响。有影响的是,如果查找 “LED灯”,就会找到 “LED照明”。请记住这一点。

Sub Test_Replace()
    ' 010

    Dim searchWord(1 To 3) As String
    Dim Clm As Long
    Dim C As Long
    Dim i As Integer

    searchWord(1) = InputBox("Enter word for bullet_points", "Keyword BOX")
    searchWord(2) = InputBox("Enter word for item_name", "Keyword BOX")
    searchWord(3) = InputBox("Enter word for product_description", "Keyword BOX")

    Clm = 2                     ' first column to replace
    With Sheet1
        For C = 8 To 10
            i = i + 1
            If Len(searchWord(i)) = 0 Then searchWord(i) = "No INPUT"
            .Cells(.Rows.Count, C).End(xlUp).Offset(1).Value = searchWord(i)
            Clm = Clm + 1
        Next C

'        If firstWord <> "" Then ReplaceText Ws.Range("B17:B4001"), firstWord
'        If secondWord <> "" Then ReplaceText Ws.Range("C17:C4001"), secondWord
'        If thirdWord <> "" Then ReplaceText Ws.Range("D17:D4001"), thirdWord
    End With

End Sub

Private Function ReplaceText(Rng As Range, _
                             searchWord() As String) As boolean

    Dim Fnd As Range
    Dim FndVal As String
    Dim i As Integer

    Set Fnd = Rng.Find(What:=searchWord(3), LookIn:=xlValues, _
                       LookAt:=xlPart, SearchOrder:=xlByRows, _
                       SearchDirection:=xlNext, _
                       MatchCase:=False, SearchFormat:=False)
    If Not Fnd Is Nothing Then
        Do Until Fnd Is Nothing
            FndVal = Fnd.Value
            ' compare case-insensitive
            For i = 2 To 1 Step -1
                If InStr(1, FndVal, searchWord(i), vbTextCompare) = 0 Then Exit For
            Next i
            If i = 0 Then
                Set Rng = Fnd
                ReplaceText = True
                Exit Do
            End If
            Set Fnd = Rng.FindNext(Fnd)
        Loop
    End If
End Function

在第一个过程中,我的代码和你的代码的区别在于将ActiveSheet替换为 Sheet1. 请注意,变量 Clm 被设置为传递范围 “B17:B4001″,C和D,也许在循环中,但我没能把这个逻辑连接起来。

该函数首先寻找第3个单词。如果这是一个空白,搜索可能会花很长时间,因为搜索范围内的每个单元格都符合条件。如果searchWord(3)被找到,代码将寻找(2)和(1),如果三个都被找到,则返回单元格作为结果。否则,函数将查找下一个预限定的单元格。你可以改进限定过程,以确保 乐趣 不会误以为 灯光.

该函数根据是否找到了匹配,返回 True 或 False。如果答案是 True,那么作为参数传递给它的 Rng 变量将包含找到匹配的地址。下面是我在测试中使用的函数调用。

Private Sub TestFind()
    Dim Rng As Range
    Dim Sw() As String

    Sw = Split(" One Two Three")
    Set Rng = Range("A2:A25")
    Debug.Print ReplaceText(Rng, Sw), Rng.Address
End Sub

如果函数返回False,Rng.Address将是 “A2:A25”

给TA打赏
共{{data.count}}人
人已打赏
未分类

如何在Python中有效地将一个字典中的字符串列表与另一个字典列表进行比较?

2022-9-8 22:44:25

未分类

拒绝连接到127.0.0.1:5432。检查主机名和端口是否正确,邮管员是否接受TCPIP连接。

2022-9-8 22:44:27

0 条回复 A文章作者 M管理员
    暂无讨论,说说你的看法吧
个人中心
购物车
优惠劵
今日签到
有新私信 私信列表
搜索