3

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.

Community
  • 1
  • 1
Hugo Cavalini Agudelo
  • 85
  • 10

1 Answers1

3

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.

tea_pea
  • 1,482
  • 14
  • 19
  • 1
    Agreed, this will solve the problem. Worth noting: it looks like OP was trying to use `Union` to build a `Range` and do a single highlight at the end. If so, the problem there is the use of `Set rng=FoundCell` which runs for every `i`. This would work if it was `Set rng = Union(rng, FoundCell)`. The problem is that the first `Union` will fail since `rng is Nothing`. This could be resolved by wrapping that with `If rng is Nothing Then : Set rng = FoundCell : Else : ...union... : End If`. – Byron Wall Jun 10 '15 at 18:30