如何查找和突出显示ActiveSheet中多个字符串的所有出现？ [英] How to find and highlight all occurrences of multiple strings within the ActiveSheet?

问题描述

我已经找到一个解决方案，但代码太长了。然后，我决定搜索一种将所有想要查找的单词插入到单一查找方法中的方法。我遇到了使用数组这样做的一些想法，并使用这3个代码写我的（ this ，这个和这个），但我是VBA的新用户，所以我的最终代码有一个问题，它突出显示了Array的最后一个字符串。我认为问题是逻辑，但我不知道VBA的基础知识，所以我不知道如何纠正它。

我的实际代码：

  Sub Sample（）
 Dim fnd As String 
 Dim MyAr 
 Dim i As Long 
 Dim作为范围，FoundCell As Range，LastCell As Range，myRange As Range 
 
设置myRange = ActiveSheet.UsedRange 
设置LastCell = myRange.Cells（myRange.Cells.Count）
 
 fnd =hugo / vw /osnabrück
 
 MyAr = Split（fnd，/）
 
对于i = LBound（MyAr）To UBound（MyAr ）
 
 Set FoundCell = myRange.Find（what：= MyAr（i），after：= LastCell）
 
如果Not FoundCell不是，然后
 FirstFound = FoundCell .Address 
结束如果
设置rng = FoundCell 
直到找到CELL没有
设置FoundCell = myRange.FindNext（之后：= FoundCell）
设置rng = Union rng，FoundCell）
如果FoundCell.Address = FirstFound然后E xit Do 
循环
下一个i 
 
如果不是rng是没有，然后
 rng.EntireRow.Interior.ColorIndex = 3 
结束如果
 End Sub 
  

例如，使用这段代码，我可以找到并突出显示所有的Osnabrück t高亮任何雨果或大众。

解决方案

这是因为你只是在代码的最后一次亮相，你的数组中的最后一个选择恰好是osnabruck。

你需要移动

 code>如果不是rng是没有，然后
 rng.EntireRow.Interior.ColorIndex = 3 
结束如果
  

刚刚在

  next i 
  

，如果对于数组中的每个元素都会这样做。

I've found a solution already but the code would be too long. Then i decided to search a way of inserting all the words I want to find and highlight into a single Find Method. I came across some ideas of using Arrays to do so and used these 3 codes to write mine (this, this, and this) but I am a new user of VBA so my final code have a problem, it highlights just the last String of the Array. I think the problem is logic but I don't know the basics of VBA so I have no idea how to correct it.

My actual code:

Sub Sample()
Dim fnd As String
Dim MyAr
Dim i As Long
Dim rng As Range, FoundCell As Range, LastCell As Range, myRange As Range

Set myRange = ActiveSheet.UsedRange
Set LastCell = myRange.Cells(myRange.Cells.Count)

fnd = "hugo/vw/osnabrück"

MyAr = Split(fnd, "/")

    For i = LBound(MyAr) To UBound(MyAr)

        Set FoundCell = myRange.Find(what:=MyAr(i), after:=LastCell)

        If Not FoundCell Is Nothing Then
            FirstFound = FoundCell.Address
        End If
Set rng = FoundCell
            Do Until FoundCell Is Nothing
                Set FoundCell = myRange.FindNext(after:=FoundCell)
                    Set rng = Union(rng, FoundCell)
                If FoundCell.Address = FirstFound Then Exit Do
            Loop
    Next i

If Not rng Is Nothing Then
    rng.EntireRow.Interior.ColorIndex = 3
End If
End Sub

For example with this code I can find and highlight all the "Osnabrück" but it doesn't highlight any Hugo or VW.

解决方案

It's because you're only doing the highlight once at the very end of the code, and the last choice in your array happens to be osnabruck.

You need to move

If Not rng Is Nothing Then
    rng.EntireRow.Interior.ColorIndex = 3
End If

just before

next i

so that it'll do if for each element in the array.

这篇关于如何查找和突出显示ActiveSheet中多个字符串的所有出现？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！