如何黑名单在VBA代码 [英] How To Blacklist Words In VBA Code

问题描述

我有以下脚本，将单元格中的任何单词与单元格范围中的任何其他单词相匹配。然而，我希望代码省略一些常见的术语，如和，或大，小。

我对VBA不是很好，所以我是希望有人能够告诉我在哪里添加到代码中：

  Sub FindSimilar（）
 Dim phrase作为范围，短语As Range 
 Dim terms As Range，term As Range 
 Dim matches As String 
 Dim words（）As String 
 
'确保这具有正确的您的工作簿的工作表名称
设置短语= ThisWorkbook.Worksheets（导出）范围（B2：B2000）
设置术语= ThisWorkbook.Worksheets（主题）范围（D100： D3000）
 
对于每个术语在
 matches =
 words（）= Split（term.Value）
 
对于i = 0对于UBound（单词，1）
对于每个短语在短语
如果InStr（1，phrase.Value，words（i））然后
 matches = matches&短语& /
 End If 
下一个短语
 Next i 
 
如果匹配<> vbNullString然后
 term.Offset（0,6）.Value = Left（matches，Len（matches） -  1）
 Else 
 term.Offset（0,6）.Value =否匹配
结束If 
下一项
 End Sub 
  

我可以黑名单中的条款吗？您的积极投入和支持受到高度评价。

解决方案

如果我明白你想要什么，你可以放置一个选择案例选择案例单词（i）
案例=和，或，大，小，无论你想添加什么
Case Else
对于每个短语在短语
如果InStr（1，phrase.Value，words（i））然后
matches = matches&短语& /
结束如果
下一个短语
结束选择
下一个i

然而，如果有大量的术语，这将变得笨重。

如果有大量条款，您可以在工作表上存储BlackListed Terms，然后执行以下操作：

  For i = 0 To UBound（words，1）
 If ThisWorkbook.Worksheets（Topics）。Range（BlackList）。Find（words ），lookat：= xlWhole）是Nothing然后
对于每个短语在短语
如果InStr（1，phrase.Value，words（i））然后
 matches = matches&短语& /
结束如果
下一个短语
结束如果
下一个我
  

假定BlackList是一个命名空间。如果不是仅仅替换为 A1：A100 或任何范围。

I have the following script that matches any word in a cell to any other word in a range of cells. However, I would like the code to omit certain common terms like "and, or, big, small".

I'm not very good with VBA, so I was hoping someone might be able to tell me where to add it into the code:

Sub FindSimilar()
Dim phrases As Range, phrase As Range
Dim terms As Range, term As Range
Dim matches As String
Dim words() As String

'ensure this has the correct sheet names for your workbook
Set phrases = ThisWorkbook.Worksheets("Export").Range("B2:B2000")
Set terms = ThisWorkbook.Worksheets("Topics").Range("D100:D3000")

For Each term In terms
    matches = ""
    words() = Split(term.Value)

    For i = 0 To UBound(words, 1)
        For Each phrase In phrases
            If InStr(1, phrase.Value, words(i)) Then
                matches = matches & phrase & "/"
            End If
        Next phrase
    Next i

    If matches <> vbNullString Then
        term.Offset(0, 6).Value = Left(matches, Len(matches) - 1)
    Else
        term.Offset(0, 6).Value = "No match"
    End If
Next term
End Sub

How can I blacklist terms in the code? Your positive input and support is highly appreciated.

解决方案

If I understand what you want correctly, you can place a Select Case statement inside the For Each Loop

For i = 0 To UBound(words, 1)
    Select Case words(i)
        Case = "and","or","big","small","whatever else you want to add"
        Case Else
            For Each phrase In phrases
                If InStr(1, phrase.Value, words(i)) Then
                    matches = matches & phrase & "/"
                End If
            Next phrase
    End Select
Next i

However, this will get unwieldy if there are a large amount of terms.

If there are a large amount of terms you can store the BlackListed Terms on a worksheet and then do something like this:

For i = 0 To UBound(words, 1)
    If ThisWorkbook.Worksheets("Topics").Range("BlackList").Find(words(i),lookat:=xlWhole) is Nothing Then 
            For Each phrase In phrases
                If InStr(1, phrase.Value, words(i)) Then
                    matches = matches & phrase & "/"
                End If
            Next phrase
    End If
Next i

assumes BlackList is a namedrange. If not just replace with A1:A100 or whatever the range is.

这篇关于如何黑名单在VBA代码的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！