如何在Excel VBA中使用LIKE运算符突出显示子字符串 [英] How to highlight substring using LIKE operator in Excel VBA

问题描述

我的字符串看起来像这样:

I have strings that look like this:

DTTGGRKDVVNHCGKKYKDK
RKDVVNHCGKKYKDKSKRAR

我要做的是用粗体和红色字体突出显示该区域. 结果:

What I want to do is to highlight the region with bold and red font. Resulting this:

我在Excel VBA中使用LIKE运算符尝试了以下代码，但它中断了 在此行Set MC = .Execute(C.Text)

I tried the following code using LIKE operator in Excel VBA but it breaks at this line Set MC = .Execute(C.Text)

Option Explicit
Sub boldSubString()
    Dim R As Range, C As Range
    Dim MC As Object    

    Set R = Range(Cells(2, 1), Cells(Rows.Count, 1).End(xlUp))

    For Each C In R
        C.Font.Bold = False
        If C.Text Like "KK*K" Or C.Text Like "KR*R"  Then
            Set MC = .Execute(C.Text)
            C.Characters(MC(0).firstindex + 1, MC(0).Length).Font.Bold = True
        End If
    Next C    

End Sub

什么是正确的方法? 我正在使用Mac Excel版本15.31

What's the right way to do it? I'm using Mac Excel Version 15.31

推荐答案

没有正则表达式，您可以尝试以下操作.我尚未对其进行广泛的测试，但即使在同一字符串中有多个匹配的子字符串，它也似乎可以正常工作.

Without Regular Expressions, you can try the following. I've not tested it extensively but it does seem to work even with multiple matching substrings within the same string.

检查VBA HELP中正在使用的功能，因此，如果需要扩展可能的模式列表，则可以了解其工作原理，以及如何构造与Like运算符配合使用的正确模式.

Examine VBA HELP for the functions that are being used, so you understand how this works, and also how to construct proper patterns to be used with the Like operator, in case you need to expand the list of possible patterns.

Option Explicit
Sub boldSS()
    Dim WS As Worksheet
    Dim R As Range, C As Range
    Dim sPatterns(1) As String
    Dim I As Long, J As Long

sPatterns(0) = "KR?R"
sPatterns(1) = "KK?K"

Set WS = Worksheets("sheet1")
With WS
    Set R = .Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp))
End With

For Each C In R

        'Reset to default
        With C.Font
            .Bold = False
            .Color = vbBlack
        End With

    For I = 0 To UBound(sPatterns)
        If C Like "*" & sPatterns(I) & "*" Then
            For J = 1 To Len(C) - Len(sPatterns(I)) + 1
                If Mid(C, J, Len(sPatterns(I))) Like sPatterns(I) Then
                    With C.Characters(J, Len(sPatterns(I))).Font
                        .Bold = True
                        .Color = vbRed
                    End With
                    If J < Len(C) - 3 Then
                        J = J + 3
                    Else
                        Exit For
                    End If
                End If
            Next J
        End If
    Next I
Next C
End Sub

使用等效的正则表达式模式代替Like运算符，可以如下重写上述内容.请注意，您的Regex模式也将匹配KKAR和KRAK(与下面的宏相同，但与上面的宏相同).

Using your regex pattern equivalent instead for the Like operator, you can rewrite the above as below. Note that your Regex pattern will also match KKAR, and KRAK (as does the macro below, but not the one above).

Option Explicit
Sub boldSS()
    Dim WS As Worksheet
    Dim R As Range, C As Range
    Dim sPattern As String
    Dim I As Long

sPattern = "K[KR]?[KR]"

Set WS = Worksheets("sheet1")
With WS
    Set R = .Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp))
End With

For Each C In R
        With C.Font
            .Bold = False
            .Color = vbBlack
        End With
        If C Like "*" & sPattern & "*" Then
            For I = 1 To Len(C) - 4 + 1
                If Mid(C, I, 4) Like sPattern Then
                    With C.Characters(I, 4).Font
                        .Bold = True
                        .Color = vbRed
                    End With
                    If I < Len(C) - 3 Then
                        I = I + 3
                    Else
                        Exit For
                    End If
                End If
            Next I
        End If
Next C
End Sub

这篇关于如何在Excel VBA中使用LIKE运算符突出显示子字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！